Эксперт

Сергей

Задать вопрос

Мы готовы помочь Вам.

ЦЕЛЬ РАБОТЫ:
 научиться вводить в ячейки и редактировать текст, числа, формулы;
 научиться строить и редактировать диаграммы;
 научиться связывать листы рабочей книги и в групповом режиме производить
вычисления по формулам;
 научиться применять статистические функции для решения задач;
 научиться создавать экранные формы и с их помощью выполнять ввод данных в таблицу
и их редактирование;
 научиться применять автофильтры и расширенные фильтры для поиска необходимой
информации по условиям.
1. ТЕОРЕТИЧЕСКОЕ ВВЕДЕНИЕ
Табличные процессоры (например, Microsoft Excel) – общее название программных
средств, предназначенных для обработки электронных таблиц.
Под электронной таблицей понимают способ представления информации в
табличной форме, хранимой в памяти ЭВМ.
Активное рабочее окно представлено в процессоре в виде таблицы, неотъемлемым и
основным элементом которой является курсор таблицы (курсор ввода), имеющий вид черной
рамки.
Файл (документ, созданный в табличном процессоре, имеющий расширение «.xls»)
представляет собой рабочую книгу, состоящую из листов – электронных таблиц.
Ячейка – элемент таблицы, находящийся на пересечении столбца и строки.
Адрес ячейки состоит из адреса столбца (большая латинская буква) и номера строки,
например: А1, С12 и т.д..
Текущая (активная) ячейка – ячейка, выделенная курсором таблицы, ее адрес
отображается в левой части строки формул.
Курсор таблицы – черная контурная рамка, символизирующая ячейку, с маленьким
квадратом в правом нижнем углу (маркер заполнения), которая перемещается с помощью
клавиш управления курсором или с помощью мыши (щелчок левой кнопки мыши на нужной
ячейке).
Строка формул – строка, расположенная под пиктографическим меню и
отображающая адрес текущей ячейки и ее содержимое.
Блок – непрерывная прямоугольная область ячеек (одна ячейка, строка, столбец,
прямоугольная область). Блок задается адресами левой верхней и правой нижней ячейки
через двоеточие, например, А1 (ячейка), C6:K6 (строка), B3:B8 (столбец), A4:D9 (блок или
прямоугольная область).
Имя ячейки складывается из заголовка столбца и номера строки, на пересечении
которых она находится, например, A1, Z324, A12. Полное имя ячейки также включает в себя
имена файла рабочей книги и листа, на которых она расположена. Так, ячейка A1 на листе
«Лист1» в рабочей книге «Книга1» имеет полное имя [Книга1]Лист1!A1.
Перемещение по листу выполняется при помощи полос прокрутки или кнопок
клавиатуры: [←], [↑], [→], [↓], [Page Up], [Page Dn].
Ввод данных в ячейку начинается с ее активизации (выделения) рамкой (курсором
таблицы), которая перемещается с помощью клавиш управления курсором или щелчком
левой кнопки мыши на нужной ячейке. Таким образом, активная (текущая) ячейка имеет
жирную контурную рамку, адрес ячейки отображается в левой части строки формул.
Информация, вводимая в ячейки электронной таблицы, бывает трех видов: текст,
число и формула. Числовая информация разделяется запятой.
Для форматирования ячеек применяются команды операционного меню ФОРМАТ,
вызываемого по нажатию ПКМ в области выделенных ячеек.
Формулы. Мастер функций. Формулы используются для выполнения расчетов, они
позволяют производить вычисления в итоговых ячейках по данным, содержащимся в
исходных ячейках. В качестве примера использования формул можно привести такие
операции, как расчет итоговых сумм, средних значений, нахождения минимального /
максимального значений и т.д. Формулы записываются при помощи арифметических
знаков: сложения (+), вычитания (–), умножения (∗), деления (/), возведения в степень (^);
чисел; имен ячеек и диапазонов; а также встроенных функций. Порядок выполнения
действий определяется приоритетом операций: в первую очередь выполняются действия в
круглых скобках, затем – встроенные функции, возведение в степень, умножение/деление и в
последнюю очередь – сложение/вычитание.
Правила ввода формул.
1. Формула всегда начинается со знака «=».
2. Адреса ячеек вводятся в режиме латинских букв.
3. Аргументы функций заключаются в круглые скобки.
Встроенные функции можно вводить вручную с клавиатуры или при помощи мастера
функций.
В табличном процессоре, например, MS Excel, имеется возможность копирования
формул. Данная операция осуществляется аналогично операции копирования ячеек, самым
простым и удобным способом при этом является использование маркера заполнения. Ссылки
на исходные ячейки, которые при копировании изменяют свою адресацию (номер строки,
или обозначение столбца) относительно номера или буквы соответствующей итоговой
ячейки, называются относительными. Ссылки на исходные ячейки, которые при
копировании не изменяют своей адресации, называются абсолютными. Абсолютная ссылка
«заключается» в знаках $, например, $F$5 – абсолютная ссылка на ячейку F5.
Для анализа и обработки данных в электронных таблицах имеются специальные
средства, позволяющие структурировать и хранить данные в связанном списке, а также
составлять и получать различные отчеты. При этом таблица данных должна быть
представлена в виде списка или базы данных (БД).
Список – это таблица, содержащая уникальные записи (имена полей) в первой строке.
Строки таблицы называются записями БД, а столбцы – полями. Запись – набор полей одного
объекта. Поле – это определенная категория информации. Первая строка таблицы – названия
полей списка.
Форма данных – это удобный способ для просмотра, изменения, добавления, удаления
записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Форма
отображает одну запись списка.
Для работы с формами, например, в программе MS Excel надо выбрать пункт меню
Данные – Форма. Если пункта Форма нет, необходимо вынести кнопку Форма на панель
быстрого доступа. Схема действий:
Кнопка Office – параметры Excel – Настройки – Настройка панели быстрого
доступа (Все команды) – Форма – Добавить.
В результате операций на экране появится диалоговое окно формы с заполненной
информацией о первой записи списка. Если список еще не создан – введены только
наименования его столбцов, следует выделить эти наименования, выбрать кнопку Форма и в
открывшемся информационном окне нажать кнопку ОК. Появится пустая форма, готовая к
заполнению. Одновременно в форме может выводиться до 32 полей списка. Назначение
кнопок управления списком представлено в Таблице 3.

Необходимо различать сортировку по одному столбцу от сортировки по нескольким
столбцам.
1. Сортировка по одному столбцу. Записи сортируются на основании выбранного поля по
возрастанию (или убыванию) значений в этом поле независимо от места расположения
данного поля.
Схема действий.
Выделить любую ячейку столбца – Вкладка Данные – Группа Сортировка и
фильтр – Выбрать кнопку: Сортировка от А до Я или Сортировка от Я до А.
2. Сортировка по нескольким столбцам. Записи упорядочиваются согласно более сложному
алгоритму. Сначала строки списка группируются по значениям одного столбца. Затем
строки с одинаковыми значениями по первому, ранее отсортированному столбцу,
располагаются по значениям второго столбца и т.д. Для того, чтобы выполнить
сортировку записей заданного списка по значениям нескольких столбцов, необходимо
выполнить операции по следующей схеме:
Выделить любую ячейку списка — Вкладка Данные – Группа Сортировка и фильтр
– Кнопка Сортировка – Кнопка Параметры … — (Уточнить параметры сортировки: по
столбцам (по умолчанию)/по строкам — ОК) – Выбрать в списке Сортировать по первый
столбец.
Фильтрация данных. Фильтрация – это способ управления данными списка,
заключающийся в скрытии всех записей, кроме тех, которые отвечают условиям, заданным
пользователем. При отборе записей по условиям фильтра табличный процессор не различает
строчные и прописные буквы. Например, в MS Excel известны две команды фильтрации:
 Автофильтр. Используется для реализации простых, часто применяемых
критериев отбора.
Схема действий по Автофильтру. Установить курсор на любой ячейке списка –
Вкладка Данные – Группа Сортировка и фильтр – Кнопка Фильтр.
Меню условий отбора автофильтра содержит список всех уникальных значений,
содержащихся в выбранном столбце, команды сортировки таблицы в соответствии со
значениями поля по возрастанию, по убыванию, по цвету, а также одну из команд
формирования пользовательского критерия отбора (в зависимости от типа данных
столбца см. рис.1):
18
 текстовые фильтры;
 числовые фильтры;
 фильтры по дате.
Выбор данных команд открывает меню с дополнительными возможностями фильтрации
(Таблица 4).

4.1. Отформатировать область диаграммы (щелкнуть в области диаграммы, на ленте
выбрать пункт меню Макет, появятся возможные действия):
4.1.1. Формат выделенного фрагмента — Цвет границы – сплошная линия черного
цвета, Стиль границ – ширина 2 пт, тип соединения – прямое, скругленные углы.
Заливка сплошная голубого цвета;
4.1.2. Шрифт (Пункт меню Главная)- Шрифт Arial, Стиль полужирный, Размер 10.
4.2. Отформатировать область построения диаграммы (щелкнуть ЛКМ в области
построения диаграммы, выделить ее, выбрать Макет – Формат выделенного
фрагмента) следующим образом:
4.2.1. Формат выделенного фрагмента — ширина линии -1 пт, тип соединения –
прямое, цвет границы — серый, Заливка розового цвета.
4.3. Отформатировать ряд данных Информатика (найти на диаграмме любой Ряд
Информатика, щелкнуть по нему, на ленте появится в окне название Ряд
«информатика», Выбрать пункт Формат выделенного фрагмента) следующим
образом:
4.3.1. Вкладка Цвет границы — сплошная линия черного цвета, Заливка сплошная
зеленого цвета;
4.3.2. Вкладка Фигура – цилиндр;
4.3.3. Вкладка Параметры ряда — фронтальный зазор – по умолчанию, боковой зазор
– по умолчанию.
4.4. Отформатировать ряд данных Высшая математика следующим образом:
4.4.1. Вкладка Цвет границы — сплошная линия черного цвета, Заливка сплошная
синего цвета;
4.4.2. Вкладка Фигура – цилиндр;
4.4.3. Вкладка Параметры ряда — фронтальный зазор – по умолчанию, боковой зазор
– по умолчанию.
4.5. Отформатировать основание диаграммы так:
4.5.1. Вкладка Цвет границы — сплошная линия черного цвета, Заливка сплошная
желтого цвета;
4.6. Отформатировать ось значений (выделить ее — Макет) так:
4.6.1. Вкладка Параметры оси — основные деления — пересекают ось, промежуточных
нет, подписи оси — рядом с осью;
4.6.2. Шрифт (пункт меню Главная) – 10.
4.7. Отформатировать ось категорий (выделить ее — Макет) так:
4.7.1. Параметры оси — тип оси – по умолчанию, основные деления — наружу,
промежуточные — нет, подписи оси — рядом с осью;
4.7.2. Вкладка Выравнивание – 60 градусов.
4.7.3. Шрифт (пункт меню Главная) – 8.
4.8. Отформатировать стены (выделить на диаграмме любую ось, на ленте выбрать пункт
меню Макет, слева на линейке в окне найти Стенки) следующим образом:
4.8.1. Формат выделенного фрагмента, Цвет границы – сплошная линия черного
цвета, Заливка сплошная, цвет – серый. Так отформатировать заднюю и боковую
стенки.
5. В результате вы должны получить следующую диаграмму:

5. Заполнить ячейки столбца А, присвоив каждому сотруднику порядковый номер,
используя средства табличного процессора для автоматического заполнения порядковых
номеров;
6. Отменить групповой режим работы. Обратить внимание, что таблица одновременно
создавалась на всех выделенных рабочих листах;
7. Включить групповой режим работы для рабочих листов Январь, Февраль, Март, выделив
их с помощью мыши и клавиши Shift. Все что вы далее будете набирать на рабочем листе
Январь, автоматически будет набираться на всех выделенных рабочих листах;
8. В ячейку D5 ввести формулу для расчета отчислений в пенсионный фонд (1% от
начисленной суммы);
9. Скопировать эту формулу в другие ячейки столбца D;
10. В ячейку E5 ввести формулу для расчета текущего облагаемого дохода, в который
заносится вся начисленная сумма за минусом 1% в пенсионный фонд и необлагаемого
минимума в размере одного минимального оклада.
Примечание: Обратить внимание на типы ссылок, которые вы будете использовать в
формуле. Ссылка на ячейку D2, содержащую размер минимального оклада , при
копировании формулы должна остаться неизменной.
11. Скопировать эту формулу в другие ячейки столбца Е;
12. В ячейках C15-E15 вычислить итоговые суммы;
13. Отменить выделение группы;
14. Заполнить ячейки столбца С на рабочих листах Январь, Февраль, Март сведениями о
начисленной заработной плате;
15. Записать формулу для облагаемого дохода с начала года;
a) В январе она равна текущему облагаемому доходу, то есть в ячейку F5 рабочего листа
Январь следует внести формулу =Е5, а затем скопировать ее в другие ячейки столбца
F;
b) В феврале облагаемый доход равен текущему облагаемому доходу плюс облагаемый
доход за январь, то есть расчетная формула в ячейке F5 рабочего листа Февраль будет
иметь вид: =E5 + Январь! Е5.
Скопировать указанную формулу в другие ячейки столбца F;
c) В марте аналогичная формула в ячейке F5 будет иметь вид:
= СУММ(Январь:Март!Е5);
Скопировать указанную формулу в другие ячейки столбца F;
16. На рабочем листе Итого записать формулы для подсчета итоговых сумм:
=СУММ(Январь:Март!С5) в столбце “Начислено”;
=СУММ(Январь:Март!D5) в столбце “Пенсионный фонд”;
=СУММ(Январь:Март!Е5) в столбце “Облагаемый доход с начала года”.
17. Размножить данные формулы;
18. В ячейках C15-F15 вычислить итоговые суммы;
19. Сохранить созданную книгу в своем рабочем каталоге;
15. Показать выполненное практическое задание преподавателю и составить отчет в тетради
с кратким описанием выполняемых действий.
З А Д А Ч А № 5
1. Создать в табличном процессоре базу данных (БД) согласно рис.14 и внести в нее 5
записей.