Пояснения к выполнению задания

Формулировка темы

Электронные таблицы EXCEL. Функции просмотра и ссылок

Содержание задания (постановка задачи)

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

 

Пояснения к выполнению задания

• теоретические основы предметной области

Использование терминов в приложениях.

Некоторые функции просмотра и ссылок (ВПР, ПРОСМОТР, ИНДЕКС, ПОИСКПОЗ и др.), сортировка данных. Функции проверки свойств и значений.

• методические указания

Первоначально на отдельном рабочем листе создается таблица соответствий англоязычных терминов русским словам. Далее созданный массив сортируется по возрастанию, для этого табличный курсор нужно установить в любой ячейке таблицы, меню Данные — Сортировка. Это необходимо сделать, потому что для работы некоторых функций просмотра и ссылок требуется, чтобы данные были упорядочены по возрастанию (например, ПРОСМОТР).

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

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

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

Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. (= ВПР (искомое_значение; таблица; номер_столбца; интервальный_просмотр)).

• искомое_значение — значение, которое необходимо найти в первом столбце таблицы (значение, ссылка или строка);
• таблица — диапазон, содержащий таблицу, в которой осуществляется поиск данных;
• номер_столбца — номер столбца в таблице, из которого необходимо возвратить соответствующее значение;
• интервальный_просмотр — необязательный аргумент. В том случае, если данный аргумент содержит значение ИСТИНА или опущен, функция возвращает значение, найденное путем приближенного сопоставления искомого значения (если не определено точное значение, функция возвращает ближайшее значение, меньшее, чем заданный аргумент искомое_значение). В случае, если аргумент содержит логическое значение ЛОЖЬ, функция будет искать точное соответствие. Если точное соответствие не найдено, функция возвратит ошибку #Н/Д.

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

Функция ПРОСМОТР имеет следующий синтаксис:

= ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата).

• искомое_значение — значение, которое функция ищет в векторе просмотра;
• вектор_просмотра — диапазон, состоящий только из одной строки или одного столбца и содержащий значения, которые необходимо найти. Значения должны располагаться в порядке возрастания;
• вектор_результата — диапазон, состоящий только из одной строки или одного столбца и содержащий значения, которые будут возвращены. Размер этого диапазона должен быть равен размеру диапазона вектор_просмотра.

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

Достаточно часто при поиске данных функции ПОИСКПОЗ и ИНДЕКС используются вместе. Функция ПОИСКПОЗ возвращает относительное положение элемента массива, который соответствует указанному значению в указанном порядке (= ПОИСКПОЗ (искомое_значение; просматриваемый_массив; тип_сопоставления)).

• искомое_значение — значение, которое требуется найти в массиве просматриваемый_массив.
• просматриваемый_массив — диапазон, который содержит искомое_значение.
• тип_сопоставления — целое число в интервале от -1, 0,1, которое устанавливает параметры соответствия.

В том случае, если аргумент тип_сопоставления равен 1 (по умолчанию), функция ПОИСКПОЗ ищет самое большое значение, которое меньше либо равно аргументу искомое_значение (значения диапазона просматриваемый_массив должны располагаться в порядке возрастания). Если аргумент тип_сопоставления равен 0, функция ищет первое значение, в точности соответствующее аргументу искомое_значение. Если же аргумент тип_сопоставления равен -1, функция ищет наименьшее значение, которое больше или равно аргументу искомое_значение (в этом случае значения диапазона просматриваемый_массив должны располагаться в порядке убывания).

Функция ИНДЕКС возвращает значение элемента таблицы или массива, заданного номером строки и номером столбца. (= ИНДЕКС (массив; номер_строки; номер_столбца)).

• массив — это диапазон ячеек или массив констант;
• номер_строки — это номер строки в массиве, из которой нужно возвращать значение. Номер строки можно получить, используя функцию ПОИСКПОЗ.

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

Выше уже говорилось, что в случае, если искомое слово не найдено, возвращается ошибка #Н/Д. Чтобы предотвратить ее появление на экране, используйте функции ЕСЛИ и ЕНД (возвращает ИСТИНА, если значение ссылается на значение ошибки #Н/Д).

Ожидаемый результат

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

 

Пример выполнения задания