Обработка и анализ больших массивов данных в электронных таблицах

Цель работы: обобщить и систематизировать умения и навыки работы с табличным процессором; продолжить формировать умения решать задачи с вводом формул и использованием стандартных функций табличного процессора.

Критерии оценки работы:

отметка «отлично» выставляется при выполнении без ошибок и недочетов шести заданий (включая самостоятельную работу по вариантам);

отметка «хорошо» выставляется при выполнении без ошибок и недочетов пяти заданий (включая самостоятельную работу по вариантам);

отметка «удовлетворительно» выставляется при выполнении без ошибок и недочетов четырёх заданий (включая самостоятельную работу по вариантам);

отметка «неудовлетворительно» выставляется при выполнении менее трёх заданий.

Студенты, получившие за работу неудовлетворительную оценку, должны выполнить её повторно. После оценки «неудовлетворительно» – оценку «отлично» студент – не получает.

Задание №1. Вам представлена ​​таблица данных, содержащая информацию о земельных участках в N-районе. В таблице приведены следующие данные: Номер участка, Площадь участка (га), Кадастровая стоимость участка (руб.), Тип почвы, Коэффициент плодородия, Ставка налога (руб./га). Рассчитайте налог на каждый участок по формуле: Налог = Площадь участка × Ставка налога. Заполните новый столбец Налог (руб.). Определите среднюю кадастровую стоимость участка для каждого типа земли. Определите самую большую площадь участка с коэффициентом плодородия выше 0,8.

Откройте заготовку к практической работе в папке преподавателя на рабочем столе.

Сохраните документ в свою рабочую папку с именем ПР10_Фамилия_ЭКЗ-000.

На листе 1 оформите таблицу (см. Таблица 1)

Отформатируйте заголовок таблицы. Выделите первую строку, нажав на номер строки, выберите сверху жирное начертание, выравнивание – по центру, центрирование вертикально и перенос текста (см. Рис. 1).

Рис. 1. Настройки заголовка

Все столбцы выровняйте – по центру.

Выделите числа столбца B, откройте окно Формат ячеек командой Ctrl + 1 – вкладка Числа и установите формат – Числовой, дробная часть – 1 (Рис. 2). Первоначально выделяются все необходимые ячейки, а затем выбирается команда на исполнение.

Рис. 2. Формат ячеек

Выделите числа столбца C, откройте окно Формат ячеек командой Ctrl + 1 – вкладка Числа и установите Разделитель разрядов.

Введите название столбцов таблицы (см. Таблица 1). Изменение ширины столбцов производите перемещением мышью в строке имён столбцов (А, В, С и т. д.).

Таблица 1 – Исходные данные для задания 1

Произведите расчёты в столбце Налог по формуле:

Налог = Площадь участка × Ставка налога

Введите расчетную формулу в ячейку G2, далее произведите автокопирование формулы. Для автокопирования формулы выполните следующие действия: подведите курсор к маркеру автозаполнения, расположенному в правом нижнем углу ячейки; когда курсор примет вид чёрного крестика, зажмите ЛКМ и протяните формулу вниз по ячейкам.

Для ячеек столбцов C, F и G задайте формат Денежный (Ctrl + 1 – вкладка Числа → категория Денежный, формат – «–1 234 ₽», дробная часть – 0.

Добавьте обрамление таблицы (рис. 6). Для этого выделите ячейки, в которых есть данные, откройте окно Формат ячеек – вкладка Обрамление. Задайте любой цвет линии (кроме белого). В графе Положение линий выберите Внешние и все внутренние линии.

Рис. 3. Обрамление таблицы

Выделив ячейки с результатами расчётов, выполните заливку ячеек цветом по вашему выбору (окно Формат ячеек – вкладка Фон → Цвет).

Определим среднюю кадастровую стоимость участка для каждого типа земли. Для этого выделите заголовок таблицы A1:G1, выберите Автофильтры во вкладке Данные. Раскройте фильтр столбца D, выберите только Чернозём (Рис. 4).

Рис. 4. Фильтрация данных

Для определения среднего значения можно воспользоваться строкой состояния, которая находится внизу. В отфильтрованной таблице выделите диапазон C2:C13. Внизу справа, будет посчитано среднее значение, количество и сумма (Рис. 5).

Рис. 5. Фрагмент строки состояния

При подсчёте среднего значения можно пользоваться функцией СРЗНАЧ, но тогда формула в отсортированной таблице будет выглядеть так:

=СРЗНАЧ($C$3; $C$4; $C$5; $C$6; $C$9; $C$10; $C$13)

Каждую ячейку нужно зафиксировать при помощи кнопки F4 (после нажатия будут добавлены $).

Внесите формулу, представленную выше в ячейку B22.

Отмените фильтр.

Аналогично определите среднюю кадастровую стоимость участка для песчаного типа земли и суглинка. Занесите данные в ячейки B23 и B24 соответственно.

В ячейке C25 посчитайте сумму участков, для проверки правильности вычислений используя функцию СУММ: =CУMM(С22:С24). В итоге должно получиться 15.

Определим самую большую площадь участка с коэффициентом плодородия выше 0,8. Для этого откройте фильтр в столбце Коэффициент плодородия и выберите Фильтр по условию – Стандартный фильтр… . Создайте условие:

Коэффициент плодородия > 0,8

Откройте фильтр в столбце Площадь участка (га) и выберите Сортировать по убыванию. Самый большой участок отобразится на первом месте. Занесите данные в ячейку C26.

Верните таблицу в исходный вид.

Итоговый вид таблицы представлен на Рис. 6.

Рис. 6. Фрагмент оформленной таблицы задания №1

Переименуйте ярлычок Лист1 в Задание 1. Для этого дважды щёлкните мышью по ярлычку и наберите новое имя (Рис. 7).

Рис. 7. Переименование ярлычка

Выполните текущее сохранение документа Ctrl + S.

Задание №2. Создать таблицу финансовой сводки за неделю.

Перейдите на второй лист, оформите таблицу (см. Таблица 2).

Таблица 2 – Исходные данные для задания 2

Произведите расчёты в столбце Е. Помните, что расчётные формулы вводятся только в верхнюю ячейку столбца, а далее они копируются вниз по колонке. Формула для расчета:

Финансовый результат = Доход – Расход

В ячейках C10, D10, E10 вычислите среднее значение за неделю, используя функцию СРЗНАЧ.

В ячейке E11 выполните расчёт сумы значений данных за неделю с понедельника по воскресенье. Для выполнения суммирования большого количества данных удобно пользоваться кнопкой Выбор функции на панели инструментов (Рис. 8).

Рис. 8. Выбор функции

Для этого установите курсор в ячейку E11, нажмите на кнопку Выбор функции и выберите функцию Сумма, выделите диапазон E3:E9. Произойдет сложение данных столбца E.

Задайте линии ячеек таблицы и проведите форматирование созданной таблицы и заголовка.

Переименуйте ярлычок Лист 2 на Задание 2.

Выполните текущее сохранение Ctrl + S.

Задание №3. Создать и заполнить таблицу, произвести расчёты и форматирование таблицы.

Перейдите на третий лист, оформите таблицу (см. Таблица 3).

Таблица 3 – Исходные данные для задания 3

Настройте в соответствующих диапазонах форматы денежный и процентный.

Сумму брака рассчитайте по формуле:

Сумма брака = Процент брака × Сумму зарплаты

Вычислите минимальное, максимальное и средние значения, используя функции.

Выполните текущее сохранение Ctrl + S.

Задание №4. Заполните таблицу «Анализ доходов от продаж товаров для землеустройства», произвести расчёты и форматирование ячеек.

Перейдите на четвёртый лист. Выполните форматирование данных в таблице (см. Таблица 4). Настройте в соответствующих диапазонах денежный формат.

Таблица 4 – Исходные данные для задания 4

Выполните текущее сохранение Ctrl + S.

Задание №5. Вы являетесь сотрудником компании, занимающейся землеустройством и продажей земельных участков. Вам предоставлены данные о 10 участках, которые распределены по трем категориям: земли сельскохозяйственного назначения, земли под индивидуальное жилищное строительство (ИЖС), земли коммерческого использования. Определите площадь участка в каждой категории. Рассчитайте выручку от продажи каждой категории. Для участка, обозначенного как «Продано», выручка рассчитывается по формуле: Выручка = Площадь участка × Цена за 1 га. Определите площадь и потенциальный доход, которые еще не проданы.

Перейдите на пятый лист. Выполните форматирование данных в таблице (см. Таблица 5). Настройте в соответствующих диапазонах денежный формат.

Таблица 5 – Исходные данные для задания 5

Рассчитайте выручку от продажи каждой категории. Для участка, обозначенного как «Продано», выручка рассчитывается по формуле:

Выручка = Площадь участка × Цена за 1 га.

Определите общую площадь участков в каждой категории, запишите ответ в ячейки C20, C21, C22.

Определите площадь земли, которую еще не продали, запишите ответ в ячейку C23.

Определите потенциальный доход от земли, которую еще не продали, запишите ответ в ячейку C24.

Выполните текущее сохранение файла Ctrl + S.

Задание №6. Для решения задач по обработке и анализу больших массивов данных в электронных таблицах используй фильтрацию данных в электронных таблицах.

Рассмотрим задачи, где необходимо определить показатели для данных, которые соответствуют какому-то условию (например, для определения дней, месяцев, городов, предметов или групп). Такими показателями могут быть:

количество значений;

среднее арифметические значений;

сумма значений;

максимальное или минимальное.

В столбце A записан код округа, в котором учится студент; в столбце B – код фамилии студента; в столбце C – выбранный студентом предмет; в столбце D – тестовый балл. Всего в электронную таблицу были занесены данные по 1000 студентам.

Сколько студентов, которые проходили тестирование по информатике, набрали более 600 баллов? Ответ запишите в ячейку H2 таблицы.

Каков средний тестовый балл студентов, которые проходили тестирование по информатике? Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.

Решение: задания такого типа удобно решать, используя «фильтры». Фильтр – это предусмотренная в программе – электронной таблице возможность отбирать значения в столбцах, удовлетворяющие некоторому условию. В качестве условия можно выбирать значения даты, населенных пунктов, классов, групп. Использование фильтров показано в задании 1.

Откройте Лист6.

Выделите заголовок таблицы A1:D1 и включите Автофильтр . Вид заголовка изменится (Рис. 9). Небольшие треугольники в первой строке – это инструмент, позволяющий использовать фильтр в том или ином столбце.

Рис. 9. Автофильтр

Щёлкните мышью на треугольник столбца С (Рис. 10). Видно, что в нижней части перечислены все значения из столбца С в алфавитном порядке без повторений. Если убрать «галочку» у каких-либо значений, то строки с такими значениями на листе отображаться не будут.

Рис. 10. Стандартный фильтр столбца С

Настроим фильтром так, чтобы остались только строки с предметом «Информатика» и определим количество этих строк. В раскрывшемся автофильтре столбца С, уберите «галочку» на варианте «Все» и поставьте галочку на строке «информатика», после чего нажать кнопку ОК.

В результате, останутся только строки с нужным предметом. Их количество будет указано в нижней левой части окна программы: 72 of 1000 records found / 72 из 1000 найденных.

В решаемой нами задаче следует найти студентов, которые набрали по информатике более 600 баллов.

Используем стандартный фильтр в столбце D (Фильтр по условию – Стандартный фильтр). Создайте дополнительное условие (Рис. 11).

Рис. 11. Создание сложного условия

После применения второго фильтра искомое значение также появится в нижней левой части окна программы: selected: 36 rows, 4 columns / выделено: 36 строк, 4 столбца, но нужно учитывать, что одна строка – заголовок таблицы. В итоге всего по данному условию будет отобрано 35 студентов.

Ответ запишите в ячейку H2 таблицы.

Отмените стандартный фильтр (Данные – Еще фильтры – Удалить фильтр).

Определим средний тестовый балл студентов, которые проходили тестирование по информатике.

Выберите команду Данные – Промежуточные итоги (Рис. 12).

Рис. 12. Промежуточные итоги

Будут сформированы итоги по каждому предмету. Найдите итоги по предмету информатика и запишите их в ячейку H3 таблицы с точностью не менее двух знаков после запятой.

Отмените группировку промежуточных итогов. Для этого выберите команду Данные – Промежуточные итоги и отмените всё (см. Рис. 13).

Рис. 13. Отмена промежуточных итогов

Построим круговую диаграмму с количеством участников в округах «С», «Ю», «СВ». Левый верхний угол диаграммы разместим вблизи ячейки G6. В поле диаграммы должны присутствовать легенда (обозначение соответствия данных определённому сектору диаграммы) и числовые значения данных, по которым построена диаграмма.

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

Заполняем на том же листе с данными небольшую таблицу (см. Таблица 6).

Таблица 6 – Таблица данных для создания диаграммы

Выделяем диапазон J1:L2 и на вкладке Вставка – Диаграмма выбираем Круговая – Готово.

Перемещаем диаграмму так, чтобы её верхний левый угол находился вблизи ячейки G6.

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

Откройте заготовку и задание для самостоятельной работы по вариантам1. Задание и заготовка находится в папке преподавателя.

Требования к оформлению отчёта

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

Каждое новое задание выполняется на отдельном листе, но в одной книге.

Каждое задание (ярлычок) должно быть подписано по образцу (например, Задание 1). Недопустимо название листа Лист1, Лист2 и т. д.

Файл должен быть назван в соответствии с требованиями в практической работе и сохранён в папке студента. При неправильном названии файла оценка будет снижена.

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

Задание, выполненное без расчётных формул (за исключением заданий с фильтрами и сортировкой), считается полностью невыполненным.