Дидактический материал по MS Excel Локтева Л.Р., учитель информатики средней общеобразовательной школы 1 г.Тосно с углубленным изучением отдельных предметов

Сортировка- это упорядочивание данных (числа, текст, даты, логические значения) в электронных таблицах. Можно сортировать по возрастанию или по убыванию. При сортировке по возрастанию данные выстраиваются в определенном порядке: - числа сортируются от наименьшего отрицательного до наибольшего положительного числа; - текст сортируется в следующем порядке: числа, знаки, латинский алфавит, русский алфавит; - все ошибочные значения равны; - пустые ячейки всегда помещаются в конец списка.

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

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

Порядок выполнения сортировки по нескольким столбцам 1.Укажите ячейку в сортируемом списке. 2.В меню Данные выберите команду Сортировка 3.На диалоговой панели Сортировка диапазона в списке Сортировать по выбрать название нужного столбца и установить переключатель в положении по возрастанию. В списке Затем по выбрать название нужного столбца, и если необходимо в списке В последнюю очередь, по выбираете нужный столбец.

Примечание Если сортировка ведется по нескольким столбцам, то строки с одинаковыми значениями в столбце, указанном в поле Сортировать по, сортируются в порядке, определяемом столбцом, указанным в поле Затем по. Строки с одинаковыми значениями в первых двух столбцах сортируются по столбцу, указанному в поле В последнюю очередь, по

Пример Имеется таблица с данными: фамилия, имя и отчество. Произведем упорядочение данных в таблице по столбцам, содержащих Фамилия, Имя и Отчество.

2. На диалоговой панели Сортировка диапазона в списке Сортировать по выбираем Фамилия и устанавливаем переключатель в положение по возрастанию 1. Выделяем ячейку с Фамилией и вводим команду Данные-Сортировка…

3. В списке Затем по выбираем название Имя и установить переключатель в положение по возрастанию.

4. В списке В последнюю очередь, по название Отчество, переключатель устанавливаем по возрастанию.

5. После нажатия кнопки ОК мы получим отсортированную таблицу.

В Электронных таблицах можно осуществлять поиск данных (строк) в соответствии с заданными условиями. Такие условия называются фильтром. В результате поиска будут найдены те строки, которые удовлетворяют заданному фильтру, а остальные строки будут скрыты. Для этого служат команды Автофильтр и Расширенный фильтр.

Выбор данных с помощью Автофильтра Фильтры могут быть использованы только для одного списка на листе. 1.Укажите ячейку в фильтруемом списке. 2. Выберите пункт Фильтр в меню Данные, а затем команду Автофильтр. 3. Чтобы отфильтровать строки, содержащие определенное значение, нажмите кнопку со стрелкой в столбце, в котором содержатся искомые данные. 4. Выберите значение в списке. 5. Повторите шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других столбцах. Чтобы отфильтровать список по двум значениям в одном столбце или применить отличные от равно операторы сравнения, нажмите кнопку со стрелкой, а затем выберите пункт Условие.

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

Пример Имеется таблица, содержащая следующие данные: Фамилия, Имя, Рост. Нам необходимо найти все строки, которые содержат информацию о росте не менее 160 см., и не более 175см.

2. Выбираем пункт Условие… для ввода пользовательск их условий. 1. Выделяем одну ячейку с данными и вводим команду Данные-Фильтр-Автофильтр. В названиях столбцов таблицы появятся раскрывающиеся списки, содержащие стандартные условия поиска.

3. На диалоговой панели Пользовательский автофильтр в первом операторе ввести условия поиска больше или равно и значения 160, во-втором больше или равно, значения 175.

4. В результате будут найдены четыре строки, удовлетворяющие заданному фильтру.

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

3. Введите в строки под заголовками условий требуемые критерии отбора. Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка. 4. Укажите ячейку в списке. 5. В меню Данные выберите команду Фильтр, а затем команду Расширенный фильтр. 6. Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель «Обработка» в положение Фильтровать список на месте.

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

Советы Если присвоить диапазону имя Критерии, то ссылка на диапазон будет автоматически появляться в поле Диапазон условий. Можно также определить имя База_данных для диапазона фильтруемых данных и имя Извлечь для области вставки результатов, и ссылки на эти диапазоны будут появляться автоматически в полях Исходный диапазон и Поместить результат в диапазон соответственно. Когда отобранные строки копируются в другое место, можно указать столбцы, включаемые в копию. Перед фильтрованием скопируйте заголовки нужных столбцов в первую строку области вставки результатов. При фильтровании введите ссылку на скопированные заголовки столбцов в поле Поместить результат в диапазон. Скопированные строки будут включать только столбцы, заголовки которых были скопированы.

Виды условий отбора. Последовательности символов. Чтобы отобрать строки, содержащие ячейки с заданным значением, введите требуемые число, дату, текстовую или логическую константу в ячейку ниже названия столбца диапазона условий. Например, чтобы отобрать строки, в которых индекс отделения связи равен , введите в диапазоне условий число ниже заголовка «Индекс отделения связи». При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Например, при вводе условия Бел будут отобраны строки с ячейками, содержащими слова Белов, Беляков и Белугин. Чтобы получить точное соответствие отобранных значений заданному образцу, например текст, следует ввести следующую формулу: =''=текст'

Значения в заданных пределах. Чтобы отобрать строки с ячейками, имеющими значения в заданных пределах, следует использовать оператор сравнения. Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобрать строки, имеющие значения ячеек большие или равные 1000, введите условие отбора >=1000 ниже заголовка. Подстановочные знаки. Чтобы отобрать строки с ячейками, содержащими последовательность символов, в некоторых позициях которой могут стоять произвольные символы, следует использовать знаки подстановки. Знак подстановки эквивалентен одному символу или произвольной последовательности символов.

Примечания. При вычислениях Microsoft Excel не учитывает регистр букв. В Microsoft Excel подстановочные знаки и операторы сравнения, содержащиеся в начале, середине или конце строки, допустимы только для текстовых значений, но не для числовых. ИспользуетсяЧтобы найти ? (знак вопроса)Любой символ в той же позиции, где указан знак вопроса. Например, для поиска «барин» или «барон» следует ввести «бар?н». * (звездочка)Любое количество символов в той же позиции, где указана звездочка. Например, для поиска слов «северо-восток» и «юго-восток» следует указать «*восток». ~ (тильда), за которой следует ?, * или ~ Знак вопроса, звездочку или тильду. Например, для поиска «ан91?» следует указать «ан~?».

Пример Имеется таблица, которая содержит информацию для выбора провайдера (название провайдера, почасовая оплата, количество входных, скорость, Web-сайт провайдера).

Мы будем искать оптимального провайдера, т.е. провайдера, у которого почасовая оплата достаточно низкая ( 500), и он обладает высокоскоростным доступом в Интернет (скорость канала >100 Мбит/с). 1. Выделим заголовки фильтруемых столбцов списка и нажмите кнопку Копировать 2. Выделим первую пустую строку диапазона условий отбора и нажмите кнопку Вставить

3. Введите в строки под заголовками условий требуемые критерии отбора. 4. Выделяем ячейку в списке и выполняем команду Данные Фильтр-Расширенный фильтр.

6. В результате будет найдена и отмечена запись На диалоговой панели Расширенный фильтр в поле Диапазон условий вводим ссылку на диапазон условий отбора, включающий заголовки столбцов.

Задание В каком порядке должны быть размещены данные таблицы после сортировки по возрастанию? Выпишите результат в тетрадь, не прибегая к помощи компьютера). 3++$96ОльгаD В каком порядке должны быть размещены данные таблицы после сортировки по убыванию? Выпишите результат в тетрадь, не прибегая к помощи компьютера АринаЦ451-33Г11V55

Задание Создайте таблицу по образцу.

2.2. Отсортируйте данные таблицы таким образом, чтобы на каждый день недели иметь перечень спектаклей по месту их показа Получите список всех спектаклей, поставленных по произведениям А. Островского Сколько записей удовлетворяет заданному критерию? 2.5. Когда идут выбранные спектакли? 2.6. Отберите те названия спектаклей, которые начинаются от 18:30 до 19: Выберите спектакли, которые идут в выходные дни.

Задание Создайте таблицу по образцу.

3.2. Используя расширенный фильтр найдите название стран, удовлетворяющих следующим условиям: Категория отеля должен бить не ниже 1***, Питание должно быть включено и Авиабилеты входят в стоимость путевки Найти отель в Турции, в котором Стоимость путевки не превышает 130, Категория отеля не выше 1*** и питание- шведский стол Найти отель трехзвездочный, в отеле должен быть прокат автомобилей, питание должно входить в стоимость тура.