Главная страница

Лаб_раб_EXCEL. Лабораторная работа использование статистических, математических и текстовых функций


Скачать 1.26 Mb.
НазваниеЛабораторная работа использование статистических, математических и текстовых функций
Дата13.05.2023
Размер1.26 Mb.
Формат файлаdoc
Имя файлаЛаб_раб_EXCEL.doc
ТипЛабораторная работа
#1126371
страница5 из 7
1   2   3   4   5   6   7

ЛАБОРАТОРНАЯ РАБОТА 7

Расширенный фильтр. Промежуточные итоги.



Откройте таблицу, созданную в работе 6 (Список.xls).
Создание интервала критериев

  1. Расположите интервал критериев, начиная с ячейки А19. Для этого: выделите диапазон с именами столбцов А1:F1; скопируйте выделенный диапазон в ячейки А19:F19.

  2. Отберите информацию о книгах, которые закуплены или в 1-ом квартале или имеют тематику Экономика.

Порядок выполнения:

  • в ячейку А20 занесите значение 1 кв, в ячейку В21 занесите значение Экономика (т.е. при расположении условий отбора на разных строчках формируется их связь по правилу ИЛИ);

  • сделайте текущей любую ячейку в области База_данных;

  • выполните команду Данные Фильтр Расширенный фильтр;

  • в диалоговом окне Расширенный фильтр поле Исходный диапазон будет уже заполнено;

  • в поле Диапазон условий либо наберите вручную (ввод осуществляется с использованием абсолютных адресов ячеек ), либо выделите с помощью мыши диапазон А19:F21 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

В результате будут отобраны записи, относящиеся к первому кварталу или у которых тематика Экономика.
3. Отмените действие фильтра. Для этого выполните команду Данные Фильтр — Отобразить все.

4. Отберите информацию о книгах, которые закуплены в 1-м квартале и имеют тематику Экономика.

Порядок выполнения:

  • переместите значение Экономика из ячейки В21 в ячейку В20 (т.е. при расположении условий отбора на одной строчке формируется их связь по правилу И);

  • сделайте текущей любую ячейку в области База_данных;

  • выполните команду Данные Фильтр Расширенный фильтр;

  • выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$20 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

5. Отберите информацию о книгах, которые закуплены в 1,2 и 3-м кварталах. Порядок выполнения:

  • очистите ячейку В20 (т.к. по столбцу Тематика условий отбора нет), в ячейки А21 и А22 занесите значения 2 кв и 3 кв соответственно;

  • сделайте текущей любую ячейку в области База__данных;

  • выполните команду Данные Фильтр Расширенный фильтр;

  • выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$Р$22 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

  1. Отберите информацию о книгах, которые закуплены в 1-м квартале по тематике Экономика, а во 2-м квартале по тематике Компьютеры.

Порядок выполнения:

  • очистите ячейку А22, в ячейки В20 и В21 занесите значения Экономика и Компьютеры соответственно; Первая строка определяет условие отбора записей покупок книг в 1-м квартале по тематике Экономика, а вторая во 2-м квартале по тематике Компьютеры. Условия в строчках объединены правилом И. Между собой строки объединены правилом ИЛИ;

  • сделайте текущей любую ячейку в области База_данных;

  • выполните команду Данные Фильтр Расширенный фильтр;

  • выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$21 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.


Применение текстовых критериев

  1. Отберите информацию о книгах, названия которых начинаются с латинских букв.

Порядок выполнения:

    • очистите диапазон ячеек А20:В21, в ячейку С20 занесите условие <А (русская буква), т.е. отберите все названия, которые начинаются с любой латинской буквы;

    • сделайте текущей любую ячейку в области База_данных,

    • выполните команду Данные Фильтр Расширенный фильтр;

    • выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$20 (диапазон ячеек, в котором заданы условия отбора), нажмите кнопку ОК.

    • Отмените действие фильтра.


Применение вычисляемых критериев

1. Отберите информацию о книгах, цена которых больше средней цены всех книг.

Порядок выполнения:

  • в ячейку С25 введите формулу определения средней цены всех книг:

=СРЗНАЧ(D2:D16)

  • в ячейку В25 введите вычисляемый критерий:

=D2>$С$25,

где D2 — адрес первой ячейки, содержащей цену (адрес этой ячейки должен быть относительным);

  • сделайте текущей любую ячейку в области База_данных;

  • выполните команду Данные — Фильтр Расширенный фильтр;

  • выберите поле Диапазон условий, очистите его и введите в него диапазон $В$24:$В$25 (в диапазон ячеек, который определяет интервал критериев, обязательно включается ячейка над формулой), нажмите кнопку ОК.


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

1. Отберите информацию о книгах, которые закуплены по тематике Компьютеры, и расположите отобранные записи, начиная с ячейки Н19. Порядок выполнения:

  • очистите ячейку С20, в ячейку В20 введите слово Компьютеры;

  • сделайте текущей любую ячейку в области База_данных;

  • выполните команду Данные Фильтр —- Расширенный фильтр;

  • включите опцию скопировать результат в другое место;

  • выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$20 (диапазон ячеек, в котором заданы условия отбора);

  • выберите поле Поместить результат в диапазон и введите в него диапазон $Н$19:$М$19 (количество ячеек в диапазоне равно общему числу столбцов в списке), нажмите кнопку ОК.

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

1. Отберите информацию о книгах, которые закуплены в 3-м квартале, расположите отобранные записи, начиная с ячейки Н19, используя информацию из столбцов Дата, Название, Стоимость.

Порядок выполнения:

  • очистите ячейку В20, в ячейку А20 введите 3 кв;

  • очистите ячейки в диапазоне Н19:М24;

  • в ячейку Н19 скопируйте содержимое ячейки А1 (название столбца Дата), в I19 из ячейки С1, и в J19 — из ячейки F1;

  • сделайте текущей любую ячейку в области База_данных;

  • выполните команду Данные Фильтр Расширенный фильтр;

  • включите опцию скопировать результат в другое место;

  • выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$20;

  • выберите поле Поместить результат в диапазон и введите в него диапазон $Н$19:$J$19, нажмите кнопку ОК.

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

1. Определите по тематике Компьютеры суммарное количество и стоимость книг, закупленных в каждом квартале и всего за год.

Порядок выполнения:

  • очистите ячейку А20, в ячейку В20 введите слово Компьютеры;

  • сделайте текущей любую ячейку в области База_данных;

  • выполните команду Данные Фильтр — Расширенный фильтр;

  • выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$20;

  • нажмите кнопку ОК. После этого отобраны все записи по тематике Компьютеры за весь год;

  • выполните команду Данные Итоги;

  • в появившемся диалоговом окне Промежуточные итоги оставьте без изменения значения полей При каждом изменении и Операция;

  • в поле Добавить итоги по включите элементы Количество и Стоимость. Проверьте, чтобы все остальные элементы в этом поле были выключены (отсутствовал крестик справа), нажмите кнопку ОК.

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


Рисунок 12 – Итоговая таблица

1   2   3   4   5   6   7


написать администратору сайта