Лабораторная работа № 4
Excel. Статистические функции. Электроннаятаблица как база данных. Организация разветвлений
Цель: уметь пользоваться диапазонами ячееки стандартными статистическими функциями, исключать, вставлять столбцы и строкив таблицу, искать и упорядочивать данные, подводить итоги, строитьматематические выражения.
Задача 1. «Деятельность фирмы в Украине»
Пусть ваша фирма имеет филиалы в Киеве,Харькове, Львове, Одессе, Донецке или других городах и есть данные об объемахпродажи в филиалах. По данным о деятельности фирмы на протяжении трех месяцев,например, января, февраля, марта, создать таблицу для определения объемовпродаж: максимальных, минимальных и в целом на Украине. Кроме этого, создатьновую таблицу – проект бизнес-плана на последующие два месяца: апрель, май – срасширением географии деятельности фирмы (названия двух-трех городов добавитьсамостоятельно).
Задача 2. «Табулирование функции ивычисление площади»
Протабулировать функцию y = n(sinx2+1),где n – номер варианта, и вычислить площадь под кривой методом левыхпрямоугольников. Отрезок, на котором рассматривать функцию, и шаг табулированияh задать самостоятельно (в таблице должно быть 10-12 строк).
Теоретические сведения
Несколько ячеек рабочей таблицы, которыеимеют смежные стороны, образуют диапазон ячеек.
Диапазоны имеют прямоугольную форму иописываются адресами двух диагонально-противоположных ячеек. Например: А1: С3 –прямоугольный диапазон; А1: А9 – диапазон-столбец; А1: Е1 – диапазон-строка.
Чтобы выделить диапазон, нужно щелкнуть влевом верхнем углу и, не отпуская кнопки, переместить белый крестообразныйкурсор в правый нижний угол, отпустить кнопку. Чтобы отказаться от выбора,достаточно щелкнуть за пределами диапазона.
Чтобы выделить несмежные диапазоны, нужнопользоваться клавишей Ctrl. Например, чтобы выделить два несмежныхстолбца-диапазона, нужно щелкнуть на их названиях в режиме нажатой клавишиCtrl.
Диапазонам можно давать названия ииспользовать эти названия вместо выражений типа А1: А9. Программа сама даетназвания диапазонам, если она может их однозначно распознать. Например, втаблице на рис. 1 названия столбцов распознаются автоматически, поэтому в ячейкуЕ4 вместо формулы =B4+C4+D4 можно ввести формулу =Январь+Февраль+Март.
Как известно, для выполнения вычисленийиспользуют формулы. Формула имеет вид =выражение. Рассмотрим правилаобразования выражений. Приоритеты выполнения операций в выражениях такие как вэлементарной математике. Приведем их в убывающем порядке:
Приоритет Операции Пояснения
1 ( ) операциив скобках, аргументы функций;
2 sin, cos и др. математическиеи другие функции;
3 % проценты;
4 ^ возведениев степень (-5^2=25);
5 * или / умножениеили деление;
6 + или - сумма илиразность;
7 & объединениетекстов;
8 =,,>= операциисравнения.
Стандартных функций есть несколькокатегорий: математические – sin, cos, exp, ln, abs, atan, sqrt и др., а такжефункции для работы с матрицами; статистические – СРЗНАЧ, МИН, МАКС, СУММ и др.;логические; финансовые; для работы с датами, текстами и др.
Функции могут быть определены над числами,адресами ячеек, адресами (названиями) диапазонов и их списками. Элементы списказаписывают через разделитель, который определяется операционной системой:запятую, если в числах используется десятичная точка, или точку с запятой,например, так, =СУММ (А1; В6: С8; 20).
Поскольку суммы вычисляют чаще всего, напанели инструментов есть кнопка Автосумма. Ею пользуются так: выделяют ячейкупод столбцом или правее от строки с данными и щелкают на кнопке Автосумма –получают нужную сумму (числовых данных из соответствующего столбца или строки).
Действия над элементами ЭТ (выделеннымиячейками, столбцами, строками, диапазонами, всей таблицей) выполняют командамиконтекстного или основного меню или при помощи кнопок панели инструментов.
Например, при необходимости в таблицувставляют пустые строки (столбцы) или исключают их командами: Редактировать> Вставить или Исключить.
В выделенную ячейку можно вставитьпримечание, которое поясняет ее назначение, командой Вставить > Примечание,а исключить командой Редактировать > Очистить (или средствами контекстногоменю).
Рассмотрим еще один способ быстроговведения текстовых данных в таблицу. Он состоит в использовании списковпользователя. Списки могут содержать названия товаров, городов, фирм, фамилии ит.д. Список пользователь сначала создает командами Сервис > Параметры >Вкладка Списки > Новый список > Вводить элементы списка через запятую илинажимая клавишу ввода > Добавить > ОК. Список используют так: первыйэлемент списка вводят в какую-либо ячейку, перетаскивают ее маркер копирования– осуществляется автозаполнение таблицы элементами списка.
ЭТ можно использовать как базу данных.Рассмотрим стандартные действия, которые можно выполнять с данными: 1)упорядочить строки по возрастанию или убыванию значений в некотором столбце; 2)искать данные по некоторым критериям. Столбец с данными здесь называют полем.
Упорядочение. Сначала выбирают частьтаблицы с данными и названиями полей или всю таблицу (без заголовка таблицы истрок с итогами). Сортировку выполняют командой Данные > Сортировать,получают список названий полей, где выбирают нужное название, например Город, изадают порядок сортировки: по возрастанию или убыванию – получают таблицу, гдестроки будут упорядочены в алфавитном или обратном порядке названий городов.
Поиск данных называют иначе фильтромданных. Сначала выбирают строку, которая содержит названия столбцов, ивыполняют команду Данные > Фильтр > Автофильтр. Ячейки с названиемстолбцов становятся списками с кнопками развертывания. Разворачивают нужныйсписок, например Январь, выбирают в списке значение Условие – открывается окноконструктора условий. В нем есть удобные средства для формулирования критерияпоиска по столбцу Январь, например такого: больше 500000 и меньше 2000000.После этого нажимают на ОК и на экране получают результаты поиска – строкитаблицы с городами, где показатель деятельности фирмы в январе удовлетворяетданному критерию. Чтобы восстановить на экране всю таблицу, выполняют командуДанные > Фильтр > Показать все.
Если нужно получить сложный критерий набазе названий нескольких столбцов, то используют команду Данные > Фильтр> Расширенный фильтр.
Итоги в таблицах. Итоги подводят с цельюопределения лучших, худших, суммарных, средних показателей деятельности фирмы внескольких странах, городах, подразделениях и т.д. Для этого сначала строки втаблице сортируют с целью группирования (размещения рядом) данных, которыекасаются каждой страны, города или подразделения для получения итогов купорядоченной таблице применяют команду Данные > Итоги, где задают: 1)название поля, содержащее объекты, для которых создают итоги, например Страна;2) операцию суммирования и 3) название поля, содержащее данные, которыеподлежат суммированию (например, Всего или/и Март). Операции суммированиябывают разные: сумма, максимум, минимум, среднее значение, отклонение от нормыи т.д.
Задание
1. Запустите программу ЭТ, откройтеновую книгу и создайте список пользователя с названиями городов.
1.2. Введите данные для решения задачи1, как показано на рис. 1. Далее введите данные самостоятельно еще для трехгородов
Числа в столбец Е и строки 10-13 невводить!
3. Введите формулы для решения задачи1. В ячейке Е4 вычислите сумму чисел строки 4.
Выберите ячейку Е4 и нажмите на кнопкуАвтосумма, а затем на кнопку ввода – получите формулу =СУММ(B4:D4).
4. В ячейке В10 вычислите сумму чиселв столбце В.
5. Скопируйте формулу из ячейки Е4вниз в диапазон Е5: Е10.
6. Скопируйте формулу из ячейки В10правее в диапазон C10:D10.
7. В ячейках В12: Е12 определитемаксимальные значения в столбцах данных.
Введите формулу =МАКС(В4: В8) в ячейку В12и скопируйте ее правее в диапазон С12: Е12.
8. Определите минимальные значения встолбцах.
Выберите ячейку В13 и нажмите на кнопкуВставка функции fx, выберите в диалоговом окне функцию МИН > ОК. Введите вследующем окне диапазон В4: В8 и нажмите на ОК.
9. Скопируйте формулу из ячейки В13 вдиапазон С13: Е13. Запишите в отчет общий объем продажи за три месяца.
10. Задайте формат чисел Числовой бездесятичных знаков после запятой и с разделителем групп трех разрядов.
Выберите все числовые данные в таблице>Формат > Ячейки > Число > Числовой > Включите режим разделятьгруппы разрядов и задайте количество десятичных цифр после запятой: 0 > ОК.
11. Отцентрируйте заголовки в первыхдвух строчках относительно столбцов А-Е.
Выберите диапазон А1: Е1 и нажмите накнопку Объединить и поместить в центре (буква а со стрелками) на панелиинструментов.
12. Скопируйте всю таблицу в буферобмена и вставьте ее на лист 2.
13. На листе 2, используя старуютаблицу, создайте новую таблицу Прогноз объемов продажи на два месяца, грн.
Дополните таблицу столбцами с названиямиАпрель, Май, и Всего2. Данные для апреля и мая придумайте и введитесамостоятельно. Запишите в отчет, какой объем продажи планирует фирма в апрелеи мае (отдельно и вместе).
14. Спрячьте и покажите столбец Е.
Выберите столбец Е и примените командуФормат > Столбец > Спрятать. Чтобы применить команду показать, нужносначала выделить два столбца, между которыми есть спрятанный.
15. Очистите строки 12 и 13.
16. Отсортируйте строки таблицы валфавитном порядке названий городов.
17. Отсортируйте филиалы (строки) по убываниюобъемов продажи в первом квартале. Запишите в отчет, какой филиал на первомместе.
18. Примените к таблице автофильтр,чтобы вывести строки с названиями филиалов, которые в феврале имели объемпродажи свыше 500 000.
19. Выведите строки с названиямифилиалов, которые в марте имели объем продажи больше, чем 200 000 и меньше, чем600 000.
20. Скопируйте основную таблицу наследующий лист и отформатируйте ее наилучшим образом.
Числа отобразите в денежном формате. Дляэтого выберите все числовые данные и задайте нужный формат. Дополните таблицустолбцом с номерами телефонов фирм. Номера телефонов введите как девятизначныечисла и примените команду Формат > Ячейки > Дополнительный > Номертелефона.
21. Сохраните книгу на диске сназванием Фамилия2.
22. Откройте новую книгу.
23. Введите в ячейки А1, А2, А3, А5,В5, С5 входные данные решения задачи 2 так, как показано на рис. 2.
24. Задайте режим Допускать названиядиапазонов.
Сервис > Параметры > ВкладкаВычисления. Если этот режим задан, то программа автоматически будет даватьдиапазонам-столбцам названия полей-столбцов. Эти названия можно использовать вформулах. Если диапазону нужно дать другое название, то см. пункт 25.
25. Введите в ячейку D4 значение шага идайте ячейке имя h.
Введите число и выделите эту ячейку.Примените команду Вставить > Имя > Присвоить > Введите h > Добавить> ОК.
26. Введите формулы решения задачи 2так:
Адрес: Формулы:
А6 0
В6 =SIN(x^2)+1
С6 =h*y
27. Скопируйте формулы из ячеек А7, В6,С7 вниз до конца рабочей таблицы – получите площади всех левых прямоугольниковв столбце С. В столбце С очистить последнее значение.
28. Чтобы получить площадь под всейкривой, вычислите сумму площадей всех левых прямоугольников.
Щелкните под столбцом С и применитекоманду-кнопку Автосумма. Ответ запишите в отчет.
29. В ячейку со значением площадивставьте такое примечание: Этот результат получил .
29.30. Вычислите среднееарифметическое всех значений функции.
31. Назовите рабочий лист Площадь.
32. Отформатируйте числовые значения результатов,чтобы количество цифр было оптимальным.
33. Отформатируйте таблицу, чтобы онаимела наилучший вид.
34. Сохраните книгу на диске в личнойпапке.
35. Оформите отчет и защитите работу.
Контрольные вопросы
1. Назначение формул в ЭТ. Какоезначение функции МИН(5; 2; 13)?
2. Что такое копирование формул?Значение функции СУММ(4; 7; 12)
3. Как ячейке дать имя?
4. Что такое диапазон ячеек?Приведите примеры диапазонов.
5. Опишите приоритеты выполненияопераций в выражениях.
6. Как исключить/вставить строку илистолбец из таблицы?
7. Назначение примечаний и как ихвставлять?
8. Как выполнить поиск нужных данныхв таблице?
9. Назначение кнопки Автосумма.
10. Как скопировать таблицу наследующий лист?
11. Как заполнить столбец числами,которые образуют арифметическую прогрессию?
12. Как отцентрировать заголовоктаблицы относительно столбцов?
13. Как создать список пользователя?
2.14. Как заполнить столбец или строкуэлементами списка пользователя?
2.15. Как сохранить книгу на диске?