7.2 Проведение анализа документа

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

ВПР() – «Вертикальный ПРосмотр» ГПР() – «Горизонтальный Просмотр»

    Для начала создадим документ следующего вида:

    Для этого:

1. Создайте документ Microsoft Excel.

2. Для ячеек A1:F1 выполните команду Объединить и центрировать. Введите заголовок.

3. Введите заголовки столбцов. Выделите ячейки A2:F2 и выполните команду Формат/Ячейки. Во вкладке Выравнивание установите флажок на Переносить по словам.

4. Введите данные в таблицу.

5. Для ячеек A20:B20 выполните команду Объединить и центрировать и введите заголовок таблицы.

6. В ячейки A22:A26 введите заголовки таблицы.

7. В ячейку B22 введите название любой страны.

    Переходим к использованию функций ВПР() и ГПР(). Эти функции имеют следующий синтаксис:

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

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

     искомое_значение – это значение, которое требуется найти в первом столбце/строке таблицы; диапазон_таблицы – это ячейки, составляющие информационную таблицу; номер_столбца/номер_строки – номер столбца/строки, в котором требуется найти искомое значение; интервальный_просмотр – (является необязательным) Это логическое значение, которое определяет, нужно ли, чтобы функция ВПР()/ВКР() искала точное значение или приближенное соответствие. Если этот аргумент опущен, или имеет истинное значение, то функция возвращает приблизительное соответствующее значение (если точное соответствие не найдено).

    Вернемся к нашему примеру и рассмотрим использование функции ВПР():

1. В ячейку B22 введите формулу =ВПР(B21;A3:F17;2;ЛОЖЬ). У вас отобразится информация о выбранной вами стране, а именно, её площадь.

2. Аналогичным образом «узнайте» другую информацию об этой стране.

3. В ячейку B21 введите название другой страны. В ячейках B22:B26 немедленно отобразится информация об этой стране.

7.3 Таблицы подстановок

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

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

    Создадим документ следующего вида:

    Для этого:

1. Создайте документ Microsoft Excel.

2. Выделите ячейки A1:C1 и выполните команду Объединить и центрировать. Введите заголовок таблицы.

3. В ячейку B3 введите заголовок и выполните команду Вставка/Примечание.

4. В ячейку B4 и В6 введите нужные заголовки.

5. В ячейку C3 введите значение. И выполните команду Формат/Ячейки во вкладке Число выберите Денежный формат - $Английский(Канада).

6. Введите курс доллара. (Формат ячейки F1 - Денежный р.)

7. В ячейку C6 введите формулу =C3*C4*F1.

8. Выделите ячейки C6:C15 и установите формат Денежный (р).

    Переходим к созданию таблицы подстановки. Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода. Ячейка ввода – это ячейка, в которую подставляются значения из таблицы данных. В нашем случае, ячейкой ввода является ячейка C4, а диапазон ячеек B6:C15 – таблицей данных - она содержит формулы и значения подстановки. Итак,

1. Выделите диапазон ячеек B6:C15.

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

3. В поле Подставлять значение по строкам укажите ячейку $C$4. Это ячейка ввода, в которую последовательно вводились бы значения, если бы не было средств таблицы постановки.

4. Нажмите кнопку OK.

    После этого таблица заполнится требуемыми значениями.