Формулировка задания: MS Excel. Создание таблиц. Ввод формул
Цель работы: Знакомство с основными навыками работы в электронных таблицах: ввод данных в ячейки таблицы, форматирование ячеек таблицы, ввод простых формул, создание обрамления, редактирование данных таблицы.
Контрольный пример
Рассмотрим работу в электронных таблицах на примере коммерческой фирмы, которая занимается торговлей кондитерскими изделиями. В процессе работы такая фирма обрабатывает различного рода документы. Каждая лабораторная работа будет посвящена разработке различных документов для документирования деятельности фирмы. Одним из основных документов является накладная на получение товаров (Рис 94.).
Дата 20.02.02
НАКЛАДНАЯ № 1
Фирма Город
Кому Владивосток
От кого кондитер
№ п/п | Наименование | Количество | Цена (за кг) | Сумма |
1. | Конфеты «Белочка» | 50 | 70р. | 3500р. |
2. | Конфеты «Маска» | 45 | 68р. | 3060р. |
3. | Конфеты «Мелодия» | 65 | 85р. | 5525р |
4. | Конфеты «Костер» | 40 | 92р. | 3680р. |
5. | Конфеты «Слива» | 70 | 53р. | 3710р. |
6. | Конфеты «Василёк» | 80 | 75р. | 6000р. |
7. | Конфеты «Ромашка» | 50 | 55р. | 2750р. |
Итого: | 28225р. |
Рисунок 94 - Накладная на получение товара
Рассмотрим создание этого документа средствами электронных таблиц MS Excel.
1. 1. Начиная с ячейки А1 введите последовательно в электронную таблицу данные о порядковом номере, наименовании, количестве и цене поставляемого товара.
Для ввода данных в ячейку необходимо выбрать нужную ячейку, набрать информацию и зафиксировать ввод нажатием клавиши Enter или щелчком мыши на другой ячейке. (Не обращайте внимание на то, что данные не всегда вмещаются в ячейку. Ширину ячеек возможно потом изменить). В результате получим таблицу следующего вида (Рис. 95):
 |
Рисунок 95 - Исходная таблица для формирования документа
2. 2. Для расчета значений сумм используется формула:
Сумма=Количество * Цена
Формулы в электронных таблицах принято строить на основе адресов ячеек, в которых находятся значения. Таким образом, необходимо выполнить следующие действия:
· · Введите формулу в ячейку Е2: =D2*C2.
· · Ввод формулы можно выполнить, набирая её вручную, как текст, или выполнив следующие операции:
- - Наберите в ячейке Е2 знак “=”;
- - Выполните шелчок мыши на первой ячейке, входящей в формулу D2;
- - Наберите знак “*”;
- - Выполните шелчок мыши на второй ячейке, входящей в формулу С2;
- - Нажмите клавишу Enter.
· · Для того, чтобы не вводить аналогичные формулы в остальные ячейки таблицы в MS Exsel возможно выполнить заполнение ячеек соответствующими формулами. Для этого:
- - установите табличный курсор на ячейку Е2;
- - наведите указатель мыши на маркер заполнения (черный квадрат в нижнем правом углу ячейки) (Рис. 3). При наведении на него указатель мыши приобретает вид тонкого черного крестика;
- -
 |
Удерживая нажатой левую клавишу мыши, перетащите маркер вниз до ячейки Е8.
Рисунок 96 - Табличный курсор с маркером заполнения
3. 3. Для расчета итогового значения необходимо:
· · Выделить диапазон ячеек Е2:Е8
Для выделения диапазона нажмите кнопку мыши на первой ячейке диапазона, протяните курсор до последней ячейки диапазона и отпустите кнопку мыши.
· · 
Воспользоваться специальной пиктограммой «Автосуммирование» на панели инструментов
В результате всех выполненных действий таблица примет следующий вид (Рис 97).
 |
Рисунок 97- Таблица с результатами вычислений
4. 4. Выполните форматирование данных таблицы.
· · Измените шрифт данных в ячейках таблицы, выполнив следующую последовательность действий:
- - Выделите диапазон ячеек А1:Е9;
- - Воспользуйтесь пунктом меню Формат~Ячейки~Шрифт
Шрифт - Times New Roman Cyr; Размер – 12;
· · Измените формат данных в столбцах Цена (за кг) и Сумма
- - Выделите диапазон ячеек D2:Е9;
- - Воспользуйтесь пунктом меню Формат~Ячейки~Число
Числовые форматы – Денежный; Число десятичных знаков – 0; Обозначение – р.
5. 5. Измените формат строки заголовка таблицы, выполнив для этого перечисленные ниже действия:
· · Чтобы заголовки таблицы были полностью видны на рабочем листе необходимо увеличить ширину столбцов В, С и D. Для этого, необходимо протащить с помощью мыши правую границу заголовка выбранного столбца (Рис. 98) до тех пор, пока столбец не станет нужной ширины;
|
 |
Рисунок 98 - Границы столбцов электронной таблицы
· · Аналогичным образом уменьшите ширину столбца А (до ширины »4,29);
· · Целесообразно расположить текст в ячейке А1 в две строки. Для этого:
- - Установите курсор в ячейку А1
- - Воспользуйтесь пунктом меню Формат~Ячейки~Выравнивание~Переносить по словам
6. 6. Настройте расположение данных в таблице. Для этого выполните следующие действия:
· · Задайте параметры выравнивания текста в ячейках:
- - Выделите ячейки А1:Е1;
- - Воспользуйтесь пунктом меню Формат~Ячейки~Выравнивание~
По горизонтали - по центру
По вертикали – по центру
- - Выделите ячейки А2:А8 и С2:Е9. Для этого, выделив первый диапазон данных, нажмите клавишу CTRL и, не отпуская её, выделите второй диапазон;
- - Воспользуйтесь пунктом меню Формат~Ячейки~Выравнивание~
По горизонтали - по центру;
· · Обратите внимание на то, что в последней строке таблицы несколько ячеек объединены. Чтобы добиться этого необходимо выделить ячейки А9:D9 и воспользоваться пунктом меню Формат~Ячейки~Выравнивание~Объединение ячеек;
7. 7. Выполните обрамление таблицы, используя линии различной толщины:
- - Выделите все ячейки таблицы А1:Е9;
- - Воспользуйтесь пунктом меню Формат~Ячейки~Граница~
- -
 |
Выберите Тип линии и укажите, что он относится к внутренним границам, а затем аналогично укажите Тип линии для внешней границы (Рис.99).
Рисунок 99 - Оформление границ таблицы
После выполнения всех операций по оформлению таблицы, она примет следующий вид (Рис. 7).
Рисунок 100 - Таблица после оформления
Для дальнейшего оформления таблицы необходимо добавить 6 строк перед таблицей. Выделите первые 6 строк таблицы (щелкните на заголовке строки 1 и протяните курсор до пятой строки). Используйте пункт меню Вставка~Строки. Введите в ячейки таблицы текст в соответствии со схемой, представленной на Рис. 101.  |
Рисунок 101 -Исходный текст шапки документа
Выполните форматирование введенных данных и настройте формат их расположения:· · Для форматирования шрифта:
- - Выделите диапазон ячеек А1:F5;
- - Воспользуйтесь пунктом меню Формат~Ячейки~Шрифт
Шрифт - Times New Roman Cyr; Размер – 12;
· · Выделите цветом, те данные шапки таблицы, которые могут изменяться:
- - Выделите ячейку F5;
- - Воспользуйтесь пунктом меню Формат~Ячейки~Вид~Заливка ячеек~Цвет~Светло-серый
- - Выполните аналогичные действия для ячеек С4, С5, D2, D4.
Переименуйте лист рабочей книги:- - Выполните двойной щелчок мыши на имени первого листа Лист 1.
- - Удалите стандартное имя и введите новое название – Накладная.
В результате всех проведенных вами операций документ приобретет вид в соответствии с Рис. 102.
Рисунок 102 - Итоговый документ
12. 12. Произведите копирование полученной таблицы на Лист2. Для этого выделите полученную таблицу, выполните операцию Копировать, перейдите на Лист 2 и выполните операцию Вставить.
13. 13. Теперь мы оформим несколько накладных на продажу нашей фирмой Сластена товара в другие фирмы, которые являются нашими клиентами. Для этого
- - В ячейке С5 введите название фирмы-поставщика Сластена
- - В ячейке С4 введите название фирмы-клиента Европа, а в ячейке D4 название города Находка
- - Сделайте 8 копий через 5 строк, полученной накладной
- - Поменяйте даты на накладных. В качестве дат нужно использовать период с января по июнь;
- - Измените номера накладных на 2,3,4 и т. д. соответственно;
- - Измените наименование фирм-клиентов (Фрегат, Аэлита, Росток, Баунти, Фламиного, Орион) и городов, где они располагаются (Москва, Владивосток, Хабаровск, Иркутск, Комсомольск, Уссурийск)
14. 14. Сохраните документ с именем Фирма_Сластена в каталоге Excel.
Формулировка задания: MS Excel. Построение диаграмм
Цель работы: Получение практических навыков построения диаграмм с помощью мастера диаграмм, а также редактирования и форматирования диаграмм.
Контрольный пример
Создание таблицы
1. 1. Откройте книгу контрольного примера Фирма_Сластена.
2. 2. Добавьте в книгу еще один рабочий лист и назовите его Склад.
3. 3. Создайте таблицу по следующему образцу (рис. 103).
 |
Рисунок 103 - Образец таблицы
4. 4. В ячейку D2 введите сегодняшнюю дату.
5. 5. В ячейку Е6 введите формулу для определения остатка товара
=С6-D6
6. 6.
 |
Скопируйте эту формулу в остальные ячейки D7:D10 методом автозаполнения. В результате таблица будет выглядеть следующим образом (рис. 104):
Рисунок 104 - Таблица после введения формул
Построение диаграммы
По данным таблицы постройте диаграмму типа гистограмма, показывающую в сравнении движение видов товара на складе.
7. 7. 
Для построения диаграммы запустите Мастер диаграмм щелчком на кнопке Мастер диаграмм на Стандартной панели инструментов или командой меню Вставка – Диаграмма
8. 8.
 |
В первом шаге Мастера диаграмм выберите тип диаграммы Гистограмма и вид первый в списке. Нажмите кнопку Далее как показано на рисунке 105.
Рисунок 105 - Окно Мастера диаграмм – шаг 1
9. 9.
 |
На втором шаге на вкладке Диапазон данных воспользуйтесь кнопкой свертывания диалогового окна (рис. 106) для выхода в таблицу.
Рисунок 106 - Окно Мастера диаграмм – шаг 2
10. 10. Выделите диапазон исходных данных В5:Е10 и вернитесь в окно диалога, используя эту же кнопку свертывания окна исходных данных. Установите переключатель Ряды в строках как показано на рисунке 107. Нажмите кнопку Далее.
 |
Рисунок 107 - Выполнение выбора данных
11. 11.
 |
В третьем шаге, используя на вкладке Заголовки введите заголовок диаграммы: Движение товара на складе (рис. 108).
Рисунок 108 - Окно Мастера диаграмм – шаг 3. Вкладка Заголовки
12. 12. На вкладке Легенды установите расположение легенды - Внизу. . Нажмите кнопку Далее для перехода к следующему шагу (рис. 109).
 |
Рисунок 109 - Настройка легенды
13. 13.
 |
В четвертом шаге установите расположение диаграммы на отдельном листе и нажмите кнопку Готово (рис. 110).
Рисунок 110 - Выбор места расположения диаграммы
В результате всех выполненных действий в книгу Excel добавляется дополнительный лист с названием Диаграмма1 и диаграмма примет вид (рис. 111):
 |
Рисунок 111 - Диаграмма
Форматирование элементов диаграммы.
14. 14. Выделите заголовок диаграммы – щелчок мыши в области заголовка.
15. 15. Измените формат заголовка. Для этого воспользуйтесь пунктом меню Формат ‑ Выделенное название диаграммы (рис. 112) и выполните следующие настройки:
Шрифт: Размер - 14 - Times New Roman Cyr – полужирный курсив, цвет ‑синий.
Рисунок 112 - Меню форматирования заголовка диаграммы
16. 16. Измените формат шрифта для оси Х. Для этого выделите ось Х. Воспользуйтесь пунктом меню Формат ‑ Выделенной оси. Выполните следующие настройки:
Шрифт: Размер - 10 - Arial Cyr – обычный – цвет ‑ фиолетовый.
17. 17. Аналогичным образом измените формат шрифта для оси Y.
18. 18. Измените заливку первого ряда данных. Для этого выберите первый столбец в любой категории щелчком мыши на выбранном столбце.
19. 19. Воспользуйтесь пунктом меню Формат ‑ Выделенный ряд, вкладка Вид. Выберите зеленый цвет.
20. 20. Аналогичным образом измените заливку второго ряда данных на желтый цвет, а заливку третьего ряда – на красный цвет.
21. 21. Измените формат шрифта легенды, выделив легенду, затем выбрав меню Формат ‑ Выделенная легенда. На вкладке Шрифт выберите Размер - 9, Arial Cyr, курсив, цвет – черный.
Добавление к диаграмме новых данных
22. 22. Перейдите на лист Склад и добавьте в таблицу еще одну строку с данными (рис. 113):
6 | Конфеты "Мечта" | 90 | 40 | 50,00 |
Рисунок 113 - Дополнительная строка таблицы
23. 23. Перейдите на лист Диаграмма1 выделите область диаграммы.
24. 24. В появившемся меню Диаграмма выберите команду Добавить данные.
25. 25. В диалоговом окне Новые данные (рис. 114) укажите диапазон с новыми данными. Для этого щелкните на ярлычке листа Склад, а затем в таблице с помощью мыши выделите диапазон ячеек B11:E11. После нажатия кнопки ОК соответствующие данные появятся на диаграмме.
 |
Рисунок 114 - Диалоговое окно Новые данные
Изменение типа или вида диаграммы:
Измените вид гистограммы из плоского представления в объемное.
26. 26. Выделите область диаграммы и выберите пункт меню Диаграмма ‑ Тип диаграммы.
27. 27. В диалоговом окне шага 1 Мастера диаграмм выберите вид объемный и нажмите кнопку ОК.
28. 28. Если полученный результат Вас не устраивает, верните исходную настройку типа диаграммы с помощью команды Отменить.
Формулировка задания: MS Excel. Работа с таблицами, расположенными на разных листах
Цель работы: научиться применять связывание двух электронных таблиц таким образом, чтобы при изменении содержимого какой-либо ячейки в первичной таблице аналогичным образом изменялось содержимое соответствующей ей ячейка в связанной таблице.
Контрольный пример
1. 1. Откройте книгу контрольного примера
2. 2. Добавьте в книгу контрольного примера новый рабочий лист и назовите его “Штаты”. Для вставки листа можно использовать команду Вставка – Лист или в контекстном меню листа команду Добавить.
3. 3. Создайте таблицу по следующему образцу
Рисунок115 – Таблица контрольного примера
4. 4. Добавьте в книгу контрольного примера рабочий лист и назовите его “Зарплата”.
5. 5. В ячейку F1 введите “УТВЕРЖДАЮ”.
6. 6. В ячейку D2 введите “Генеральный директор”.
7. 7. В ячейку G2 введите Фамилию и инициалы генерального директора.
8. 8. В ячейку А4 введите “Количество рабочих дней в месяце”, в ячейку D4 количество рабочих дней в прошедшем месяце (например, 22).
9. 9. В ячейку Е4 введите “ Налог”.
10. 10. В ячейку F4 значение налога равное 13%.
11. 11. В ячейку В6 введите “Расчет зарплаты предприятия”.
12. 12. В ячейку D6 введите название своего предприятия.
13. 13. В ячейку F6 введите “за”.
14. 14. В ячейку G6 введите название прошедшего месяца и номер текущего года.
15. 15. В ячейку В19 введите “ИТОГО”
16. 16. В ячейку D22 введите Главный бухгалтер.
17. 17. В ячейку D24 введите Бухгалтер - кассир.
18. 18. В ячейку G22 введите фамилию главного бухгалтера.
19. 19. В ячейку G24 введите фамилию бухгалтера - кассира
20. 20. Отформатируйте ячейки с введенными данными полужирным шрифтом, тип Arial Cyr, размер 10.
21. 21. В ячейки A8, B8, C8, D8, E8, F8, G8 введите соответственно:
№пп, , Оклад руб. Количество отработанных дней, Начислено руб. Налог руб. Сумма к выдаче руб.
22. 22. Отформатируйте ячейки заголовков по своему усмотрению
23. 23. В результате выполненных операций у Вас должен появится шаблон таблицы расчета зарплаты в виде представленном на рис. 116.
 |
Рисунок 116 – Шаблон таблицы
24. 24. На листе Зарплата в графе в ячейку В9 введите формулу, состоящую из ссылки на адрес ячейки С4 листа Штаты, для чего в ячейке В9 поставьте знак =, перейдите на лист Штаты, активизируйте ячейку С4 и нажмите клавишу Enter. В ячейке В9 появится фамилия генерального директора с инициалами, а в строке формул появится формула =Штаты!C4. Таким образом произошло связывание ячейки D4 листа Штаты и ячейки В9 листа Зарплата.
25. 25. Аналогичным образом свяжите ячейку С9 листа Зарплата с ячейкой D4 листа Штаты. Активизируйте ячейку С9, поставьте знак =, перейдите на лист Штаты, активизируйте ячейку D4 и нажмите клавишу Enter. В ячейке С9 появится число 7000 соответствующее окладу генерального директора, а в строке формул появится формула =Штаты!D4.
26. 26. Теперь свяжите ячейки D9 и D4 листа Зарплата. Активизируйте ячейку D9, поставьте знак = и щелкните по ячейке D4 и одновременно нажатием клавиши F4 обратите относительную ссылку в абсолютную. В ячейке D9 появится число 22, а в строке формул появится формула =$D$4.
27. 27. На листе Зарплата выделите ячейки В9, С9 и D9 и протяните выделение в этих ячейках до строки 18. В результате проведенных действий у Вас должна получится следующая таблица(рис.117).
 |
Рисунок 117 – Полученная таблица с расчетами
А в ячейках таблицы должны находится следующие формулы (рис.118).
Рисунок 
118 – Формулы в созданной таблице
28. 28. Рассмотрите содержимое ячеек по столбцам В и С. Вы видите что в ячейках таблицы Зарплата содержатся ссылки на соответствующие ячейки таблицы Штаты. Такие ссылки называются относительными. В ячейках столбца D все ячейки содержат ссылку на ячейку D4. Такие ссылки называются абсолютными.
29. 29. Произведите расчет начислений по заработной плате. Для этого введите в ячейку E9 формулу:
=С9/$D$4*D9.
30. 30. Произведите расчет налога с заработной платы. Для этого введите в ячейку F9 формулу:
=E9*$F$4/100.
31. 31. Просчитайте сумму к выдаче по заработной плате. Для этого введите в ячейку G9 формулу:
=E9-F9.
32. 32. Сохраните файл контрольного примера.
Представьте работу преподавателю
Для проверки работы у вас должна быть открыта рабочая книга индивидуального задания с листами Штаты и Зарплата.
Заключительные действия
1. 1. Закройте все открытые файлы электронной таблицы.
2. 2. Скопируйте результаты работы.
Формулировка задания: MS Excel. Работа со статистическими, текстовыми функциями, функциями категории
Цель работы: Научиться применять статистические, текстовые функции и функции категории «дата и время», «ссылки и массивы» для обработки электронных таблиц.
Контрольный пример
Откройте книгу контрольного примера Добавьте в книгу еще один рабочий лист и назовите его Фирма-клиент
Создайте таблицу по следующему образцу (рис.1), начиная с ячейки A1.
Рисунок 1 - Таблица «Фирмы-клиенты»
Присвойте столбцам листа имена: Код, Фирма, Руководитель, Адрес, Телефон. Для этого необходимо выделить целый столбец листа электронной таблицы например А, а затем дать команду Вставка – Имя - Присвоить. В открывшемся окне Присвоение имени (рис.2) в поле Имя ввести название Код и нажать клавишу ОК.
Рисунок 2 - Окно для присвоения имени
Повторите описанную выше операцию для всех столбцов таблицы, присвоив им соответствующие имена. Добавьте в книгу контрольного примера еще один лист и назовите его Ревизия В ячейку B1 введите текст Ведомость просроченных платежей на В ячейку B2 введите функцию для определения сегодняшней даты. Для этого используйте мастер функций (рис.3): в категории Дата и время выберите функцию СЕГОДНЯ(). |
Рисунок 3 - Окно мастера функций
В ячейку С2 введите функцию для задания даты в числовом формате. Для этого используйте мастер функций: В категории Дата и время выберите функцию ДАТА. В качестве списка параметров введите год, месяц и число даты введенной с помощью функции СЕГОДНЯ.
Рисунок 4 - Функция ДАТА
На листе рабочей книги у вас должны получиться записи подобные тем, что показаны на рис.5. Датой формирования контрольного примера является 20 мая 2002 года.
 |
Рисунок 5 – Результат вычислений функций
В ячейки A5:E10 введите заголовки столбцов таблицы со следующими названиями: Код заказа, Наименование товара, Код фирмы, Цена заказа, Дата продажи. Наименование товаров скопируйте с листа Накладная. Код фирмы и цену заказа введите как показано на рис.127. В ячейку F5 введите заголовок столбца Дата оплаты В ячейку G5 введите заголовок столбца Просрочка дней. Отредактируйте записи Е6:Е10, вводя дату продажи с помощью функции ДАТА (см п.10). В ячейки F6 и F9 введите даты оплаты по заказам 30 и 28 апреля текущего года соответственно. В ячейку G6 введите формулу для определения количества просроченных дней по оплате заказа =ЕСЛИ(F6=0;$C$3-E6;0). Протяните полученную формулу для ячеек столба G.В результате проведенных действий у вас должна получиться следующая таблица
 |
Рисунок 6 - Таблица заказов
А в ячейках таблицы должны находиться следующие формулы:
 |
Рисунок 7 - Расчетные формулы
Введите в ячейку D13 текст «Количество просроченных заказов». Введите в ячейку D14 текст «Стоимость просроченных заказов». Введите в ячейку G13 формулу для определения количества просроченных заказов:=СЧЁТЕСЛИ(G6:G10;">0")
=СУММЕСЛИ(G6:G10;">0";D6:D10)
Введите в ячейку A17 формулу для формирования вывода по полученным данным. Это формула сцепления, она имеет следующий вид:=СЦЕПИТЬ("На сегодняшний день в фирме неоплаченных заказов ";G13;" на сумму ";G14;" рублей")
Сформируем таблицу с данными о клиентах просрочивших платежи. В ячейку B19 введем заголовок таблицы «Фирмы неоплатившие заказы». В ячейки A20:E20 введем наименования столбцов таблицы – Код фирмы, Наименование фирмы, Руководитель, Адрес, Телефон. В столбец Код фирмы введем коды организаций, просрочивших платежи – 2,4. Для заполнения таблицы остальными данными будем использовать функцию ПРОСМОТР. Введем в ячейку B21 функцию=ПРОСМОТР($A21;Код;фирма).
Введем в ячейку C21 функцию=ПРОСМОТР($A21;Код;Руководитель)
Введем в ячейку D21 функцию=ПРОСМОТР($A21;Код;Адрес)
Введем в ячейку E21 функцию=ПРОСМОТР($A21;Код;Телефон)
 |
Скопируем формулы в строки таблицы. У вас должна получиться таблица представленная на рис.8
Рисунок 8 - Вывод результатов расчетов
Сохраните файл рабочей книги.Формулировка задания: MS Excel. Сортировка и обработка списков
Цель работы: научиться обрабатывать таблицу, как базу данных: сортировать данные по определённым критериям; вводить данные с помощью функции Автоввод; использовать автоматическую фильтрацию для просмотра данных, получать промежуточные результаты при обработке данных.
Контрольный пример
1. 1. Запустите MS Excel.
2. 2. Откройте книгу с контрольным примером.
3. 3. Вставьте еще один лист рабочей книги.
4. 4. Скопируйте таблицу с данными, содержащую поля: Код заказа, Наименование товара, Код фирмы, Цена заказа, Дата продажи с листа Резизия.
5. 5. Переименуйте этот лист в лист с именем Сортировка.
6. 6. Вставьте столбец после столбца Наименование товара.
7. 7. Введите в поле заголовка столбца Тип продукции.
8. 8. Добавьте записи в таблицу, чтобы их число стало 10, для этого выполните следующие операции:
9. 9. Щёлкните на ячейке в конце таблицы и начните набирать слово Конфеты. Как только вы введёте букву «К», функция Автоввод автоматически наберёт оставшиеся буквы слова Конфеты.
10. 10. Введите остальные данные. Исходная таблица представлена на Рис 130.
Код заказа | Наименование товара | Тип продукции | Код фирмы | Цена заказа | Дата продажи |
1 | Конфеты "Василек" | шоколад. | 1 | 10 550,00р. | |
2 | Конфеты "Ромашка" | шоколад. | 2 | 5 800,00р. | |
3 | Конфеты "Загадка" | карамель | 2 | 3 200,00р. | |
4 | Конфеты "Москвичка" | карамель | 3 | 7 000,00р. | |
5 | Конфеты "Школьная" | шоколад. | 4 | 5 200,00р. | |
6 | Конфеты "Шалунья" | карамель | 2 | 2 700,00р. | |
7 | Конфеты "Южная ночь" | шоколад. | 1 | 4 500,00р. | |
8 | Конфеты "Слами" | карамель | 4 | 7 800,00р. | |
9 | Конфеты "Чароднейка" | шоколад. | 3 | 2 600,00р. | |
10 | Конфеты "Орион" | шоколад. | 4 | 4 100,00р. |
Рисунок130 - Таблица с исходными данными
Сортировка данных
1. 1. Отсортируйте данные по наименованию товара, для этого выполните следующие действия:
- - Установите курсор в поле таблицы;
- - В меню Данные выберите команду Сортировка. В открывшемся диалоговом окне в группе Сортировать по щёлкните на стрелке «вниз» и в списке заголовков столбцов выделите строку Наименование товара (Рис 131).
Данные в группе будут отсортированы по наименованию товара и расположены в алфавитном порядке.
- - Убедитесь , что текстовые поля в группах Затем по и В последнюю очередь пусты, и щёлкните на кнопке ОК.
5. 5. Отменить сортировку можно выбрав команду Отменить сортировку в меню Правка. Данные будут находиться в прежнем порядке.
 |
Рисунок 131 - Диалоговое окно Сортировка диапазона
6. 6. Отсортируйте по двум критериям: по Наименованию товара и по Дате продажи, для этого:
- - В меню Данные выберите команду Сортировка.
- - Убедитесь, что в текстовом поле группы Сортировать по находится заголовок Наименование товара и включите опцию по убыванию.
- - В группе Затем по щелкните на кнопке «вниз» и выделите ключ Дата продажи. Щёлкните на кнопке ОК.
Фильтрация списков с помощью автофильтра
1. 1. Для фильтрации списков выполните следующие операции:
- - Установите курсор текущей ячейки в таблицу.
- - В меню Данные выберите команду Фильтр ~ Автофильтр. В заголовках каждого столбца появятся кнопки фильтра со стрелочками вниз.(Рис. 132)
Стрелки фильтра
Рисунок 132 - Таблица со стрелками фильтра
2. 2. Щёлкните на стрелке фильтра в ячейке Дата продажи. В появившемся окне списка критериев необходимо выбрать нужный критерий (ключ) фильтрации.
Критерии фильтрации
Рисунок 133 - Список критериев фильтрации
3. 3. В открывшемся списке критериев выберите Дату продаж , т. е. какие конфеты были проданы в этот день. Результат выполнения операции представлен на Рис. 134.
 |
Рисунок134 - Список конфет, проданных
Остальные записи таблицы не удалены, а временно скрыты.
Порядковые номера строк в списке не являются последовательными. Они выделены на листе синим цветом. В списке, где происходила фильтрация, стрелка фильтрации стала синего цвета.
4. 4. Восстановить список можно с помощью меню Данные команда Фильтр ~ Показать все или в списке критериев фильтрации выделить Все.
5. 5. Для задания своих критериев поиска используют Пользовательский автофильтр.
 |
Рисунок 135 - Диалоговое окно Пользовательский автофильтр
11. 11. Найдите товары с названием «Конфеты «Ромашка» и «Конфеты «Южная ночь».
12. 12. Щёлкните на стрелке фильтра поля Наименование товара. В списке ключей выберите Условие. Откроется диалоговое окно Пользовательский автофильтр (Рис135 ).
13. 13. Убедитесь, что в группе Наименование товара в верхнем поле операторов находится «равно».
14. 14. Щёлкните на стрелке соседнего поля и выделите строку Конфеты «Ромашка».
15. 15. Включите опцию ИЛИ.
16. 16. Щёлкните на стрелке нижнего поля критериев и выделите строку Конфеты «Южная ночь».
17. 17. Щёлкните на кнопке ОК. Список выглядит как на рис.136.
18. 18. Отмените Автофильтр. Команда Фильтр ~ Отобразить все меню Данные.
 |
.
Рисунок 136 - Результат выполнения операций с помощью Пользовательского автофильтра
Расширенный фильтр
С помощью расширенного фильтра можно отобразить результат фильтрации не только в самой исходной таблице, но ив любом другом месте электронной таблицы.
Для применения расширенного фильтра:
1. 1. Перед списком данных вставьте 3-4 пустые строки
2. 2. Сформируйте диапазон условий.
- - В первую пустую строку скопируйте заголовки фильтруемых столбцов.
- - Во вторую – под соответствующими заголовками введите условия отбора (Рис. 137)..
- - В ячейкуС2 – шоколад., а в D2 – 1.
3. 3. Выберите команду Данные ~ Фильтр ~ Расширенный фильтр( Рис.138), установив курсор внутри списка
Код заказа | Наименование товара | Тип продукции | Код фирмы | Цена заказа | Дата продажи |
1 | Конфеты "Василек" | шоколад. | 1 | 10 550,00р. | |
2 | Конфеты "Ромашка" | шоколад. | 2 | 5 800,00р. | |
3 | Конфеты "Загадка" | карамель | 2 | 3 200,00р. | |
4 | Конфеты "Москвичка" | карамель | 3 | 7 000,00р. | |
5 | Конфеты "Школьная" | шоколад. | 4 | 5 200,00р. | |
6 | Конфеты "Шалунья" | карамель | 2 | 2 700,00р. | |
7 | Конфеты "Южная ночь" | шоколад. | 1 | 4 500,00р. | |
8 | Конфеты "Слами" | карамель | 4 | 7 800,00р. | |
9 | Конфеты "Чародейка" | шоколад. | 3 | 2 600,00р. | |
10 | Конфеты "Орион" | шоколад. | 4 | 4 100,00р. |
Рисунок 137 - Таблица для применения Расширенного фильтра
4. 4. Укажите исходный диапазон и диапазон условий отбора, включая заголовки столбцов. Диапазон можно вводить с клавиатуры или с помощью мыши, указывая в таблице необходимые ячейки.
5. 5. Установите переключатель Обработка в положение Фильторвать список на месте диалогового окна Расширенный фильтр (Рис.138). при необходимости установите параметр Только уникальные записи, т. е. неповторяющиеся записи и нажмите кнопку OK
 |
Рисунок138 - Диалоговое окно Расширенный фильтр
6. 6. В результате будут найдены шоколадные конфеты, которые поставляет фирма. Результат применение расширенного фильтра представлен на Рис.139.
Код заказа | Наименование товара | Тип продукции | Код фирмы | Цена заказа | Дата продажи |
шоколад. | 1 | ||||
Код заказа | Наименование товара | Тип продукции | Код фирмы | Цена заказа | Дата продажи |
1 | Конфеты "Василек" | шоколад. | 1 | 10 550,00р. | |
7 | Конфеты "Южная ночь" | шоколад. | 1 | 4 500,00р. |
Рисунок 139 - Результат применения Расширенного фильтра
Отмените Расширенный фильтр командой меню Данные ~ Фильтр ~ Отобразить все. Произведите фильтрацию данных из таблицы по тому же критерию, но результат поместите в диапазон A17:F27. Для этого- - скопируйте заголовок таблицы в диапазон A17:F17;
- - выберите команду Данные ~ Фильтр ~ Расширенный фильтр (рис.140);
- - укажите исходный диапазон и диапазон условий отбора, включая заголовки столбцов;
- - установите переключатель Обработка в положение скопировать результат в другое место и установите диапазон A17:F27 в поле Поместить результат в диапазон;
- - нажмите кнопку OK.
 |
Рисунок 140 – Окно для задания диапазонов
Подведение промежуточных итогов
1. 1. Найти суммарную стоимость отдельных видов конфет, например шоколадных.
2. 2. Установите курсор в таблицу.
3. 3. В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги.
 |
Рисунок 141 - Диалоговое окно Промежуточные итоги
4. 4. Щёлкните на стрелке текстового поля При каждом изменении в: и в появившемся окне списка выделите строку Тип продукции.
5. 5. Убедитесь, что в поле Операция находится слово Сумма.
6. 6. Убедитесь, что в поле Добавить итоги по: флажок установлен только напротив строки Цена заказа.
7. 7. Установите флажки напротив строк Заменить текущие итоги и Итоги под данными., и щелкните на кнопке ОК.
8. 8.
 |
Вы получите итоговые значения суммарной стоимости каждого вида конфет. Рабочий лист выглядит как на рис.142:
Рисунок 142 - Рабочий лист с промежуточными итогами
1. 1. Для управления структурой таблицы Итоги, вы можете использовать кнопки:
· · Развернуть группу;
· · Свернуть группу;
· · Режим 1, Режим 2 и т. д.
2. 2. Отменить итоги можно с помощью команды Итоги меню Данные. В диалоговом окне Промежуточные итоги нажать кнопку Убрать все (Рис.141).
Дополнительные задания
1. В свою рабочую книгу добавьте лист с данными об имеющихся товарах. Таблица должна содержать следующие столбцы: Наименование товара, Тип продукции; Код фирмы, Стоимость заказа, Код заказа, Дата. Переименуйте лист в лист с именем Сортировка1.
2. Введите данные в таблицу, число записей должно быть не менее 10.
3. Добавьте в таблицу две записи, используя функцию Автоввод.
4. Скопируйте полученную таблицу еще на четыре отдельных листа, предварительно вставив их. Назовите листы: Сортировка2, Автофильтр1, Автофильтр2, Расширенный фильтр.
5. Отсортируйте список Код заказа по возрастанию.
6. Перейдите на лист Сортировка 2
7. Отсортируйте список по полю Тип продукции по возрастанию и по полю Наименование товара по убыванию.
8. Перейдите на лист Автофильтр.
9. Создайте Автофильтр.
10. Выберите записи находящиеся в столбце с именем Код заказа.
11. Перейдите на лист Автофильтр 2.
12. Задайте свои критерии поиска данных: Наименование товара начинается на определенную букву, используя Пользовательский автофильтр.
13. Перейдите на лист Расширенный фильтр
14. Выполните поиск записей с помощью Расширенного фильтра, поместив полученный результат на отдельную область электронной таблицы, задав диапазон условий: Цена заказа лежит в определенном диапазоне.
15. Скопируйте таблицу на пустой лист и назовите его Промежуточные итоги.
16. Добавьте промежуточные итоги, определив Стоимость по определенному виду товара.
17. Сохраните рабочую книгу
Формулировка задания: MS Excel. Создание сводных и консолидированных таблиц
Цель работы: Научиться создавать сводные и консолидированные таблицы, которые являются мощным аналитическим инструментом, позволяющим преобразовывать информацию из полей списков Microsoft Excel в том виде, которые будет удобен пользователю.
Контрольный пример
Запустите Microsoft Excel. Откройте книгу с контрольным примером. Добавьте в книгу новый рабочий лист и переименуйте его в лист с именем «Список». Скопируйте ранее созданную таблицу с листа «Сортировка» и отмените все условия сортировки, возвращая таблице первоначальный вид.Создание сводной таблицы
 |
Выделите любую ячейку в списке, на основе которого вы хотите создать сводную таблицу.
Рисунок 143 - Таблица исходных данных с листа «Список»
Выберите в пункте меню Данные команду Сводная таблица. Microsoft Excel запустит Мастер сводных таблиц и диаграмм – шаг 1 из 3. На данном шаге построения сводной таблицы указывается тип источника данных и вид создаваемого отчета. Установите переключатели согласно приведенному ниже рисунку и нажмите кнопку Далее. |
Рисунок 144 - Мастер сводных таблиц и диаграмм – шаг 1 из 3
Далее во втором окне Мастер сводных таблиц и диаграмм, вам требуется указать диапазон, в котором находятся исходные данные. Если вы правильно выполнили пункт 5 контрольного задания, выделили одну ячейку в списке, то мастер правильно заполнит это окно диалога, нажмите кнопку Далее.
Рисунок 145 - Мастер сводных таблиц и диаграмм – шаг 2 из 3
В последнем окне диалога, укажите место, где вы хотите расположить сводную таблицу, в нашем примере это новый лист, смотрите ниже приведенный пример, после нажмите кнопку Готово. |
Рисунок 146 - Мастер сводных таблиц и диаграмм – шаг 3 из 3 После нажатия кнопки Готово Microsoft Excel отобразит в новом рабочем листе пустой макет таблицы и выведет на экран панель инструментов Сводные таблицы с кнопками для каждого поля из источника данных. Для создания сводной таблицы, перетащите кнопки полей с панели инструментов в область макета:
- - в поле страницы поля Код заказа и Код фирмы;
- - в поле столбцов поле Вид продукции;
- - в поле строк поле Наименование товара;
- - в область данных поле Цена заказа.
 |
Рисунок 147 – Построение сводной таблицы После построения сводной таблицы с помощью мастера она должна иметь следующий вид:
Рисунок 148 – Сводная таблица
Переименуйте лист, на котором была создана сводная таблица в лист с соответствующим именем. Выделите любую ячейку списка сводной таблицы, на одноименной панели инструментов выберите пункт Сводная таблица, из предложенного списка выберите команду Параметры таблицы. В появившемся диалоговом окне Параметры сводной таблицы, уберите флажок в разделе Формат напротив поля Общая сумма по строкам и нажмите кнопку ОК. В поле Код фирмы из списка выберите фирму под номером 4 и нажмите кнопку ОК. После всех преобразований вы должны получить таблицу, приведенную ниже, по которой можно сделать следующие выводы: фирма под номеров 4 приобрела товар по следующим наименованиям, шоколадные конфеты «Орион» и «Школьная» на сумму 9300 тыс. руб. и карамель «Слами» на сумму 7800 тыс. руб.
Рисунок 149 – Итоговая сводная таблица
Создание консолидированной таблицы
На новом листе рабочей книги создайте таблицу, которая будет отображать информацию о продажах продукции за определенный месяц года, она будет иметь следующий вид:
Рисунок 150 – Таблица с исходными данными
Переименуйте лист с созданной таблицей в лист с именем «Январь». Создайте три копии листа «Январь». На второй, третьей и четвертой копии листа измените значения в столбцах Количество и Цена за единицу товара, таким образом, чтобы они отличались друг от друга по разным позициям товаров. Переименуйте данные листы соответственно в листы с названиями «Февраль», «Март», «Апрель». На новом листе рабочей книге произведите предварительные действия перед консолидацией данных. Скопируйте с любого листа месяца заголовки столбцов Наименование товара и Количество, сделайте активной ячейку А2.
Рисунок 151 – Подготовка листа Консолидация
Переименуйте данный лист в лист с названием «Консолидация». В пункте меню Данные выберите команду Консолидация и заполните окно диалога Консолидация. Выберите функцию Максимум в раскрывающемся списке Функция. В данном примере в области Использовать в качестве имен установите флажок в поле Значение левого столбца, для консолидации по строкам.
Рисунок 152 – Окно диалога Консолидация
В поле Ссылка введите или укажите с помощью мыши последовательно следующие исходные ссылки:· · Январь!$A$2:$B$11
· · Февраль!$A$2:$B$11
· · Март!$A$2:$B$11
· · Апрель!$A$2:$B$11
Ввод в это поле производится в следующем порядке :
· · вводим данные за январь в поле Ссылка;
· · нажимаем кнопку Добавить. Данные будут перенесены с область Список данных;
· · повторить выполнение выше описанных действий для остальных данных.
Рисунок 153 – Команда Консолидация использует ссылки из Списка диапазонов для вычисления консолидированных максимальных значений.
Нажмите кнопку ОК. Microsoft Excel вычислит максимальные исходные значения и поместит их в итоговый лист, представленные на рисунке 12.
Рисунок 154 – Диапазон В2:В11 в листе Консолидация содержит максимальные значения соответствующих ячеек в четырех исходных листах.
Сохраните файл рабочей книги.Дополнительные зедения
1. 1. В свою рабочую книгу добавьте лист с данными об имеющихся товарах. Таблица должна содержать следующие столбцы: Наименование товара, Вид продукции, Код фирмы, Цена заказа, Дата продажи. Скопируйте таблицу с предыдущих листов.
2. 2. Переименуйте лист в лист с именем Сводная таблица.
3. 3. Введите данные в таблицу, число записей должно быть не менее 10.
4. 4. Создайте на существующем листе своей рабочей книги сводную таблицу, с помощью которой можно проанализировать закупки фирмы по датам продажи.
5. 5. Отформатируйте параметры сводной таблицы по своему усмотрению.
6. 6. На новом листе рабочей книги создайте таблицу, которая будет отображать информацию о продажах продукции за определенный месяц. Таблица должна содержать следующие столбцы: Наименование товара, Цена за единицу товара, Количество, Цена заказа.
7. 7. Переименуйте лист по названию текущего месяца.
8. 8. Создайте три копии листа текущего месяца.
9. 9. Переименуйте листы по названиям других месяцев года.
10. 10. На данных листах внесите изменения значений в столбце Цена товара за единицу.
11. 11. Создайте на новом листе консолидированную таблицу, которая будет содержать средние значения цены за единицу товара по каждому наименованию продукции за четыре отчетных месяца.
12. 12. Переименуйте данный лист в лист с названием Консолидация.
13. 13. Сохраните рабочую книгу.
14. 14. Закройте все открытые файлы электронной таблицы.
15. 15. Скопируйте результат на свою дискету
