По горизонтали - по центру

По вертикали – по центру

-  Выделите ячейки А2:А8 и С2:Е9. Для этого, выделив первый диапазон данных, нажмите клавишу CTRL и, не отпуская её, выделите второй диапазон;

-  Воспользуйтесь пунктом меню Формат~Ячейки~Выравнивание~

По горизонтали - по центру;

Обратите внимание на то, что в последней строке таблицы несколько ячеек объединены. Чтобы добиться этого необходимо выделить ячейки А9:D9 и воспользоваться пунктом меню Формат~Ячейки~Выравнивание~Объединение ячеек;

Рисунок 101 -Исходный текст шапки документа

1. Выполните форматирование введенных данных и настройте формат их расположения:

·  Для форматирования шрифта:

-  Выделите диапазон ячеек А1:F5;

-  Воспользуйтесь пунктом меню Формат~Ячейки~Шрифт

Шрифт - Times New Roman Cyr; Размер – 12;

·  Выделите цветом, те данные шапки таблицы, которые могут изменяться:

-  Выделите ячейку F5;

-  Воспользуйтесь пунктом меню Формат~Ячейки~Вид~Заливка ячеек~Цвет~Светло-серый

-  Выполните аналогичные действия для ячеек С4, С5, D2, D4.

1. Переименуйте лист рабочей книги:

-  Выполните двойной щелчок мыши на имени первого листа Лист 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 - Окно Мастера диаграмм – шаг 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. Откройте книгу контрольного примера
2. Добавьте в книгу еще один рабочий лист и назовите его Фирма-клиент
3. 
   Создайте таблицу по следующему образцу (рис.1), начиная с ячейки A1.

Рисунок 1 - Таблица «Фирмы-клиенты»

1. Присвойте столбцам листа имена: Код, Фирма, Руководитель, Адрес, Телефон. Для этого необходимо выделить целый столбец листа электронной таблицы например А, а затем дать команду Вставка – Имя - Присвоить. В открывшемся окне Присвоение имени (рис.2) в поле Имя ввести название Код и нажать клавишу ОК.

Рисунок 2 - Окно для присвоения имени

1. Повторите описанную выше операцию для всех столбцов таблицы, присвоив им соответствующие имена.
2. Добавьте в книгу контрольного примера еще один лист и назовите его Ревизия
3. В ячейку B1 введите текст Ведомость просроченных платежей на
4. В ячейку B2 введите функцию для определения сегодняшней даты. Для этого используйте мастер функций (рис.3): в категории Дата и время выберите функцию СЕГОДНЯ().

Рисунок 3 - Окно мастера функций

1. В ячейку B3 введите текст Дата переучета.
2. 
   В ячейку С2 введите функцию для задания даты в числовом формате. Для этого используйте мастер функций: В категории Дата и время выберите функцию ДАТА. В качестве списка параметров введите год, месяц и число даты введенной с помощью функции СЕГОДНЯ.

Рисунок 4 - Функция ДАТА

На листе рабочей книги у вас должны получиться записи подобные тем, что показаны на рис.5. Датой формирования контрольного примера является 20 мая 2002 года.

Рисунок 5 – Результат вычислений функций

1. В ячейки A5:E10 введите заголовки столбцов таблицы со следующими названиями: Код заказа, Наименование товара, Код фирмы, Цена заказа, Дата продажи.
2. Наименование товаров скопируйте с листа Накладная.
3. Код фирмы и цену заказа введите как показано на рис.127.
4. В ячейку F5 введите заголовок столбца Дата оплаты
5. В ячейку G5 введите заголовок столбца Просрочка дней.
6. Отредактируйте записи Е6:Е10, вводя дату продажи с помощью функции ДАТА (см п.10).
7. В ячейки F6 и F9 введите даты оплаты по заказам 30 и 28 апреля текущего года соответственно.
8. В ячейку G6 введите формулу для определения количества просроченных дней по оплате заказа =ЕСЛИ(F6=0;$C$3-E6;0).
9. Протяните полученную формулу для ячеек столба G.

В результате проведенных действий у вас должна получиться следующая таблица

Рисунок 6 - Таблица заказов

А в ячейках таблицы должны находиться следующие формулы:

Рисунок 7 - Расчетные формулы

1. Введите в ячейку D13 текст «Количество просроченных заказов».
2. Введите в ячейку D14 текст «Стоимость просроченных заказов».
3. Введите в ячейку G13 формулу для определения количества просроченных заказов:

=СЧЁТЕСЛИ(G6:G10;">0")

1. Введите в ячейку G14 формулу для определения суммы просроченных платежей:

=СУММЕСЛИ(G6:G10;">0";D6:D10)

1. Введите в ячейку A17 формулу для формирования вывода по полученным данным. Это формула сцепления, она имеет следующий вид:

=СЦЕПИТЬ("На сегодняшний день в фирме неоплаченных заказов ";G13;" на сумму ";G14;" рублей")

1. Сформируем таблицу с данными о клиентах просрочивших платежи. В ячейку B19 введем заголовок таблицы «Фирмы неоплатившие заказы».
2. В ячейки A20:E20 введем наименования столбцов таблицы – Код фирмы, Наименование фирмы, Руководитель, Адрес, Телефон.
3. В столбец Код фирмы введем коды организаций, просрочивших платежи – 2,4.
4. Для заполнения таблицы остальными данными будем использовать функцию ПРОСМОТР. Введем в ячейку B21 функцию

=ПРОСМОТР($A21;Код;фирма).

1. Введем в ячейку C21 функцию

=ПРОСМОТР($A21;Код;Руководитель)

1. Введем в ячейку D21 функцию

=ПРОСМОТР($A21;Код;Адрес)

1. Введем в ячейку E21 функцию

=ПРОСМОТР($A21;Код;Телефон)

1. 
   Скопируем формулы в строки таблицы. У вас должна получиться таблица представленная на рис.8

Рисунок 8 - Вывод результатов расчетов

1. Сохраните файл рабочей книги.

Формулировка задания: 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.

Рисунок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 - Результат применения Расширенного фильтра

1. Отмените Расширенный фильтркомандой менюДанные ~ Фильтр ~ Отобразить все.
2. Произведите фильтрацию данных из таблицы по тому же критерию, но результат поместите в диапазон 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 в том виде, которые будет удобен пользователю.

Контрольный пример

1. Запустите Microsoft Excel.
2. Откройте книгу с контрольным примером.
3. Добавьте в книгу новый рабочий лист и переименуйте его в лист с именем «Список».
4. Скопируйте ранее созданную таблицу с листа «Сортировка» и отмените все условия сортировки, возвращая таблице первоначальный вид.

Создание сводной таблицы

1. 
   Выделите любую ячейку в списке, на основе которого вы хотите создать сводную таблицу.

Рисунок 143 - Таблица исходных данных с листа «Список»

1. Выберите в пункте меню Данные команду Сводная таблица.
2. Microsoft Excel запустит Мастер сводных таблиц и диаграмм – шаг 1 из 3. На данном шаге построения сводной таблицы указывается тип источника данных и вид создаваемого отчета. Установите переключатели согласно приведенному ниже рисунку и нажмите кнопку Далее.

Рисунок 144 - Мастер сводных таблиц и диаграмм – шаг 1 из 3

1. 
   Далее во втором окне Мастер сводных таблиц и диаграмм, вам требуется указать диапазон, в котором находятся исходные данные. Если вы правильно выполнили пункт 5 контрольного задания, выделили одну ячейку в списке, то мастер правильно заполнит это окно диалога, нажмите кнопку Далее.

Рисунок 145 - Мастер сводных таблиц и диаграмм – шаг 2 из 3

1. В последнем окне диалога, укажите место, где вы хотите расположить сводную таблицу, в нашем примере это новый лист, смотрите ниже приведенный пример, после нажмите кнопку Готово.

Рисунок 146 - Мастер сводных таблиц и диаграмм – шаг 3 из 3

1. 
   После нажатия кнопки Готово Microsoft Excel отобразит в новом рабочем листе пустой макет таблицы и выведет на экран панель инструментов Сводные таблицы с кнопками для каждого поля из источника данных. Для создания сводной таблицы, перетащите кнопки полей с панели инструментов в область макета:

- - в поле страницы поля Код заказа и Код фирмы;

- - в поле столбцов поле Вид продукции;

 

1234

---

Не нашли, что искали? Воспользуйтесь поиском по сайту:

©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.