Электронные таблицы. Сортировка и поиск данных. Диаграммы

Сегодня на уроке мы вспомним:

· как организовать сортировку данных:

· как осуществить поиск данных;

· как построить диаграмму.

Сортировка – это упорядочивание данных в таблице.

Вспомним основные виды сортировки.

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

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

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

Фильтр – это средство отображения только тех строк таблицы, которые соответствуют заданным условиям.

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

Давайте вспомним на примере, как осуществить сортировку и поиск данных.

В таблице представлены данные о ценах на книги в разных магазинах.

Сколько магазинов продаёт книгу «Мастер и Маргарита» по минимальной цене?

Сначала узнаем максимальную цену на книгу «Мастер и Маргарита».

Сделать это можно так:

· отсортировать таблицу по возрастанию по названию книги;

· при равных значениях названия книги отсортировать таблицу по возрастанию цены на книгу.

Ставим курсор в пределах таблицы.

На вкладке «Главная» в группе «Редактирование» нажимаем на кнопку «Сортировка и фильтр» и выбираем пункт «Настраиваемая сортировка».

В открывшемся окне в выпадающем списке «Столбец» выбираем название столбца «Книга».

Обратите внимание: в выпадающем списке «Сортировка» предоставлено четыре варианта сортировки, оставляем «Значение ячеек».

Так как мы сортируем по возрастанию, то в выпадающем списке «Порядок» выбираем «от А до Я».

Нажимаем кнопку «ОК».

Так как много магазинов продают книгу «Мастер и Маргарита», то выполним сортировку по возрастанию цены на книгу.

На вкладке «Главная» в группе «Редактирование» нажимаем на кнопку «Сортировка и фильтр» и выбираем пункт «Настраиваемая сортировка».

В открывшемся окне нажимаем на кнопку «Добавить уровень».

В выпадающем списке «Столбец» выбираем название столбца «Цена».

Сортировку оставляем «Значение ячеек».

И порядок выбираем «По возрастанию».

Нажимаем кнопку «ОК».

По результатам сортировки видно, что 3 магазина продают книгу «Мастер и Маргарита» по минимальной цене.

Но если бы данных было намного больше, то, чтобы не считать самому и не ошибиться, можно было бы воспользоваться встроенной функцией СЧЁТЕСЛИ.

Выберем любую ячейку, например, Е2 и назовём её «Количество».

Ставим курсор в ячейку Е3.

Нажимаем на кнопку «Вставить функцию».

В поле «Поиск функции» пишем название нужной функции – СЧЁТЕСЛИ.

Нажимаем на кнопку «Найти».

В поле «Выберите функцию» выбираем нужную.

И нажимаем кнопку «ОК».

В открывшемся окне выбираем диапазон.

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

И нажимаем кнопку «ОК».

В ячейке «Количество» появилось значение 3.

Теперь давайте в исходной таблице отфильтруем таблицу так, чтобы в ней были показаны данные о магазинах, которые продают книги «Властелин колец» по цене больше 1000 рублей.

Для этого на вкладке «Главная» в группе «Редактирование» нажимаем на кнопку «Сортировка и фильтр» и выбираем пункт «Фильтр».

В столбце «Книга» нажимаем на эту кнопку.

Убираем галочку в пункте «Выделить все».

А затем ставим галочку напротив «Властелин колец».

И нажимаем кнопку «ОК».

Чтобы вывести с ценами выше тысячи, необходимо в столбце с названием «Цена» нажать на эту кнопку.

В выпадающем списке нажимаем на пункт «Числовые фильтры» и выбираем пункт «Больше».

В появившемся окне вводим нужное значение, в данном случае тысячу.

И нажимаем кнопку «ОК».

Как видим, поиск удался.

Чтобы вернуть таблице исходный вид, то есть чтобы были показаны все строки таблицы, необходимо на вкладке «Главная» в группе «Редактирование» нажать на кнопку «Сортировка и фильтр» и выбрать пункт «Очистить».

Как мы помним, в Microsoft Excel можно не только обработать большой объём информации, но и вывести числовые данные в более наглядном виде за счёт их графического представления.

Основным средством графического представления табличных данных является диаграмма.

Диаграмма – это графическое отображение числовых данных и соотношений между ними.

С помощью диаграмм можно:

· отобразить данные в удобном для восприятия виде;

· подчеркнуть какую-либо характеристику;

· сравнить данные;

· найти закономерности в данных и так далее.

 На одном листе можно расположить несколько диаграмм.

Диаграмма может находиться на отдельном листе.

Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются, если изменяются данные на листе.

Перед тем как вспомнить типы часто используемых диаграмм в Microsoft Excel, вспомним некоторые понятия.

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

Категории – наборы соответствующих друг другу значений из разных рядов.

Выбор вида диаграммы зависит от задачи, которую необходимо с помощью неё решить.

Гистограмма, или столбчатая диаграмма, – это диаграмма, на которой значения отображены в виде столбцов разной площади.

Гистограмма может показывать изменение данных за какой-то промежуток времени и иллюстрирует соотношение отдельно взятых данных.

Линейчатая диаграмма – это гистограмма, повёрнутая на 90 градусов.

График используется для иллюстрации непрерывных изменений.

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

У графиков, гистограмм, линейчатых диаграмм и диаграмм с областями существуют такие версии, как «нормированная» и «с накоплением».

Нормированная диаграмма показывает долю каждой компоненты в общей сумме.

А диаграмма с накоплением отображает вклад отдельных элементов в общую сумму.

Следующий вид диаграмм – это круговая. Такая диаграмма иллюстрирует соотношение величин, то есть процентные показатели частей по отношению к общему значению. Диаграмма представляется в виде круга, который разделён на секторы пропорционально отображаемым значениям. На такой диаграмме показывается только один ряд данных.

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

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

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

Диаграмма – это составной объект, который может содержать в себе различные элементы:

· название диаграммы;

· область диаграммы;

· область построения диаграммы;

· ряды данных;

· подписи данных;

· легенду, поясняющую принятые обозначения;

· оси.

Давайте построим гистограмму, в которой отобразим данные о ценах на книгу «Мастер и Маргарита» в разных магазинах.

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

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

Для добавления диаграммы необходимо на вкладке «Вставка» в группе «Диаграммы» выбрать тип диаграммы.

Назовём диаграмму «Цены на книгу “Мастер и Маргарита” в разных магазинах».

На вкладке «Главная» можно изменить шрифт названия, его цвет, начертание и так далее.

Нижний ряд чисел в диаграмме необходимо заменить на названия магазинов.

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

Чтобы добавить дополнительные элементы диаграммы, например, линии сетки, легенду, метки данных и так далее, необходимо на вкладке «Конструктор диаграмм» в группе «Макеты диаграмм» нажать на кнопку «Добавить элемент диаграммы» и выбрать нужное. Причём у каждого элемента есть несколько вариантов его расположения.

На этой же вкладке в группе «Стили диаграмм» можно выбрать интересное оформление диаграммы.

Воспользовавшись кнопкой «Переместить диаграмму» в группе «Расположение», можно разместить диаграмму на отдельном листе или на уже имеющемся листе.

Изменим тип диаграммы на круговую.

Вызываем контекстное меню и выбираем пункт «Изменить тип диаграммы…».

Находим в списке «Круговая». Выбираем её вид.

И нажимаем кнопку «ОК».

Добавим метки данных у края, снаружи.

И добавим легенду справа.

Рассмотрим решение типичного задания по обработке данных в электронной таблице, которое может быть на ОГЭ по информатике. Обычно это задание под номером четырнадцать.

Как написано в задании, расположение файла с электронной таблицей вам сообщат организаторы экзамена. Ну а у нас уже подготовленный файл.

Итак, открываем файл и видим таблицу с большим количеством записей.

Можно выделить один из столбцов, чтобы узнать, сколько записей содержит таблица.

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

Для начала найдём для каждого учащегося сумму баллов по двум предметам, если его оценки удовлетворяют условию, то есть по географии ИЛИ информатике стоят баллы больше 50.

Вычислять сумму будем в столбце Е.

Ставим курсор в ячейку Е2.

Вводим знак равно.

Нам понадобится для вычисления суммы с условиями в данном случае две функции: ЕСЛИ и ИЛИ.

После знака равно начинаем вводить слово Если, как только на экране появляется название нужной нам функции, щёлкаем по нему два раза левой кнопкой мыши.

Так как в условии задания написано «…получивших больше 50 баллов по географии ИЛИ информатике», то вводим ещё название функции ИЛИ. Теперь нажимаем на кнопку «Вставить функцию» и с помощью мастера функций вводим аргументы значений. Можно, конечно, вводить формулу вручную, но легко ошибиться при вводе, и к тому же с мастером функций это будет быстрее. Итак, первым логическим значением будет, чтобы баллы по географии были больше пятидесяти, значит, ставим курсор в ячейку С2 и вводим знак больше и 50.

Вводим следующее логическое значение. Чтобы баллы по информатике были больше 50.Ставим курсор в ячейку D2 и вводим знак больше и 50. Нажимаем кнопку «ОК».

Появится окно с предупреждением. Нажимаем кнопку «ОК» и продолжаем ввод формулы.

Ставим курсор возле любой буквы в слове ЕСЛИ и нажимаем на кнопку «Вставить функцию».

Во втором аргументе мы должны ввести значение, которое должно вернуться, если логическое выражение будет иметь значение ИСТИНА. В нашем случае необходимо вывести сумму баллов по двум предметам.

Ставим курсор в поле второго аргумента.

Нажимаем на ячейку C2 вводим знак плюс и нажимаем на ячейку D2.

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

Значит, ставим в поле третьего аргумента и вводим пустые кавычки.

Нажимаем кнопку «ОК».

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

=ЕСЛИ(ИЛИ(C2>50;D2>50);C2+D2;"")

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

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

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

Теперь найдём наименьшую сумму баллов по двум предметам среди школьников, получивших больше 50 баллов по географии или информатике.

Ставим курсор в ячейку F3.

На вкладке «Формулы» в группе «Библиотека функций» нажимаем на кнопку «Автосумма» и выбираем функцию «Минимум».

Диапазон сразу выбран неверно, поэтому нажимаем на кнопку «Вставить функцию».

В открывшемся окне удаляем значения в поле «Число 1».

И выделяем столбец Е.

Такая запись, означает, что выбраны все значения столбца Е.

Нажимаем кнопку «ОК».

У нас получилась вот такая формула:

=МИН(E:E)

Можно было, конечно, записать формулу такую, и это будет тоже верно.

=МИН(E2:E273)

Ответ на первый вопрос получен – 75.

Переходим ко второму вопросу.

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

В ячейке, например, I2, выведем количество участников, которые набрали по географии меньше 60 баллов. В этом нам поможет функция СЧЁТЕСЛИ.

В результате получили 129 участников.

Выразим полученное значение в процентах об общего числа участников.

В ячейку F5 введём вот такую формулу.

Уменьшим разрядность полученного ответа.

Ответ на второй вопрос получен – 47,4.

Теперь построим диаграмму, которая отображает соотношение учеников из школ «2», «5» и «6».

В ячейке L2 подсчитаем количество участников из школы номер два, в ячейке M2 – из школы номер 5, а в ячейке N2 – из школы номер 6

Воспользуемся функцией СЧЁТЕСЛИ.

Теперь построим круговую диаграмму по полученным значениям и подпишем сектора.

Название диаграммы можно удалить, про него ничего не сказано.

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

В конце урока попробуйте ответить на следующие вопросы:

Что такое фильтр?

Круговая и кольцевая диаграммы выглядят одинаково?

Как называется множество значений, которое нужно отобразить на диаграмме?

Внимательно посмотрев урок, вам не составит труда ответить на вопросы.