Обработка данных с помощью средств MS Excel

 

Таблица 5

 

Таблица 6

 
 

 

Вариант 8 

   1. Создайте таблицу как на рис. 1. 

Рис. 1. Список расходов

   2. Используя форму данных, добавьте в список данные об АО Престиж: «30.06.97. Материалы. $800, АО Престиж».

   3. Используя форму данных, просмотрите информацию о Казакове и измените сумму зарплаты за 05.09.99 на $2800.

   4.  Используя форму данных, просмотрите все данные списка о расходах на материалы, превышающих $12000.

   5. Используя Автофильтр, отобразите  все данные списка по АО  ИНВЕСТ.

   6. Используя Автофильтр, отобразите  все данные списка по накладным  расходам, а затем только за  июнь.

   7. По каждому расходу подведите  итог по полю Сумма, воспользовавшись  командой Данные > Итоги, предварительно  отменив Автофильтр и отсортировав  данные списка по полю Расход.

   8. Присвоите имя (например, имя Список) диапазону ячеек, содержащему все данные списка (включая заглавную строку).

   9. Вставьте перед диапазоном со  списком 11 пустых строк.

   10. В ячейки А1:01 скопируйте шапку  таблицы.

   11. Используя  Расширенный фильтр, отобразите все данные списка  по зарплате, используя для области  критериев ячейки А1:В2. Изменив  область критериев, отобразите  все данные списка по накладным  расходам.

   12. Внесите изменения в область  критериев, добавив в ее шапку  еще одну ячейку с названием  Сумма, так чтобы отобразились  накладные расходы только от  $500 до $1000.

   13. Используя Расширенный фильтр, поместите  в любую пустую область рабочего  листа все данные списка о  накладных расходах и зарплате  за июль, предварительно изменив  область критериев, либо создав  новую.

   14. Используя Расширенный фильтр  и новую область критериев,  рядом с полученным результатом  поместите данные списка только  по зарплате за июнь и июль, указав дату, сумму и получателя. Для этого предварительно создайте  шапку новой таблицы (3 ячейки).

   15. Измените область критериев, оставив  в качестве критерия только  вид расхода — зарплату.

   16. Для заданного критерия отбора  вычислите общую сумму:

   • в ячейку В6 введите формулу расчета  суммы, используя Мастер функции  БДСУММ. Для указания диапазона базы данных выберите из списка имен ячеек (в левой части строки формул) имя соответствующего диапазона (Список), для задания поля укажите с  помощью мыши ячейку с названием  поля Сумма, для задания диапазона  критериев также воспользуйтесь мышью:

   • в ячейку В5 введите текст «Сумма по заданному критерию».

   17. Используя функцию БСЧЕТ, подсчитайте  в ячейке С6 количество выданных  зарплат. Имя поля, указываемого  в окне Мастера функций, оставьте  прежним. В ячейку С5 введите  текст «Количество».

   18.  Подсчитайте сумму расходов и  количество записей по материалам, изменив область критериев. (Область значений при этом изменится автоматически.) 19. Сформируйте таблицу для расчета суммы и количества расходов по каждому виду расхода, скопировав в ячейки А7, А8 и А9 значения поля Расходы: Материалы, Зарплата и Накладные расходы. В ячейках В6 и С6 рассчитайте общую сумму и количество расходов, очистив критерий поиска.

   20. Для автоматической подстановки  значений из ячеек А7, А8 и  А9 в ячейку В2 области критериев  и построчного получения результатов  в сформированной таблице, выделите  диапазон А6:С9 и выполните команду  Данные ® Таблица подстановки.

   21. Сформируйте еще одну таблицу  для расчета количества и суммы  расходов, связанных с АО ИНВЕСТ, ТОО Надежда и ЗАО БИН, а также найдите максимальные и минимальные из них, воспользовавшись возможностью автоматической подстановки значений в область критериев для получения соответствующих результатов.

   22. Измените в сформированной по  предыдущему пункту таблице значения  получателей на другие значения (например, скопировав на их место  фамилии получателей). Область значений  при этом изменится автоматически.

   23. Сформируйте еще одну таблицу  для расчета количества каждого  из расходов по АО Престиж,  ЗАО БИН и Васильевой М.Ф., для  этого значения получателей запишите  в разных строках под ячейкой  с формулой, а значения расходов  — в разных столбцах, правее  ячейки с формулой. Выделите сформированную  таблицу и выполните команду  Данные - Таблица подстановки, указав, в какую ячейку области критериев  значения будут подставляться  построчно, а в какую — из столбцов.

   24. Создайте на новом рабочем  листе сводную таблицу, позволяющую отобразить количество расходов, связанных с каждым получателем. Для этого поместите в область строк значения поля Получатель, а в область столбцов — значения поля Расход (в область данных нужно поместить поле Расход и, щелкнув на нем 2 раза указателем мыши, выбрать функцию для подсчета количества значений по этому полю).

   25. Переместите поле Расход из  области столбцов в область  строк (для этого можно воспользоваться  Мастером сводных таблиц, вызвав  его либо с помощью соответствующей  кнопки панели инструментов Сводные  таблицы, либо выбрав соответствующую  команду из контекстного меню).

   26. На этом же рабочем листе,  начиная с новой ячейки, создайте  еще одну сводную таблицу, позволяющую отобразить в один столбец сумму расходов, связанных с каждым получателем, предусмотрев возможность фильтрации по полю Расход (поле Расход в этом случае нужно поместить в область страниц, а поле Получатель — в область строк).

   • Отобразите данные по зарплате.

   • Используя Мастер сводных таблиц, добавьте в область данных количество значений по полю Получатель.

   • Отобразите данные по накладным расходам.

   • Используя панель инструментов Сводные таблицы (кнопка Отобразить страницы), отобразите информацию по каждому расходу.

   27. На этом же рабочем листе,  начиная с новой ячейки, создайте  еще одну сводную таблицу, позволяющую отобразить в один столбец сумму расходов по каждой дате.

   28. Сгруппируйте полученные данные  по месяцам, воспользовавшись  контекстным меню или соответствующей  кнопкой панели инструментов  Сводные таблицы.

 

Вариант 9

1. Создайте таблицу, используя базу данных деклараций, приведённую в табл. 1.
2. Найти, на какую сумму было закуплено мужского, женского и детского трикотажа (код 61*) по отдельности и всего трикотажа ежеквартально. Для этого необходимо провести отбор записей согласно выбранному критерию и необходимые вычисления, используя методы анализа данных в списках.
3. По сводной таблице постройте диаграмму.
4. Оформите отчет с обоснованием выбора метода и описанием последовательности действий при выполнении задания.

Таблица 1 

 

 

Вариант 10

   1. Создать таблицу (рис. 1), начиная с третьей строки:

• шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф, расположение по центру столбца;
• текст «Объем страховых сделок» расположить по центру четырех столбцов;
• названия граф «№ п/п», «Комиссионное вознаграждение» расположить в 2 ячейки по вертикали, объединив эти ячейки;
• для граф «№ п/п», «за III квартал», «Комиссионное вознаграждение» — задать перенос по словам;
• названия граф «июль», «август», «сентябрь» ввести, используя автозаполнение.

   2. Ввести фамилии, после этого  переопределить ширину столбца.  В, сделав его равным максимальной  длине фамилии.

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

   4. Ввести числовые данные в столбцы  D, Е и F.

   5. Автоматически пронумеровать все  фамилии в столбце А.

   6. Сделать сортировку всей таблицы  по фамилиям (без столбца А).