Опорный конспект лекции (стр. 2 )

Тема № 2. Использование функции ВПР(), ГПР(), ПОИСКПОЗ(), ИНДЕКС(), ЕСЛИ(). Проведение анализа с помощью таблиц подстановок. (2ч)

Рекомендуемая литература: [1], стр. ;

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

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

□ использование формул для поиска информации в таблицах;

□ использование функции ветвления если () (if ()) для принятия решений;

□ работа с таблицей подстановок.

30.1. Функции поиска информации в таблицах

Рассмотрим задачу поиска нескольких значений из множества данных в таблице.

В Excel существует возможность просмотра данных с помощью функций просмотра впр () и гпр () (в англоязычной версии Excel им соответствуют функции vlookup() и hlookup(). Кроме того, можно воспользоваться функциями индекс () и поискпоз() (index () и match () соответственно).

Функции просмотра впр () и гпр () очень просты в использовании, но обладают меньшей гибкостью по сравнению с функциями индекс () И П0ИСКП03().

Все четыре описываемые функции предполагают, что искомое значение бу­дет найдено в таблице. В противном случае результатом работы каждой функции будет ошибка #н/д (#n/a).

Использование функций ВПР() и ГПР()

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

функции впр () и гпр () имеют следующий синтаксис:

ВПР(искомое значение; диапазон таблицы; номер столбца;интервальный_просмотр)

ГПР(искомое_значение; диапазон_таблицы; номер_строки; интервальный_просмотр) ,

где:

искомое_значение — это значение, которое требуется найти в первом столбце/строке таблицы;

диапазон_таблицы — это ячейки, составляющие информационную таблицу;

номер_столбца и, соответственно, номер_строки — номер столбца (строки), в котором требуется найти искомое значение.

Четвертый аргумент, интервальный_просмотр, является необязательным. Это логическое значение, которое определяет, нужно ли, чтобы функция впр () (или гпр ()) искала точное или приближенное соответствие. Если этот аргу­мент имеет истинное значение или опущен, то функция возвращает прибли­зительно соответствующее значение, то есть, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем иско­мое значение.

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

Рассмотрим пример использования функции впр (): в рабочей книге Артур Хейли. х1s представлен список романов Артура Хейли с некоторыми дополнительными сведениями — оригинальным (английским) названием, годом создания и количеством страниц. Внизу расположена небольшая таб­лица поиск. Она позволяет по заданному русскому названию романа полу­чить остальные сведения о произведении (рис. 30.1).

Для поиска в таблице используется функция впр (). На рис. 30.2 изображена та же самая рабочая книга, в которой включен режим отображения формул.

Использование функций ПОИСКПОЗ() и ИНДЕКС()

Альтернатива функциям впр () и гпр () — функции поискпоз () (match ()) и индекс () (index ()). Они имеют следующий синтаксис:

ИНДЕКС(массив; номер строки; номер столбца)

ПОИСКПОЗ(искомое_значение; массив_просмотра; тип_сопоставления)

Для функции индекс () аргумент массив — это интервал ячеек или массив констант. Эта функция возвращает значение, хранящееся в ячейке этого массива, расположенной в строке номер_строки и в столбце номер_столбца.

один из двух последних аргументов — номер_строки ИЛИ номер_столбца —

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

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

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

=ИНДЕКС(В3:В11; ПОИСКПОЗ(В17;АЗ:А11;0)1) .

В17 — это ячейка, содержащая искомое значение (т. е. текстовую строку «перегрузка»); аз:а11 — таблица, в которой это значение должно быть най­дено; последний аргумент, равный нулю, указывает на то, что мы ищем точное, а не приближенное значение.

Тогда, когда нам известен номер строки, содержащей оригинальное название, мы можем получить его, вызывая функцию ИНДЕКС(В3:В11; ПОИСКПОЗ(В17;АЗ:А11;0)1)— т. е. извлекаем значение диапазона В3:В11 с порядковым номером строки, равным ПОИСКПОЗ(В17;АЗ:А11;0)1, и порядковым номером столбца, равным 1.

Функция если (), результат которой зависит от истинности или

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

функция если () имеет следующий формат:

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

Если лог_выражение (условие) истинно, то эта функция вернет значение

значение_если_истина, В ПрОТИВНОМ случае она вернет значение_если_ложь.

функция может возвращать следующие значения: □ Текст (например, «оплаченный»):

=ЕСЛИ(В4="0плачен"; "оплаченный"; "не оплаченный").

Если в ячейке в4 содержится текст "Оплачен", то результатом при истине будет "оплаченный", в противном случае результатом будет текст

"не оплаченный". О Формула (например, В2*6):

=--ЕСЛИ(В4>10;ВЗ*2;ВЗ+2) .

Если в ячейке в4 содержится число больше ю, то результатом будет формула вз*2, если в ячейке В4 содержится число меньше ю, то формула

ВЗ + 2.

Просто ссылка на содержимое ячейки (например, cs):

=ЕСЛИ(В4>10;С5;0) .

Результатом будет значение ячейки съ, если значение в ячейке В4 больше ю, и о в противном случае.

Рассмотрим конкретный пример: на рис. 30.4 изображен набросок неболь­шой анкеты, в которой человеку предлагается ввести свои данные, а потом ответить на несколько вопросов.

Значение в ячейке В12 зависит от того, к какому полу принадлежит анкети­руемый человек. Ячейка содержит слово "господин", если в графе "пол" ука­зано "мужской", либо слово "госпожа" в противном случае. Добиться этого эффекта позволяет функция если (), вызванная следующим образом:

=ЕСЛИ(В2="м";"господин";"госпожа")

Такое решение не лишено недостатков, которые несложно исправить.

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

Доработаем нашу анкету таким образом, чтобы приветствие появлялось на экране лишь в тех случаях, когда все анкетные данные введены корректно

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

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

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

При помощи функций поискпозо и индексо можно получить те же ре­зультаты, что и при использовании впр () и гпр (). На рис. 30.3 изображена Рабочая книга Артур Хейли. хХэ из предыдущего примера с использованием Функций ПОИСКПОЗ() и индекс().

В функции если () результат которой зависит от истинности или ложности задаваемого условия.

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

функция если () имеет следующий формат:

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

Если лог_выражение (условие) истинно, то эта функция вернет значение

значение_если_истина, В ПрОТИВНОМ случае она вернет значение_если_ложь.

функция может возвращать следующие значения: □ Текст (например, «оплаченный»):

=ЕСЛИ(В4="0плачен"; "оплаченный"; "не оплаченный").

Если в ячейке в4 содержится текст "Оплачен", то результатом при истине будет "оплаченный", в противном случае результатом будет текст

"не оплаченный". О Формула (например, В2*б):

=--ЕСЛИ(В4>10;ВЗ*2;ВЗ+2) .

Если в ячейке в4 содержится число больше ю, то результатом будет формула вз*2, если в ячейке В4 содержится число меньше ю, то формула

ВЗ + 2.

Просто ссылка на содержимое ячейки (например, cs):

=ЕСЛИ(В4>10;С5;0) .

Результатом будет значение ячейки С5, если значение в ячейке В4 больше ю, и о в противном случае.

Рассмотрим конкретный пример: на рис. 30.4 изображен набросок неболь­шой анкеты, в которой человеку предлагается ввести свои данные, а потом ответить на несколько вопросов.

Значение в ячейке В12 зависит от того, к какому полу принадлежит анкети­руемый человек. Ячейка содержит слово "господин", если в графе "пол" ука­зано "мужской", либо слово "госпожа" в противном случае. Добиться этого эффекта позволяет функция если (), вызванная следующим образом:

=ЕСЛИ(В2="м";"господин";"госпожа")

Такое решение не лишено недостатков, которые несложно исправить.

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

Доработаем нашу анкету таким образом, чтобы приветствие появлялось на экране лишь в тех случаях, когда все анкетные данные введены корректно,

т. е. графа «Фамилия» непуста, а графа «Пол» содержит букву «м» или «ж» Для этого надо ввести в ячейки A12-D12 формулы:

=ЕСЛИ( И(ВК>"";ИЛИ(В2 = "м";В2="ж") ) ; "Здравствуйте, "; "" )

=ЕСЛИ( ИЛИ(В1 = "";И(В2о"м";В2<>"ж") ) ; "" ; ЕСЛИ ( В2="м"; "господин"; "госпожа") )

=ЕСЛИ(И(ВК>"";ИЛИ(В2 = "м";В2="ж") ) ;В1; "") =ЕСЛИ(И(ВК>"";ИЛИ(В2="м";В2 = "ж") );"!"; "")

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

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

Функция или () возвращает значение истина в том случае, если хотя бы один из ее аргументов имеет значение истина. Иначе значение функции бу­дет равно ложь.

Хаким образом, строка «Здравствуйте,» в ячейке А12, фамилия анкетируе­мого человека в ячейке С12 и восклицательный знак в ячейке D12 будут ото­бражаться лишь в том случае, если содержимое ячейки bi (т. е. графы «фамилия») не будет равно пустой строке, а содержимое ячейки В2 (т. е. графы «Пол») будет равно «м» или «ж».

В ячейке А14 будет отображена пустая строка, если графа «Фамилия» пуста, либо графа «Пол» не содержит букв «м» или «ж», либо и то, и другое одно­временно. В противном случае применяется вызов функции если () из пре­дыдущего примера, Т. е. ЕСЛИ(В2="м";"господин";"госпожа") .

30.2. Проведение анализа с помощью таблиц подстановок

Как повлияет изменение данных на конечный результат? Ответ на этот во­прос можно получить, воспользовавшись таблицами подстановок. С их по­мощью нетрудно проследить, как будет развиваться система при различных условиях. Таблицей подстановки данных называется диапазон ячеек, показы­вающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Таблицы подстановки обеспечивают быстрый доступ к выполнению одной операции разными способами, а также возможность просмотра и сравнения полученных результатов. Использование таблицы подстановки вместе со статистическими функциями позволяет быстро и эффективно анализировать финансовую и научно-исследовательскую ин­формацию.

В Excel существует понятие анализа чувствительности — это анализ, позво­ляющий определить, насколько надо изменить исходные данные, чтобы ко­нечный результат претерпел значительные изменения. С помощью команды меню Данные, Таблица подстановки (Data, Table) можно проводить анализ чувствительности со сколь угодно широким диапазоном исходных данных.

Для более мобильного и эффективного использования таблиц подстановок в Excel существует возможность применения на одном рабочем листе нескольких таблиц подстановок.

Если необходимо изменять одно исходное значение, просматривая результа­ты одной или нескольких формул, или изменять два исходных значения, просматривая результаты только одной формулы, — используйте команду Меню Данные, Таблица подстановки (Data, Table).

Таблица подстановки с одной изменяющейся переменной

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

Чтобы создать таблицу подстановки с одной переменной, следует сформи­ровать таблицу так, чтобы введенные значения были расположены либо в столбце, либо в строке. Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода. Ячейка ввода — это ячейка, в которую подставляются значения из таблицы данных. Любая ячейка на листе может быть ячейкой ввода. Хотя ячейка ввода не обязатель­но должна быть частью таблицы данных, формулы в таблице данных всегда ссылаются на ячейку ввода.

Для начала следует создать рабочий лист с исходными данными задачи (рис. 30.5).

Зарплата вычисляется по формуле: Зарплата = (Тариф * Кол-во_часов) / 18,

где 18 часов — полная ставка учителя. Эта формула введена в ячейку сб. Те­перь требуется рассчитать зарплату для всех возможных вариантов занято­сти — от половины ставки (9 часов) до 24 часов. Для этого нужно выпол­нить следующие действия: