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

Лабораторная работа №6

Использование MICROSOFT EXCEL в экономических расчетах

Цель - приобрести навыки применения функций просмотра и ссылок в решении экономических задач, обработки списков в режиме подбора параметров, создания сводных таблиц

Краткие теоретические сведения

Для извлечения информации из блоков (диапазонов) используют функции категории «ссылки и массивы». Массив - это объект Excel, используемый для получения нескольких значений в результате вычисления по одной формуле (диапазон массива) или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам (диапазон констант).

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

Для присвоения имени используется команда Формулы – Присвоить Имя. В поле Имявводится имя диапазона, в поле Формула указывается ячейка или диапазон (вводится непосредственно, либо выделяется с помощью мыши).

После нажатия кнопки Добавитьимя отображается в соответствующем поле. По умолчанию имена являются абсолютными ссылками. Использование имен может упростить понимание формулы.

Например, формула «=Активы-Пассивы» понятнее формулы «=F6-D6». Имена можно использовать в любом листе книге.

Например, если имя «Контракты» ссылается на группу ячеек "A20:A30" в первом листе рабочей книге, то это имя можно применить в любом другом листе той же рабочей книги. Можно использовать команду Формулы - Диспетчер имен -Создать.

Основные встроенные функции для работы с массивами (категория Ссылки и массивы)

Функция ПРОСМОТР.

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

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

Искомое_значение – значение, которое ищется в просматриваемом векторе; может быть числом, текстом, логической константой, ссылкой на ячейку.

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

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

Функция ВПР. Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы:

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

Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала.

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

Если интервальн_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной.

Функция ИНДЕКС -возвращает значение элемента таблицы или массива, заданного номером строки и номером столбца:

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

Номер_строки это номер строки в массиве, из которой нужно возвращать значение.

Номер_столбца - это номер столбца в массиве, из которого нужно возвращать значение.

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

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

Например, ПОИСКПОЗ("б";{"а";"б";"в"};0) возвращает 2 - относительную позицию буквы "б" в массиве {"а";"б";"в"}.

ПОИСКПОЗ не различает регистры при сопоставлении текстов:

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

Искомое_значение - это значение, используемое при поиске значения в таблице, которое сопоставляется со значениями в аргументе просматриваемый_массив.

Просматриваемый_массив - это непрерывный интервал ячеек, содержащих искомые значения.

Тип_сопоставлен - это число -1, 0 или 1.

Если тип_сопоставлен равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем искомое_значение.

Просматриваемый_массив должен быть упорядочен по возрастанию.

Если тип_сопоставлен равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение.

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

Просматриваемый_массив должен быть упорядочен по убыванию. Если тип_сопоставления опущен, то предполагается, что он равен 1.

Подбор параметра

Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если".

Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра». Выбрав на ленте вкладку Данные,и в группе «Работа с данными», раскройте меню кнопки «Анализ что-если». В списке команд выберите пункт «Подбор параметра».

В окне команды Подбор параметранеобходимо заполнитьполя:

- Установить в ячейке: вводят адрес ячейки, содержащей формулу;

- Значение: вводят искомое значение;

- Изменяя значение ячейки:вводят ссылку на изменяемую ячейку.

В ячейку, задаваемую в поле Изменяя значение ячейкипо определенному алгоритму подставляются значения до тех пор, пока не будет найдено решение, задаваемое в поле Значение.

По умолчанию по команде Подбор параметравычисления прекращаются либо после 100 операций, либо когда найденное решение отличается от заданного не более чем на 0.01.

Если требуется большая точность, то необходимо выбрать команду Офис-Параметры Еxcel - Формулыи изменить поле Предельное число итерацийили поле Относительная погрешность.При подборе параметра решение имеет тот же знак, что и начальное значение.

Сводные таблицы

Сводные таблицы позволяют осуществлять групповые операции над данными, находящимися либо в списках, либо во внешних базах данных. Для построения сводных таблиц используют Мастер сводных таблиц, запускаемый с помощью команды Вставка - Сводная таблица.

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

В итоге открывается панель Список полей сводной таблицы(рисунок 1) для создания ее макета.

Рисунок 1 – Макет для создания сводной таблицы

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

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

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

После нажатия ОК на листе создается сводная таблица, а на Лентепоявляется временный раздел Работа со сводными таблицамис двумя вкладками Параметрыи Конструктордля работы со сводной таблицей.

Например, для определения суммарной заработной платы по магазинам в область «Поля строк» перетаскиваем элемент «№ магазина» из списка полей, в область «Элементы данных» перетаскиваем элемент «Зарплата» из списка полей. Полученная сводная таблица приведена на рисунке 2.

Чтобы выбрать другие итоги (например, среднее значение), следует правой кнопкой вызвать контекстное меню и выбрать строку «Параметры поля». Для удаления любого поля из области конструктора нужно просто потянуть за него мышкой и вытащить за пределы данной области

Рисунок 2 – Пример сводной таблицы