Глава 4 (О.Б. Калугина, В.С. Люцарев - Работа с электронными таблицами. Microsoft Office Excel 2003), страница 5

Эта форма допускает большее число аргументов.Организация вычислений в рабочих книгах93Функции поиска в массивах данныхФункция ПРОСМОТР имеетдве синтаксические формы, которыепозволяют организовать поиск либопо вектору значений, либо в массиве,заданном таблицей. Рассмотримкаждую из этих возможностей.Для векторной формы задаются такие аргументы.Одна или несколькообластей – диапазоновсмежных ячеекИскомомое_значение ищется в Просматриваемом_векторе и возвращает значение из Вектора_результатов, совпадающее по номеру с найденным элементом просматриваемого вектора.Особо отметим следующие важные моменты. Просматриваемый_вектор и Вектор_результатов должныбыть либо строкой, либо столбцом и обязательно иметьодинаковую размерность1.

Значения Просматриваемого_вектора должны быть упорядочены по возрастанию2. В противном случае может бытьвозвращено неверное значение. Если в Просматриваемый_векторе находятся текстовые значения, регистр букв при поиске не учитывается.Так, в качестве первого аргумента можно указать ссылки не на одну,а на несколько областей ячеек, при этом последний аргумент Номер_области задает номер той, в которой находится искомая ссылка.В этом примере функцияИНДЕКС определяет ячейкуна пересечении второй строки и третьего столбца во второй из областей, указанных варгументе Ссылка.12Размерность – это количество элементов.Подробно возможности сортировки будут рассматриваться в Главе 6 Списки в Excel.

Здесьже упомянем о кнопке сортировки по возрастаниюв панели инструментов Стандартная, позволяющей быстро упорядочить данные в столбце, внутри которого находится активная ячейка.94Глава 4Организация вычислений в рабочих книгах95 Если точного совпадения с Искомым_значением не найдено, то подходящим считается наибольшее значение из Просматриваемого вектора, которое меньше, чем искомое. Если Искомое_значение меньше, чем наименьшее значениев аргументе Просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.Можно ли получить данные из любых столбцов или строк просматриваемой таблицы, а не только из последних? В этом случае рекомендуется обратиться к возможностям функций ВПР и ГПР, вертикальногои горизонтального просмотра соответственно.Приведем пример задания аргументов функции ВПР.Вторая синтаксическая форма функции ПРОСМОТР предназначена для поиска данных в табличных массивах и возвращает значение изпоследней строки или последнего столбца таблицы.

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

Оно может быть указано ссылкой (как впримере на рисунке) или конкретным значением. Не забывайте о том, чтодля получения правильных данных первый столбец Таблицы должен бытьупорядочен по возрастанию. Если в первом столбце находятся текстовыезначения, они сравниваются с Искомым_значением без учета регистра букв.При задании аргумента Таблица можно использовать ссылку на соответствующий диапазон ячеек или его имя.Номер_столбца – это номер столбца в Таблице, из которого должнобыть извлечено соответствующее значение. В нашем примере, приведенном на рисунке выше, по заданному номеру «2» возвращается значение из столбца фамилий. Нетрудно догадаться, что по номеру «5» дляэтого примера из приведенного списка был бы получен номер соответ-96Глава 4Организация вычислений в рабочих книгах97ствующей группы.

Если Номер_столбца задан значением меньше 1 илибольше, чем количество столбцов в Таблице, то функция ВПР возвращает значение ошибки #ИМЯ!.Ошибка #Н/Д возвращается также в случае, если Искомое_значение меньше, чем наименьшее значение в первом столбце таблицы.Последний, необязательный аргумент функции называется Интервальный_просмотр. Это логическое значение, которое определяет, нужноли, чтобы функция ВПР искала в первом столбце таблицы точное или приближенное соответствие Искомому_значению. Если аргумент Интервальный_просмотр имеет значение ИСТИНА или опущен, то возвращаетсяприблизительно соответствующее значение. Это значит, что если точноесоответствие не найдено, то подходящим считается наибольшее значениеиз первого столбца таблицы, которое меньше, чем Искомое_значение.Функция ГПР – горизонтальный поиск – по своему назначению ипринципам действия аналогична функции ВПР, с той лишь разницей,что Искомое_значение ищется в первой строке указанной таблицы, аданные извлекаются по номерам строк, а не столбцов.Выполните практическое задание 4.4 по теме Организациявычислений в рабочих книгах из приложения От теории кпрактике.Это задание продемонстрирует на практике возможности испецифику функций просмотра из категории Ссылки имассивы.Формула массиваЕсли аргумент Интервальный_просмотр имеет значение ЛОЖЬ, тофункция ВПР ищет точное соответствие Искомому_значению в первомстолбце1.

Если точного соответствия не найдено, то возвращается значение ошибки #Н/Д.1В этом случае первый столбец таблицы можно и не отсортировывать по возрастанию.Рассматривая выше аргументы некоторых функций, мы уже сталкивались с понятием массива. Очевидно, что самое общее понятие массива подразумевает некоторую совокупность величин. Каждую из такихвеличин обычно называют элементом массива.98Глава 4В Microsoft Excel предусмотрена возможность оперировать с массивами данных как с «единым целым», объединяя их элементы формулоймассива.

Ввод такой формулы для элементов массива всегда должен подтверждаться одновременным нажатием Ctrl + Shift + Enter. Приведемнебольшой пример, который поможет понять эту специфику.Предположим, на рабочем листе имеется два ряда числовых значений с одинаковым количеством элементов. Нужно получить новый рядзначений, каждое из которых является произведением соответствующихэлементов исходных рядов (ci = ai * bi).

Решим эту задачу с использованием формулы массива.Перед созданием формулы массива необходимо выделить диапазон ячеек (C2:C6) дляразмещения значений всех элементов массива, а уже затем начинать составлениеформулы.Ввести знак равенства, как признак формулы в ячейке, отметить диапазон ячеек(А2:А6), ввести знак умножения, отметитьдиапазон ячеек (В2:В6).99Организация вычислений в рабочих книгахЧтобы отредактировать формулу массива, нужно сделать активнойячейкой листа любую из ячеек его элементов и щелкнуть по строке формул – появится текстовый курсор, а фигурные скобки исчезнут. Послетого, как внесены необходимые изменения, подтвердить новую формулу клавишами Ctrl + Shift + Enter – значения будут пересчитаны во всехэлементах массива.Для удаления массива нужно выделить все его элементы и податькоманду удаления.Функция ТРАНСПЭту функцию можно найти в категории Ссылки и массивы.

Она используется для транспонирования массивов – преобразования вертикального диапазона значений в горизонтальный и наоборот. Мы ужерассматривали возможность транспонирования данных на рабочем листе посредством буфера обмена1. Теперь же на примере этой функции мыпродолжим разговор о формулах массивов.У функции ТРАНСП есть только один аргумент – массив. Подготовку формулы начинают с выделения диапазона ячеек, который имеетстолько же строк и столбцов, сколько столбцов и строк имеет аргументмассив.

Затем, вызвав функцию и указав аргумент в поле массив, следует нажать Ctrl + Shift + Enter.Подтвердить ввод формулы массива одновременным нажатием клавишCtrl + Shift + Enter.Формула массива всегда заключена вфигурные скобки (в нашем примере{=A2:A6*B2:B6}).Для всех ячеек, являющихся элементами массива, содержимое строки формул одинаково, и отредактировать или удалить формулу можнолишь для всего массива целиком.

Так, например, при попытке очиститьсодержимое только одной ячейки массива будет получено напоминание:Ctrl + Shift + Enter1Через команду Правка Специальная вставка…100Глава 4Функция ЛИНЕЙНОрганизация вычислений в рабочих книгах101На втором шаге Мастера функций нужно задать аргументы, а именно массивы известных значений Y и X.В процессе обработки результатов эксперимента – массивов числовых значений { xi } и { yi } – часто встает задача аппроксимации полученных данных линейной зависимостью общего вида y = ax + b.

Другимисловами, через экспериментальные точки нужно наилучшим образомпровести прямую линию. Для решения такой задачи используют методнаименьших квадратов.Основываясь на этом методе, функция ЛИНЕЙН возвращает коэффициенты прямой, и, при необходимости, массив различных статистических характеристик (стандартные ошибки, сумму квадратов регрессии и т.д.). Перед вызовом функции ЛИНЕЙН нужно указать диапазонячеек, достаточный для размещения значений, возвращаемых функцией. В этом примере мы ограничимся только подсчетом двух коэффициентов прямой, поэтому на рабочем листе следует выделить две ячейкии вызвать функцию ЛИНЕЙН из категории Статистические.Обратите внимание, что Известные_значения_x не является обязательным аргументом.

Если он не задан, то предполагается, что это массив {1, 2, 3, …} такой же размерности, как массив Известные_значения_y.К необязательным аргументам этой функции относятся такжеКонст и Статистика. Что они определяют при вычислении функцииЛИНЕЙН? Оба они являются логическими значениями. АргументКонст определяет, требуется ли вычисление коэффициента b в уравнении прямой, или же он равен нулю. Если аргумент Конст задан какЛОЖЬ, то расчет производится для уравнения вида y = ax, если жеКонст не указан, он полагается равным ИСТИНА и возвращает коэффициент b (что и требуется для нашего примера).Назначение аргумента Статистика будет рассмотрено чуть ниже, вследующем примере этого параграфа. Пока же оставим поле Статистикапустым (что равноценно для него значению ЛОЖЬ) и на этом завершимзадание аргументов функции.Привычный щелчок покнопке ОК в диалоговом окнедля нашего примера будет ошибочным – вместо возвращениямассива коэффициентов прямой, только в одной из выделенных ячеек появится значениекоэффициента а.Правильным для функцииЛИНЕЙН будет завершение вводаформулы массива – комбинацияклавиш Ctrl + Shift + Enter.102Глава 4103Организация вычислений в рабочих книгахИменно по этой команде в две ячейки, которые были указаны перед вызовом функции, будут возвращены вычисленные значения коэффициентов прямой.Теперь рассмотрим назначение возможного при вызове функцииЛИНЕЙН аргумента Статистика – от того, как он задан, зависит, нужноли вычислить только коэффициенты прямой, или же, в дополнение кним, вернуть регрессионную статистику.Для рассматриваемой нами линейной модели дополнительная регрессионная статистика включает значения следующих статистическихвеличин:Стандартное значение ошибки длякоэффициента aСтандартная значение ошибкидля коэффициента bКоэффициент детерминированности, Стандартная ошибка для оценки yили коэффициент корреляцииЕсли он равен 1, то имеет местополная корреляция с моделью,если равен 0, то уравнение регрессии неудачно для предсказаниязначений y.F-статистика,или F-наблюдаемое значениеF-статистика используется дляопределения того, является линаблюдаемая взаимосвязь междузависимой и независимой переменными случайной или нет.Регрессионная сумма квадратовСтепени свободыСтепени свободы полезны длянахождения F-критическихзначений в статистическойтаблице.

Для определенияуровня надежности моделинужно сравнить значения втаблице с F-статистикой,возвращаемой функциейЛИНЕЙН.Остаточная сумма квадратовДля получения в результате выполнения функции ЛИНЕЙН и двухкоэффициентов прямой, и указанных в таблице выше статистическихвеличин нужно сначала выделить на рабочем листе десять свободныхячеек (диапазон из двух столбцов и пяти строк), и только после этого вызвать Мастер функций и выбрать ЛИНЕЙН.На втором шаге Мастера функций для аргумента Статистика нужноуказать значение ИСТИНА (или 1), чтобы была возвращена дополнительная регрессионная статистика, и подтвердить ввод формулы нажатием клавиш Ctrl + Shift + Enter.Коэффициенты прямойДополнительная регрессионная статистика (значениястатистических величин возвращены в порядке, аналогичном их перечислению вприведенной выше таблице)104Глава 4Действия с матрицамиЕсли заданная квадратная матрица не может быть обращена, функция МОБР возвращает значение ошибки #ЧИСЛО!Для математической обработки числовых данных, представленныхв виде матриц, полезно знать о функциях Microsoft Excel, позволяющихнайти обратную матрицу, произведение матриц, определитель матрицы1.Обратная матрицаМатрицу, обратную исходной, можно получить с помощью функции МОБР.