Тема: Ввод и редактирование формул. Формат ячеек

⇐ ПредыдущаяСтр 2 из 4Следующая ⇒

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

Ввод формул

Выполнение расчетов в таблицах Excel осуществляется при помощи формул.

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства «=». В эту последовательность могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы.

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

Пример №1: Вычислим корни квадратного уравнения . В ячейках A2, B2 и C2 (рис. 2) находятся значения коэффициентов a, b и c соответственно (a=1, b=-5, c=6).

Рис. 2. Окно Excel с вычислением корней квадратного уравнения

Последовательность действий:

Корни квадратного уравнения вычисляются по формулам:

, .

Следовательно, в ячейках A4 и B4 записано:

=(-B2+(B2^2-4*A2*C2)^0, 5)/(2*A2)

=(-B2-(B2^2-4*A2*C2)^0, 5)/(2*A2)

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

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

Если изменить значение в ячейке A2 с 1 на -1, то в ячейках A4 и B4 будут получены результаты -6 и 1.

Пример №2: Вычислим стоимость каждого товара, исходя из его цены и количества.

Последовательность действий:

На листе 2 оформить «шапку» таблицы и столбец A, как показано на рисунке 3.

Рис. 3. Таблица расчёта стоимости товара

Значения в столбце Цена имеют денежный формат. По умолчанию информация выводится в формате Общий.

Можно изменить формат представления информации в выделенных ячейках с помощью команды Формат → Ячейки, предварительно выделив столбец со значениями, формат которых необходимо изменить. Появится окно Формат ячеек, в котором нужно выбрать вкладку Число (рис. 4).

Рис. 4. Окно Формат ячеек вкладка Число

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

В нашем случае следует выбрать формат Денежный и установить в поле Обозначение формат «р.».

В окне Формат ячеек можно полностью настроить все свойства ячеек:

Выравнивание – по горизонтали и по вертикали;
Ориентация – вертикальная, горизонтальная, под наклоном;
Шрифт – размер и цвет текста;
Границы – обрамление ячеек;
Вид – заливка ячеек и т.д.

Окно Формат ячеек можно также вызвать нажатием правой кнопки мыши по выделенным ячейкам и выбором Формат ячеек.

Для расчёта стоимости товаров необходимо в ячейке D2 ввести формулу =B2*C2, которая перемножает значения в полях Цена и Количество. Посчитанное значение также будет представлено в денежном формате.

Рис. 5. Таблица расчёта стоимости товара

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

Далее в таблице товаров можно вычислить величину налога, которая может меняться (рис. 6).

Рис. 6. Таблица расчёта величины налога

Ставка налога в ячейках H2: Н4 имеет Процентный формат. Величина налога в ячейках Е2: Е4 рассчитывается как произведение стоимости товара на ставку налога. Итог рассчитывается путём сложения значений в полях Стоимость и Налог.

Автоформат

Оформление таблицы можно изменить на более красочное (рис. 7).

Рис. 7. Автоформат таблицы

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

В открывшемся окне выбрать любой тип оформления таблицы (рис. 8).

Рис. 8. Окно Автоформат меню Формат

Сортировка

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

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

Рис. 9. Окно Сортировка меню Данные

В появившемся окне в разделе Сортировать по… выбрать имя нужного столбца. Указать порядок сортировки – по возрастанию или по убыванию. Если необходимо отсортировать значения в нескольких столбцах таблицы, то следует выбрать названия этих столбцов в разделах Затем по… и В последнюю очередь по...

Задания для самостоятельной работы

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

Таблица 1

Сведения о студентах и оценках

Фамилия	Имя	Дата рождения	№ группы	Математика	История	Информатика	Ср. балл
Жукова	Екатерина	16.02.1986					3, 0
Сухов	Андрей	25.10.1987					3, 0
Самойлов	Дмитрий	20.11.1987					5, 0
Данилов	Александр	12.12.1987					5, 0
Валеев	Даниил	19.02.1988					4, 5
Андреева	Юлия	12.04.1988					3, 3
Иванова	Ирина	27.04.1988					4, 7
Кузнецова	Мария	26.12.1988					4, 7

2. Отсортировать данные таблицы по № группы, затем по полю Фамилия, в последнюю очередь по полю Имя.

3. Создать поле Возраст (после поля Дата рождения). Рассчитать возраст студентов по формуле: =(СЕГОДНЯ()-С2)/365. Полученный результат представить в формате Числовой без знаков после запятой. Результат представлен на рис. 10.

Рис. 10. Таблица сведений о студентах и оценках

ЛАБОРАТОРНАЯ РАБОТА № 2

Тема: Использование функций в Excel. Построение графиков

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

Автозаполнение

Для ввода последовательности данных, отличающихся друг от друга на определённый шаг, используется механизм автозаполнения.

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

Автозаполнение действует для всех типов данных. При этом числа и даты – увеличиваются (или уменьшаются), а текст – повторяется.

Так, например, можно быстро создать список товаров с номером (рис. 11).

Рис. 11. Окно Excel с автозаполнением ячеек

Функции

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

2. Функции даты и времени. При вводе даты или времени пользователь вводит последовательность символов, которая не является числом, но с этими символами можно производить вычисления: сравнивать, складывать, вычитать.

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

ABS(X)	–	возвращает модуль числа;
COS(X), SIN(X), TAN(X)	–	возвращает косинус, синус и тангенс угла;
ACOS(X), ASIN(X), ATAN(X)	–	возвращает арккосинус, арксинус и арктангенс числа;
EXP(X)	–	возвращает число e возведённое в степень X;
КОРЕНЬ(X)	–	возвращает значение квадратного корня;
ПИ()	–	возвращает число π с точностью до 15 знака;
СУММ(A; B; C)	–	возвращает сумму всех чисел, входящих в список аргументов.

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

ДИСП(число1; число2; ...)	–	оценивает дисперсию по выборке;
МИН(число1; число2; ...)	–	возвращает наименьшее значение в списке аргументов;
МАКС(число1; число2; ...)	–	возвращает наибольшее значение в списке аргументов;
СРЗНАЧ(число1; число2; ...)	–	возвращает среднее значение в списке аргументов;

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

6. Логические функции. Всякий раз, когда необходимо реализовать те или иные действия, в зависимости от выполнения каких-либо условий, следует использовать логические функции. Имеются следующие логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ. Результатом работы логических функций И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ является логическое значение ИСТИНА или ЛОЖЬ, а результатом работы логической функции ЕСЛИ может быть число, текст или ссылка на выполнение каких-либо действий. Функция НЕ(Аргумент) изменяет логическое значение своего аргумента на противоположное. Функция ЕСЛИ(арг_лог; арг1; арг2) возвращает значение арг1, если арг_лог при вычислении приобретает значение ИСТИНА, и арг2, если арг_лог принимает значение ЛОЖЬ.

Например, пусть в ячейку B1 введена формула

=ЕСЛИ(А1< 0; «отрицательно»; «положительно»)

то есть, если в ячейке А1 будет отрицательное число, то в ячейке В1 появится слово «отрицательно» и наоборот.

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

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом «; ». Между именем функции и скобками не допускается вставлять пробел. Например, функция суммы двух аргументов запишется так:

=СУММА(A1; B2)

Excel содержит более 400 встроенных функций. Вводить в формулу названия функций и значений входных параметров с клавиатуры не всегда удобно, поэтому целесообразно использовать специальное средство для работы с функциями, которое называется Мастер функций, вызываемый командой Вставка → Функция (рис. 12).

Рис. 12. Окно Мастера функций

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

Например, для нахождения максимального значения ряда чисел, записанных в ячейках А1: А10, используется функция МАКС. Окно ввода аргументов функции МАКС представлено на рис. 13.

Рис. 13. Окно ввода аргументов функции МАКС

Пример №1: Найти сумму ряда чисел, записанных в ячейках А1: А10 (рис. 14). Для ввода чисел от 1 до 10 использовать автозаполнение. Результат представить в ячейке А11.

Рис. 14. Окно расчёта суммы аргументов

с помощью Мастера функций

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

Функция ЕСЛИ используется при проверке условий для значений и формул. Синтаксис:

ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь),

где логическое_выражение: условие, которое требуется проверить;

значение_если_истина: значение, возвращаемое, если условие истинно;

значение_если_ложь: значение, возвращаемое, если условие ложно.

Последовательность действий:

Функция ЕСЛИ вызывается следующим образом:

1. В нужной ячейке после знака «=» следует нажать на кнопку fx – вставка функции или в меню Вставка выбрать пункт Функция.

2. Появится окно ввода аргументов функции, куда следует ввести условие и значения, если условие верно и если оно не верно.

Например, пусть в ячейку B2 выдаётся результат в зависимости от условия, которое следует проверить (рис. 15). Если число в ячейке А2 меньше либо равно числу 100, формула отображает строку «Внутри бюджета». В противном случае – строку «Вне бюджета». Окно ввода аргументов показано на рис. 15.

Рис. 15. Окно ввода аргументов функции ЕСЛИ

Результат представлен на рис. 16.

Рис. 16. Окно Excel с результатом вычисления

Пример №2: На промежутке вычислить значения функции: при x≥ 0, и в остальных случаях.

Последовательность действий:

В столбец А с помощью автозаполнения вводится диапазон изменения аргумента х от –5 до 5. В столбце В должны содержаться значения функции y, соответствующие значениям x.

Расчёт значений функции производится также с помощью функции ЕСЛИ. Окно ввода аргументов функции ЕСЛИ представлено на рис. 17.

Рис. 17. Окно ввода аргументов функции ЕСЛИ

Результат вычисления представлен на рис. 18.

Рис. 18. Окно Excel с результатом вычисления

Построим график полученной функции. Для этого необходимо:

1. Выделить оба столбца данных (без названий x и y).

2. В меню Вставка выбрать пункт Диаграмма или на панели инструментов нажать на кнопку – Мастер диаграмм.

3. На первом шаге следует выбрать тип диаграммы – Точечный, вид – сглаженный с маркерами (рис. 19). Нажать Далее.

Рис. 19. Окно Мастера диаграмм шаг 1

4. На втором шаге во вкладке Диапазон данных следует сравнить указанный диапазон с выделенным диапазоном на рабочем листе. Вкладка Ряд позволяет добавить ряд новых значений, указать имя ряда, изменить значения переменных x и y. Здесь в строке Имя ничего не указываем. Нажать Далее.

5. На третьем шаге определяются параметры диаграммы. В строку Название диаграммы вводится общий вид функции y. Показатель степени следует пока ввести без указания регистра (y=x3-2x2+x-1, x≥ 0; y=sin(x-1), x< 0). Необходимо также указать названия осей: в строке Ось x ввести X, в строке Ось y – Y. На вкладке Легенда можно разместить легенду под графиком, над ним, справа, слева (по умолчанию она находится справа). Нажать Далее.

6. На последнем шаге выбирается размещение диаграммы: на отдельном или на текущем листе (по умолчанию диаграмма размещается на текущем листе). Нажать кнопку Готово.

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

Рис. 20. Окно Формат названия диаграммы

Результат представлен на рис. 21.

Рис. 21. Окно диаграммы

Для объединения нескольких условий в единое, могут применяться функции И, ИЛИ. Синтаксис функций И и ИЛИ:

И(логическое_значение1; логическое_значение2; ...)

ИЛИ(логическое_значение1; логическое_значение2; ...),

где логическое_значение1; логическое_значение2; .. – проверяемые условия.

Функции И или ИЛИ могут быть совместно использованы с функцией ЕСЛИ. Синтаксис:

ЕСЛИ(И(логическое_значение1; логическое_значение2); значение_

если_истина; значение_если_ложь)

Эту запись следует понимать так: Если логическое значение 1 Илогическое значение 2 верно, то выполняется выражение «значение_если_истина», иначе выполняется «значение_если_ложь».

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

ЕСЛИ(И(бак заправлен; аккумулятор заряжен); заведётся; не заведётся).

Пример №3: На промежутке вычислить значения функции при и , и в остальных случаях.

Последовательность действий:

В столбец А с помощью автозаполнения вводится диапазон изменения аргумента х от –5 до 5. Столбец B содержит рассчитанные значения функции y. Окно ввода аргументов функции ЕСЛИ представлено на рис. 22.

Рис. 22. Окно ввода аргументов функции ЕСЛИ

Результат вычисления представлен на рис. 23.

Рис. 23. Окно результата вычисления

График данной функции представлен на рис. 24.

Рис. 24. Окно диаграммы

Задания для самостоятельной работы

1. Определить наименьший возраст студентов с помощью функции МИН. Результат поместить в ячейку D10. Результат представлен на рис. 25. В данном задании используется таблица, созданная в лабораторной работе №1 (рис. 10).

Рис. 25. Таблица сведений о студентах

с рассчитанным минимальным возрастом

2. Рассчитать средний балл по каждому предмету (рис. 26). Результаты расположить в ячейках F10, G10, H10. Формат ячеек числовой с двумя знаками после запятой.

Рис. 26. Таблица сведений о студентах

с рассчитанным средним баллом по каждому предмету

3. На промежутке вычислить значения функции при или , и в остальных случаях. Построить график функции.

ЛАБОРАТОРНАЯ РАБОТА № 3

Тема: Работа со ссылками

Цель работы: Научиться производить расчеты с помощью абсолютных и относительных ссылок.

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

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

В Excel существует 2 вида ссылок:

· Ссылка типа A1 называется относительной ссылкой, которая автоматически обновляется в случае копирования или переноса формулы из одной ячейки в другую. Например, если в ячейке A3 была записана формула =A1+A2, то при копировании содержимого A3 в ячейку C3 новая формула примет следующий вид: =C1+C2.

· Абсолютные ссылки. В них кроме названия столбца и номера строки используется специальный символ $, который фиксирует данную часть ссылки и оставляет её неизменной при копировании формулы в другую ячейку. Обычно в таких ячейках содержатся значения констант. Например, если необходимо зафиксировать в формуле значение ячейки A1, которое не должно изменяться в случае копирования или переноса формулы, то абсолютная ссылка на эту ячейку будет выглядеть $A$1. Аналогично, если необходимо зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка, то ссылка примет вид A$1 или $A1 соответственно.

Пример №1: Дано значение Δ x = 0, 01 и диапазон изменения x от –5 до 5 (рис. 27). Рассчитать значения x + Δ x, используя абсолютную ссылку.

Поскольку значение Δ x остаётся постоянным на всём промежутке изменения x, то для расчёта суммы x + Δ x целесообразно использовать абсолютную ссылку на ячейку B1, в которой содержится значение Δ x.

Рис. 27. Окно Excel с вычислением значений x + Δ x

Пример №2: Составить таблицу Пифагора для значений от 1 до 15.

Последовательность действий:

С помощью автозаполнения поместим значения от 1 до 15 в столбце А и в первой строке (рис. 28). При расчёте таблицы каждое значение строки необходимо перемножить с каждым значением столбца и наоборот. Для этого используется абсолютные ссылки, которые фиксируют нужный столбец или строку. В ячейке B2 следует записать формулу: =$A2*B$1. Происходит умножение всех строк столбца А на все столбцы первой строки и наоборот. На пересечении этих строк и столбцов содержится результат перемножения. Рассчитанное значение в ячейке B2 следует растянуть вниз, а затем, не убирая выделения, направо.

Рис. 28. Окно Excel с таблицей Пифагора

Задание для самостоятельной работы

1. Дано значение Δ x, записанное в ячейку B1 (рис. 29). Диапазон изменения x от –5 до 5. С помощью абсолютных ссылок рассчитать значения x + Δ x. Вычислить значения функции вида , также вычислить значения функции y, зависящей от аргумента x + Δ x. Построить график функции y(x).

Рис. 29. Окно Excel с расчётом значений функции y

и график функции y(x)
ЛАБОРАТОРНАЯ РАБОТА № 4

Тема: Анализ данных в Excel. Сортировка и фильтрация данных

Цель работы: Научиться работать с данными в Excel, организованными в виде таблицы базы данных, применять фильтрацию данных и сортировку.

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

Существуют ограничения, накладываемые на структуру базы данных:

· первый ряд базы данных должен содержать неповторяющиеся имена полей;

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

· информация по полям (столбцам) должна быть однородной, т.е. столбец должен содержать данные только одного формата (число, текст, дата, цена и др.).

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

Если данные представлены в виде таблицы, Excel позволяет упростить процесс поиска данных путем сортировки и фильтрации данных.

Процесс сортировки был подробно рассмотрен в лабораторной работе №1.

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

Фильтрация записей в базе данных

с помощью автофильтра

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

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

Пример №1: Дана база данных студентов и их оценок по предметам. С помощью автофильтра выбрать студентов определённой группы.

Последовательность действий:

В данном примере используется база данных, созданная в лабораторной работе №1 (рис. 10). Следует скопировать её на Лист 1 текущей книги.

Чтобы применить автофильтр к записям базы данных необходимо выделить какую-либо ячейку с данными и выбрать в меню Данные команду Фильтр → Автофильтр. В ячейках с названиями столбцов появляются кнопки раскрывающихся списков (рис. 30).

Рис. 30. Окно Excel с автофильтром данных

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

Отберём всех студентов группы с номером 5433. Для этого:

1. Открыть раскрывающийся список в столбе с именем № группы.

2. Выбрать из списка нужный номер.

Записи, не удовлетворяющие данному критерию, отображаться не будут.

Команда Данные → Фильтр → Отобразить все позволяет отобразить все записи. Чтобы отменить использование автофильтра, нужно повторно выбрать команду Данные → Фильтр → Автофильтр.

Пример №2: С помощью автофильтра выбрать студентов, родившихся позднее 1 января 1988 года.

Последовательность действий:

1. Скопировать базу данных (рис. 30) на Лист 2.

2. Открыть раскрывающийся список в столбце с именем Дата рождения.

3. Выбрать из списка пункт Условие. В появившемся окне Пользовательский автофильтр выбрать критерий отбора, в нашем случае – «больше либо равно» и вписать нужную дату (рис. 31). После нажатия кнопки ОК в таблице останутся только записи, удовлетворяющие критерию.

Рис. 31. Окно пользовательского автофильтра

Пример №3: Отобрать с помощью автофильтра студентов, обучающихся в группе № 5433 с фамилией, начинающейся на букву С.

Последовательность действий:

1. Скопировать базу данных (рис. 30) на Лист 3.

2. Открыть раскрывающийся список в столбце Фамилия.

3. Выбрать из списка пункт Условие. В появившемся окне Пользовательский автофильтр выбрать критерий отбора «начинается с», в поле напротив ввести нужную букву (проверить, чтобы раскладка была русскоязычная). Нажать ОК.

4. Открыть раскрывающийся список в столбце № группы.

5. Выбрать нужный номер.

Фильтрация записей в базе данных

с помощью расширенного фильтра

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

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

Пример №4: Выбрать всех студентов из группы № 5433, у которых средний балл больше либо равен 4, 5.

Последовательность действий:

1. Скопировать базу данных (рис. 30) на Лист 4.

2. Скопировать названия столбцов № группы и средний балл в область ниже исходной таблицы. Под названиями столбцов ввести нужные критерии отбора (рис. 32)

Рис. 32. Окно Excel с расширенным фильтром

2. В меню Данные выбрать пункт Фильтр → Расширенный фильтр. Появится диалоговое окно(рис. 33), в котором указываются диапазоны данных.

Рис. 33. Окно расширенного фильтра

В поле ввода Исходный диапазон указывается интервал, содержащий исходную базу данных. В нашем случае выделяется диапазон ячеек с А1 по I9.

В поле ввода Диапазон условий выделяется интервал ячеек на рабочем листе, который содержит требуемые критерии (С12: D13).

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

Флажок Только уникальные записи предназначен для отображения только неповторяющихся строк.

Результирующая таблица, удовлетворяющая критериям фильтрации, представлена на рис. 34.

Рис. 34. Окно Excel с результатами фильтрации

Задания для самостоятельной работы

1. Создать свою базу данных, количество записей в которой должно быть не менее 15, а количество столбцов – не менее 6. Например, база данных Список клиентов (рис. 35).

2. К базе данных применить три автофильтра (на отдельных листах). Количество критериев должно быть не менее двух.

3. Применить три расширенных фильтра к записям базы данных, каждый из которых должен содержать не менее двух критериев. Все расширенные фильтры разместить на одном листе под исходной таблицей.

Рис. 35. Окно Excel с базой данных Список клиентов

ЛАБОРАТОРНАЯ РАБОТА № 5

⇐ Предыдущая 123 4 Следующая ⇒