По горизонтали - по центру
По вертикали – по центру
- Выделите ячейки А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 и диаграмма примет вид (рис. 11Рисунок 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):
Рисунок 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 - Окно мастера функций
- В ячейку B3 введите текст Дата переучета.
-
В ячейку С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")
- Введите в ячейку G14 формулу для определения суммы просроченных платежей:
=СУММЕСЛИ(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.
Код заказа
| Наименование товара
| Тип продукции
| Код фирмы
| Цена заказа
| Дата продажи
|
| Конфеты "Василек"
| шоколад.
|
| 10 550,00р.
| 02-04-10
|
| Конфеты "Ромашка"
| шоколад.
|
| 5 800,00р.
| 02-04-25
|
| Конфеты "Загадка"
| карамель
|
| 3 200,00р.
| 02-04-25
|
| Конфеты "Москвичка"
| карамель
|
| 7 000,00р.
| 05-04-28
|
| Конфеты "Школьная"
| шоколад.
|
| 5 200,00р.
| 02-05-07
|
| Конфеты "Шалунья"
| карамель
|
| 2 700,00р.
| 02-05-25
|
| Конфеты "Южная ночь"
| шоколад.
|
| 4 500,00р.
| 02-05-27
|
| Конфеты "Слами"
| карамель
|
| 7 800,00р.
| 02-05-17
|
| Конфеты "Чароднейка"
| шоколад.
|
| 2 600,00р.
| 02-05-14
|
| Конфеты "Орион"
| шоколад.
|
| 4 100,00р.
| 02-05-18
|
Рисунок130 - Таблица с исходными данными
Сортировка данных
1. 1. Отсортируйте данные по наименованию товара, для этого выполните следующие действия:
- - Установите курсор в поле таблицы;
- - В меню Данныевыберите командуСортировка. В открывшемся диалоговом окне в группеСортировать пощёлкните на стрелке «вниз» и в списке заголовков столбцов выделите строку Наименование товара(Рис 131).
Данные в группе будут отсортированы по наименованию товара и расположены в алфавитном порядке.
- - Убедитесь , что текстовые поля в группах Затем по и В последнюю очередь пусты, и щёлкните на кнопке ОК.
5. 5. Отменить сортировку можно выбрав команду Отменить сортировку в менюПравка. Данные будут находиться в прежнем порядке.
Рисунок 131 - Диалоговое окноСортировка диапазона
6. 6. Отсортируйте по двум критериям: по Наименованию товара и по Дате продажи, для этого:
- - В меню Данныевыберите команду Сортировка.
- - Убедитесь, что в текстовом поле группы Сортировать по находится заголовок Наименование товара и включите опцию по убыванию.
- - В группе Затем пощелкните на кнопке «вниз» и выделите ключ Дата продажи. Щёлкните на кнопкеОК.
Фильтрация списков с помощью автофильтра
1. 1. Для фильтрации списков выполните следующие операции:
- - Установите курсор текущей ячейки в таблицу.
- - В меню Данные выберите команду Фильтр ~ Автофильтр. В заголовках каждого столбца появятся кнопки фильтра со стрелочками вниз.(Рис. 132)
Стрелки фильтра
Рисунок 132 - Таблица со стрелками фильтра
2. 2. Щёлкните на стрелке фильтра в ячейке Дата продажи. В появившемся окне списка критериев необходимо выбрать нужный критерий (ключ) фильтрации.
Критерии фильтрации
Рисунок 133 -Список критериев фильтрации
3. 3. В открывшемся списке критериев выберите Дату продаж 02-04-25, т.е. какие конфеты были проданы в этот день. Результат выполнения операции представлен на Рис. 134.
Рисунок134 - Список конфет, проданных 02-04-25
Остальные записи таблицы не удалены, а временно скрыты.
Порядковые номера строк в списке не являются последовательными. Они выделены на листе синим цветом. В списке, где происходила фильтрация, стрелка фильтрации стала синего цвета.
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), установив курсор внутри списка
Код заказа
| Наименование товара
| Тип продукции
| Код фирмы
| Цена заказа
| Дата продажи
|
| Конфеты "Василек"
| шоколад.
|
| 10 550,00р.
| 02-04-10
|
| Конфеты "Ромашка"
| шоколад.
|
| 5 800,00р.
| 02-04-25
|
| Конфеты "Загадка"
| карамель
|
| 3 200,00р.
| 02-04-25
|
| Конфеты "Москвичка"
| карамель
|
| 7 000,00р.
| 05-04-28
|
| Конфеты "Школьная"
| шоколад.
|
| 5 200,00р.
| 02-05-07
|
| Конфеты "Шалунья"
| карамель
|
| 2 700,00р.
| 02-05-25
|
| Конфеты "Южная ночь"
| шоколад.
|
| 4 500,00р.
| 02-05-27
|
| Конфеты "Слами"
| карамель
|
| 7 800,00р.
| 02-05-17
|
| Конфеты "Чародейка"
| шоколад.
|
| 2 600,00р.
| 02-05-14
|
| Конфеты "Орион"
| шоколад.
|
| 4 100,00р.
| 02-05-18
|
Рисунок 137 - Таблица для применения Расширенного фильтра
4. 4. Укажите исходный диапазон и диапазон условий отбора, включая заголовки столбцов. Диапазон можно вводить с клавиатуры или с помощью мыши, указывая в таблице необходимые ячейки.
5. 5.Установите переключатель Обработкав положение Фильторвать список на месте диалогового окнаРасширенный фильтр (Рис.138). при необходимости установите параметр Только уникальные записи,т.е. неповторяющиеся записи и нажмите кнопку OK
Рисунок138 - Диалоговое окно Расширенный фильтр
6. 6. В результате будут найдены шоколадные конфеты, которые поставляет фирма. Результат применение расширенного фильтра представлен на Рис.139.
Код заказа
| Наименование товара
| Тип продукции
| Код фирмы
| Цена заказа
| Дата продажи
|
|
| шоколад.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Код заказа
| Наименование товара
| Тип продукции
| Код фирмы
| Цена заказа
| Дата продажи
|
| Конфеты "Василек"
| шоколад.
|
| 10 550,00р.
| 02-04-10
|
| Конфеты "Южная ночь"
| шоколад.
|
| 4 500,00р.
| 02-05-27
| Рисунок 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 отобразит в новом рабочем листе пустой макет таблицы и выведет на экран панель инструментов Сводные таблицы с кнопками для каждого поля из источника данных. Для создания сводной таблицы, перетащите кнопки полей с панели инструментов в область макета: - - в поле страницы поля Код заказа и Код фирмы;
- - в поле столбцов поле Вид продукции;
Не нашли, что искали? Воспользуйтесь поиском по сайту:
©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|