Лабораторная работа: Статистические функции в Excel. Электронная таблица как база данных. Организация разветвлений
ЭТ можно использовать как базу данных. Рассмотрим стандартные действия, которые можно выполнять с данными: 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.