Лабораторная работа № 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. Как сохранить книгу на диске?