Файл: Методические рекомендации по выполнению лабораторно практических работ по теме Обработка табличной информации.pdf

Симферопольское высшее профессиональное училище электронного и промышленного оборудования
Методические рекомендации по
выполнению лабораторно-
практических работ по теме
«Обработка табличной информации»

- 2 -
Карасиков В.В. Методические рекомендации по выполнению лабораторно- практических работ по теме «Обработка табличной информации». Симф: СВПУ ЭПО, -
2012, - с 62.
Составитель: к.п.н., старший преподаватель, Карасиков Вячеслав Витальевич
( http://wikasim.at.ua
)
Откорректировано: Преподаватель, Балаховская Лилия Викторовна
Рецензент: к.э.н., доцент, Фролов Валерий Иванович.

- 3 -
СОДЕРЖАНИЕ
Л
АБОРАТОРНАЯ РАБОТА
№1.
«
ФОРМАТИРОВАНИЯ ТАБЛИЦЫ
,
ЯЧЕЕК
» .................................. 4
Л
АБОРАТОРНАЯ РАБОТА
№2.
«
МАСТЕР ФУНКЦИИ
,
АДРЕСАЦИЯ ЯЧЕЕК
» .............................. 12
Л
АБОРАТОРНАЯ РАБОТА
№3.
«
РУЧНОЙ ВВОД ФОРМУЛ
» ........................................................ 17
Л
АБОРАТОРНАЯ РАБОТА
№4.
«
ВВОД ФОРМУЛ С УСЛОВИЯМИ
» ............................................. 21
Л
АБОРАТОРНАЯ РАБОТА
№5.
«
ПОСТРОЕНИЕ ГРАФИКОВ И ДИАГРАММ
» .............................. 26
Л
АБОРАТОРНАЯ РАБОТА
№6.
«
ПОДБОР ПАРАМЕТРА
,
СЦЕНАРИИ
» ........................................ 36
Л
АБОРАТОРНАЯ РАБОТА
№7.
«
СВОДНЫЕ ТАБЛИЦЫ
,
ИТОГИ
» ................................................ 41
Л
АБОРАТОРНАЯ РАБОТА
№8.
«
УСЛОВНОЕ ФОРМАТИРОВАНИЕ
,
ФИЛЬТРЫ
» ......................... 47
Л
АБОРАТОРНАЯ РАБОТА
№9.
«
НАСТРОЙКА MS EXCEL
» ......................................................... 53
В
ОПРОСЫ ТЕСТА ДЛЯ САМОПРОВЕРКИ
. ................................................................................... 58
П
ОЛЯ ДЛЯ ЗАМЕТОК
................................................................................................................... 61

- 4 -
ЛАБОРАТОРНАЯ РАБОТА №1.
«ФОРМАТИРОВАНИЯ ТАБЛИЦЫ, ЯЧЕЕК»
Цель: Изучить основы построения таблицы
Задачи:
1. Изучить методику построения сложных таблиц;
2. Изучить форматирования ячеек.
3. Изучить правила ввода последовательностей и автозамены.
Теория.
Документ MS Excel называется книгой и представляет собой листы (по умолчанию их 3), которые в свою очередь состоят из таблицы размером 2 16
×2 16
для MS Excel 2003 или более ранних и 2 20
×2 20
, для MS Excel 2010. Каждый столбец помечен буквой
(буквами) латинского алфавита, а каждая строка числами от 1 до 1048576. Каждая ячейка таблицы однозначно определяется буквой столбца и номером строки: например: B2 - ячейка столбца B и строки 2. Данная запись называется адресом ячейки. На нижеследующем рисунке показано, как определить, какая ячейка является выделенной, ее значение в преобразованном и исходном видах.
Несколько выделенных ячеек называется диапазоном.
Все ячейки имеют 2 состояния: просмотра (преобразованный вид) и ввода данных
(исходный вид).
Примечание. При просмотре данных отображается полное форматирование
ячейки, при вводе данных только частичное форматирование. При вводе текста в
несколько строк для перехода на новую строчку нажимается ALT+ENTER.
В состояние ввода можно попасть 3-мя способами:
1. Сразу вводя данные в выделенной ячейке. При этом, при использовании клавишей
«стрелки» вы покидаете вводимую ячейку, что не всегда удобно.
Адрес выделенной ячейки
Содержание ячейки в непреобразованном виде
Содержание ячейки, преобразованное согласно формату

- 5 -
2. Нажатием клавиши F2. При этом ввод данных аналогичен вводу в обычном текстовом редакторе (можно применить большинство сочетаний клавиш)
3. Перейти в область адресной строки (над таблицей)
ПРАВИЛО ПОСТРОЕНИЕ СЛОЖНЫХ ТАБЛИЦ.
DEFAULT. Стандартный курсор мыши. Отображается над ячейками. При событии Drag (перемещение мыши при зажатой левой кнопке) выделяются ячейки под курсором мыши. Ctrl+Сlick (нажатие левой кнопки мыши) –
Добавляется/снимается (при повторном нажатии) выделение с ячейки.
Shift+Click - Добавляется/снимается выделение с ячеек, начиная с предыдущей позиции до текущей (выделение имеет вид прямоугольника) . Событие DClick
(двойное нажатие левой кнопки мыши) переводит в режим редактирования ячейки. R-Click (нажатие правой кнопки мыши) - открываетконтекстное меню работы с ячейкой(ками).

SERIES. Курсор последовательностей, появляется при наведении на нижний правый угол выделенных ячеек. При событии Drag переносит формулу или последовательность выделенных ячеек в соседнюю ячейку. При событии R-
Drag (перемещение мыши при зажатой правой кнопки мыши) открывается контекстное меню создания последовательности.
TRANSFER. Курсор переноса. Появляется при наведении на границу выделенных ячеек. При событии Drag переносит значения выделенных ячеек на новое место.
H-ZOOM. Курсор изменения горизонтальной границы. Появляется при наведении на границу между буквами столбцов. При событии Drag изменяется ширина столбца слева от курсора. Если выделено несколько столбцов, то измениться размер всех выделенных столбцов. При событии DClick
изменяется размер столбцов, оптимизируя его по содержимому всех ячеек.
V-ZOOM. Курсор изменения вертикальной границы. Появляется при наведении на границу между номерами строк. При событии Drag изменяется высота строки сверху от курсора. Если выделено несколько строк, то изменится размер всех выделенных строк. При событии DClick изменяет размер строк, оптимизируя его по содержимому всех ячеек

COL-SELECT. Курсор выделения столбца. Появляется при наведении на букву столбца (на сером фоне). При событии Click выделяется столбец.
Событие Drag выделяет несколько столбцов. При событии R-click открывается контекстное меню работы с ячейкой применимое для всего столбца.

ROW-SELECT. Курсор выделения строки. Появляется при наведении на номер строки (на сером фоне). При событии Click выделяется строка. Событие
«перетягивание» выделяет несколько строк. При событии R-click открывается контекстное меню работы с ячейкой применимое для всей строки.
Основные клавиши навигации:
TAB
Переключается между выделенными ячейками слева направо, сверху вниз.
SHIFT+TAB
Переключается между выделенными ячейками справа налево, снизу вверх.
CTRL+HOME
Перейти на первую ячейку.

- 6 -
CTRL+END
Перейти в последнюю ячейку таблицы (если на листе несколько независимых таблиц, то переход к последней ячейки прямоугольной области, обхватывающей все таблицы).
Ctrl+(, , )
Переход к следующей границе данных (до или после есть пустые ячейки).
Ctrl+PageUp
(PageDown)
Переход на предыдущий (следующий) лист
Примечание. Все перемещения с нажатой клавишей shift – выделяет ячейки.
ФОРМАТИРОВАНИЕ ЯЧЕЕК (ДОСТУПНО ПРИ СОБЫТИИ R-CLICK):
Формат
Описание
Кнопка на панели в 2007
{2003}
Вкладка «Число» (Числовые форматы)
Общий
Используется для отображения произвольных данных как числовых, так и текстовых. Примечание: если у ячейки стоит общий формат, то Excel стремиться заменить формат данных согласно содержимому, например на процентный или дату. Во избежание автозамены указывайте конкретный формат данных
Вкладка главная/ область
«число» {отсутствует в стандарте}
Числовой
Стандартная запись числа. Могут быть вариации: выделять ли отрицательные числа красным цветом или нет.
--//--//--//--
Ctrl+Shift+1
Также для всех числовых форматов можно изменить разрядность (число знаков после запятой)
Денежный
Вариант числа, при отображении которого в конце приписывается знак выбранной валюты.
Финансовый
Аналогичен числовому и денежному формату, но нельзя выделять отрицательные числа и, по умолчанию, не указана валюта.
Дата
Запись даты в различных стандартах.
По умолчанию - как в системе.
Автоматически создается из общего формата при вводе двухзначных чисел разделенных точкой.
(10.1210.дек). Примечание: является особой формой числового формата (может участвовать в вычислении).
--//--//--//--

- 7 -
Время
Запись времени в различных стандартах по умолчанию как в системе. Автоматически создается из общего формата при вводе двухзначных чисел разделенных двоеточием.
(10:3010:30:00). Примечание: является особой формой числового формата (может участвовать в вычислении)
--//--//--//--
Процентный
Автоматически создается при наборе сивола «%» в конце числа. (пример:
1,345 134,5%)
Ctrl+shift+5
Дробный
Округляет вводимое или вычисляемое число так чтобы его можно было представить в виде дроби заданной размерности (например число ПИ будет выглядеть 3 1/7 при точности 1 знак и 3 16/113 при точности 3 знака).
--//--//--//--
Экспоненциальный
Числовой формат, в котором выделяется десятичная степень и обозначается буквой E(пример:
12341,234E+03, что читается как
1,234 × 10 3
)
--//--//--//--
Текстовый
Стоит устанавливать данный формат в том случае, когда вы хотите чтоб данные отображались буквально без изменений, т.е. не проводились вычисления и автозамены.
--//--//--//--
Дополнительные
Например для ввода телефона с разделителем «44-89-00»
--//--//--//--
Вкладка «выравнивание»
Выравнивание вертикальное
Варианты: по верхнему краю, по центру, по нижнему краю. По умолчанию по нижнему краю.
{отсутствует в стандарте}
Выравнивание горизонтальное
По умолчанию текст в программе
Microsoft Excel выравнивается по левому краю, числа — по правому краю, а логические значения и ошибки выравниваются по центру.
Стандартные: по левому краю, по правому краю, по центру.
Дополнительные: с заполнением
0  000000;

- 8 -
Перенос по словам
Автоматически включается при вставке разрыва строки в текст
(АLT+ENTER). Границы строки при этом автоматически расширятся так, чтобы текст помещался по горизонтали
{отсутствует}
Объединение ячеек
Обедняются выделенные ячейки и текст выравнивается по центру по горизонтали. Повторное нажатие
отменяет объединение. Ячейки
нельзя разбивать, их можно только
объединять.
Ориентация
В MS Excel текст может быть повернут под любым углом.
Примечание. Ориентация
показывается только в режиме
просмотра. В режиме ввода текст
всегда горизонтальный.
{отсутствует в стандарте}
Форматирование аналогичное текстовым редакторам
Размер, гарнитура шрифта, полужирный, курсив, подчеркнутый, границы таблицы, фон, цвет шрифта. Все работает аналогично с MS
Word, также работают горячие клавиши.
ПРАВИЛА ВВОДА ПОСЛЕДОВАТЕЛЬНОСТЕЙ.
1. Способ первый «построения арифметической последовательности»
Вид
Описание
Ввели произвольный текст в две ячейки, отличающиеся числами в конце
Выделили ячейки.
Захватили за нижний правый уголок
Растянули до нужной позиции. При этом выскакивает подсказка, какое последнее значение будет внесено
Результат создания последовательности

- 9 -
2. Создание последовательностей даты или времени
Вид
Описание
Вводим в ячейку первый месяц последовательности (и/или начальное время).
Растягиваем аналогично первому способу.
При этом автоматически выбирается следующий месяц, а для времени следующий час.
3. Создание произвольных последовательностей a. Вводим значение, заканчивающееся числом, можно одно, можно два. b. Выделяем ячейку или ячейки, растягиваем, захватывая нижний правый угол, удерживая правую кнопку мыши. c. Выбираем один из пунктов контекстного меню: линейный, экспоненциальный (если выделено два значения), прогрессия…(если одно значение). Следуя инструкциям, создаем последовательность.
Контекстное меню
Диалог создания прогресси
Ход работы.
1. Набрать нижеследующую таблицу, кроме столбцов, выделенных серым цветом
(выделенные столбцы - вычисляемые и будут рассчитываться в следующей лабораторной работе)
2. На выполнение работы отводиться 50 минут, отставание на 15 минут снижает оценку на 1 балл.

- 10 -
Рекомендации по выполнению работы.
1. Не объединяйте ячейки, если в том нет необходимости, лучше расширяйте границы ячеек.
2. Помните - ячейки нельзя разбивать. По этому, учитывайте количество столбцов.
3. При автозамене номеров в первом столбце выделить столбцы и выставить формат ячейки «текстовый»;
4. Вводите последовательность там, где идут подпункты (1.1.1, 1.1.2);
5. При вводе чисел выделяйте заполняемый сегмент и переключайтесь клавишей
TAB;
6. Для создания равномерных столбцов, выделите все столбцы, которые должны стать узкими (Ctrl+Click на букве столбца) и измените их размер все вместе.

- 11 -

- 12 -
ЛАБОРАТОРНАЯ РАБОТА №2.
«МАСТЕР ФУНКЦИИ, АДРЕСАЦИЯ ЯЧЕЕК»
Цель: научиться использовать мастер функций
Задачи:
1. Проработать использование мастера функций.
2. Определить понятия относительной, абсолютной и смешенной адресации.
Теория
Диапазон - это набор выделенных ячеек. Ячейки можно выделить с помощью события drag (перетягивание, см. выше) или события Ctrl+Click. Диапазон можно задать также ручной записью следующим образом:
1. Прямоугольная область: задаётся адресом верхнего левого и правого нижнего адреса диапазона прямоугольной области разделенных « : »
2. Перечислением адресов ячеек и прямоугольных областей разделенных « ; »
Пример:
1. «B3:C7» - прямоугольная область состоящая из 10 ячеек;
2. «А1;А7; В15; B3:C7» - перечисление состоящее из 3-х отдельных ячеек и диапазона (всего 13 ячеек).
СПОСОБЫ ВВОДА ФУНКЦИЙ
Стандартные статистические функции:
Способ доступа
Синтаксис
функции
Описание
Примечание. В диапазоне игнорируются ячейки отличные от числового формата
= СУММ(Диапазон)
(ALT + =). Складывает все числа из диапазона
=СРЗНАЧ(Диапазон)
Вычисляет среднее значение всех значений из диапазона
=СЧЁТ(Диапазон)
Считает число ячеек диапазона (в том числе и нечисловые)
=МАКС(Диапазон)
Результатом является максимальное число из диапазона
=МИН(Диапазон)
Результатом является минимальное число из диапазона
«Shift+F3» или
Доступ к мастеру функций

- 13 -
Шаг 2. Ввод аргументов выбранной функции.
Пример результата работы мастера функций:
=СУММПРОИЗВ(F7:I7; F12:I12)
АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ
Чтобы понять принципы относительной и абсолютной адресации рассмотрим следующий пример:
Если известно, как писать функцию, то можно ее быстро найти не листая найти
Список функций из выбранной категории.
Стандартно отображаются – 10 недавних
Поиск функций по категориям. Если не уверены, в какой, выбирайте «полный алфавитный…»
Если в качестве аргумента должна выступить другая функция, то выбираем ее в адресной строке
Краткая справка по аргументу
Результат предварительного вычисления (Можно увидеть ошибки)
Кнопка временного скрытия диалога, чтобы выделить ячейку (диапазон), которая будет выступать аргументом.