II. Простейшие запросы на выборку данных из списка (автофильтр)

 

8. Для выполнения простейших запросов к данным списка воспользуйтесь инструментом «Фильтр» на панели «Сортировка и фильтр» ленты «Данные». После выполнения команды во всех ячейках заголовка списка появляются кнопки со стрелками. Нажатие на какую-либо из стрелок открывает список критериев поиска данных. После выбора критерия в списке данных останутся только те строки, которые удовлетворяют критерию поиска.

Внимание! Строки списка, не удовлетворяющие введенному Вами критерию поиска, не удаляются из рабочего листа, а становятся лишь временно невидимыми. Для их вывода на экран необходимо воспользоваться инструментом «Очистить» на панели «Сортировка и фильтр».

9. Нажмите на клавишу со стрелкой в ячейке С3, из развернувшегося списка выберите значение R20”.

 

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

10. Примените инструмент «Очистить» на панели «Сортировка и фильтр» ленты «Данные».

11. Нажмите на клавишу со стрелкой в ячейке D3.

12. Выберите команду Числовые фильтры\больше.

13. В открывшемся диалоговом окне «Пользовательский автофильтр» в свободное поле рядом с оператором «больше» введите число 10

14. После выполнения указанного действия в списке должны остаться только те строки, в которых в поле “Вес” значение будет превышать число 10.

15. Примените инструмент «Очистить» на панели «Сортировка и фильтр» ленты «Данные».

 

III. Контрольное задание

В рабочей книге <ФАМИЛИЯ>.XLSX создайте новый рабочий лист, дайте ему название “Контрольная 1”. На созданном листе сделайте текстовый заголовок “Автофильтр”. При помощи технологии, приведенной в пунктах 7-9, получите и скопируйте на лист под заголовком “Автофильтр” следующие списки:

a. Список контейнеров, погруженных в порту Хельсинки (HEL), названия которых начинаются с букв ” ASDF”

b. Список контейнеров, погруженных в порту Хельсинки для выгрузки в портах Роттердам (ROT) и Гамбург (HAMB)

c. Список 20” контейнеров, предназначенных для выгрузки в порту Гамбург (HAMB)

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

 

IV. Более сложные запросы на выборку данных из списка (расширенный фильтр)

Более сложные запросы, состоящие из более чем одного критерия либо запросы предусматривающие, что искомые значения лежат в некотором диапазоне, требуют предварительного определения двух дополнительных областей – области Критериев (куда мы будем записывать критерии поиска) и области “Выборки”, куда MS Excel будет помещать строки списка, которые удовлетворяют заданным критериям выборки.

 

 

1. Выделите ячейки, содержащие заголовок списка, скопируйте выделенную область в буфер обмена (КОНТЕКСТНОЕ МЕНЮ|Копировать)

2. Для формирования области критериев для выборки из списка, установите курсор на ячейку N3 и вставьте содержимое буфера обмена (КОНТЕКСТНОЕ МЕНЮ|Вставить)

3. Для формирования области выборки из списка, в соответствии с выбранными критериями, установите курсор на ячейку N16 и вставьте содержимое буфера обмена (КОНТЕКСТНОЕ МЕНЮ|Вставить)

4. Заполнение области критериев.

a. Чтобы указать, что критерием выбора является конкретное значение (число, дата, текст), просто запишите это число в ячейку ниже названия соответствующего поля (например, значение BRE в ячейку W4 – чтобы указать, что нас интересуют только груз для порта Бремен).

b. Чтобы указать, что критерием является значение большее или меньшее чем некоторое число или дата, воспользуйтесь операторами “<” или “>” (например, чтобы сказать, что нас интересуют контейнеры, расположенные на крышках трюмов, в ячейку U4 введите текст >2)

c. Если в критерий требуется внести значения, связанные условием ИЛИ (например, чтобы указать, что нас интересуют груз, погруженный в Санкт-Петербурге или в Хельсинки), необходимо, в ячейку V4 записать SPB и в ячейку V5 записать HEL.

 

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

 

#	Номер контейнера	Тип	Вес	Трюм	Ряд	Номер в ряду	Ярус	Погружен	К разгрузке	Код
							>2	SPB	BRE
							>2	HEL	BRE

 

Внимание! При анализе критерия используйте следующее правило:

Критерии, записанные в одной строке – объединяются логической операцией «И», критерии, записанные в разных строках области – объединятся логической операцией «ИЛИ».

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

(расположены в ярусе не ниже 2-го И погружены в порту SPB И предназначены для выгрузке в порту BRE) ИЛИ (расположены в ярусе не ниже 2-го И погружены в порту HEL И предназначены для выгрузке в порту BRE)

 

 

5. Для получения списка данных, удовлетворяющих сформированному запросу установите курсор в любую ячейку из диапазона A4:K54, воспользуйтесь инструментом «Дополнительно» на панели «Сортировка и фильтр» ленты «Данные» и в открывшемся диалоговом окне выберите параметр “скопировать в другую область” и укажите диапазоны, в которых располагаются Область списка данных (Исходный диапазон), Область критериев (Диапазон условий) – N3:X5 и Область выборки (Поместить результат в диапазон) – N16:X16.

 

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

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

Результат выполнения запроса к базе данных

№	Номер контейнера	Тип	Вес	Трюм	Ряд	Номер в ряду	Ярус	Погружен	К разгрузке	Код
	FDSF13123114	40”						SPB	BRE
	FDSF13123115	40”						SPB	BRE

V. Контрольное задание

В рабочей книге <ФАМИЛИЯ>.XLS создайте новый рабочий лист, дайте ему название “Контрольная 2”.

На рабочем листе “Контрольная 2” создайте текстовый заголовок “Расширенный фильтр”. При помощи технологии, приведенной в пунктах 11-15, получите и скопируйте на лист ниже заголовка “Расширенный фильтр” следующие списки:

a. Список 40” контейнеров, погруженных в порту Хельсинки (HEL), содержащих опасные грузы (код груза больше 0)

b. Список контейнеров, погруженных в порту Хельсинки для выгрузки в портах Роттердам (ROT) и Гамбург (HAMB)

c. Список 20” контейнеров, предназначенных для выгрузки в порту Гамбург (HMBG) по трюмам судна (выполнив запрос несколько раз используя в качестве дополнительно параметра в критерии запроса номер трюма)

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