Обработка и анализ данных в среде excel

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

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

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

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

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

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

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

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

      Функция ПОИСКПОЗ. Возвращает относительное положение элемента массива, который соответствует заданному значению. Например: ПОИСКПОЗ("б";{"а";"б";"в"};0) возвращает 2 -- относительную позицию буквы "б" в массиве {"а";"б";"в"}. ПОИСКПОЗ не различает регистры при сопоставлении текстов:

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

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

      Пример  использования встроенных функций для работы с массивами. Для заполнения поля «Коэф А» таблицы 1 (рисунок 3.1) на основании таблицы 2 в ячейку С3 можно ввести одну из следующих формул:

1) =ПРОСМОТР(B3;$F2:$F5;$G2:$G5);

2) =ВПР(B3;$F2:$H5;2);

3) =ИНДЕКС(;$F2:$H5;ПОИСКПОЗ(B3; ;$F2:$F5;0);2).

 

Рисунок 3.1 – Исходные данные для работы с функциями категории «Ссылки и массивы» 

      Результатом вычислений по каждой из формул будет значение 1,8. Для заполнения ячеек С4:С7 в них необходимо скопировать введенную в ячейку С3 формулу. 

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

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

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

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

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

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

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

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

      Шаг 1. Выбирают источник с данными (список или база данных Excel) и вид создаваемого отчета (сводная таблица).

      Шаг 2. Задают или подтверждают диапазон, содержащий исходные данные.

      Шаг 3. Выбирают размещение таблицы (новый или существующий лист). В случае размещения сводной таблицы на существующем листе указывают адрес левой верхней ячейки  сводной таблицы. После нажатия кнопки «Готово» появляется конструктор обработки данных списка (рисунок 3.2)

Рисунок 3.2 – Конструктор обработки данных

       Конструктор позволяет задать структуру сводной таблицы, т.е. определить заголовки исходного списка, которые будут использоваться в качестве полей страницы, столбца, строки. Структура задается путем перетаскивания нужных полей на соответствующие области шаблона таблицы. Выбираются данные, по которым будут подводиться итоги. В качестве данных по умолчанию предлагается сумма по выбранному полю числовых данных или количество значений, если исходные данные текстовые. Например, для определения суммарной заработной платы по магазинам в область «Поля строк» перетаскиваем элемент «№ магазина» из списка полей, в область «Элементы данных» перетаскиваем элемент «Зарплата» из списка полей. Полученная сводная таблица приведена на рисунке 3.3. Чтобы выбрать другие итоги (например, среднее значение), следует правой кнопкой вызвать контекстное меню и выбрать строку «Параметры поля».Для удаления любого поля из области конструктора нужно просто потянуть за него мышкой и вытащить за пределы данной области

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

       3.2. Порядок выполнения задания 

1.  Выбрать  вариант  i=N mod 15, где N – две последние цифры зачетной книжки.

2. Перенести  на рабочий лист таблицу 3.1. Добавить в таблицу пустые столбцы с заголовками «Коэф_A», «Коэф_В», «Зарплата». Присвоить таблице имя «Сотрудники»

3. Создать на этом же рабочем листе таблицу, содержащую поля: «Должность», «Коэф_А», «Коэф_В». Заполнить таблицу в соответствии с вариантом задания (таблица 3.2). В поле «Должность» ввести значения: Кассир, Продавец, Ст.продавец. Присвоить таблице имя «Коэффициенты».

4. В  свободную ячейку рабочего листа занести значение минимальной заработной платы в соответствии с вариантом (таблица 3.2). Присвоить ячейке имя «Мин_зарплата».

5. Заполнить поля «Коэф_A», «Коэф_В», «Зарплата» таблицы «Сотрудники»: поля «Коэф_A» и «Коэф_В» заполнить на основании таблицы «Коэффициенты» с помощью функции ВПР, используя в качестве искомого значения должность сотрудника. Зарплата сотрудника считается по формуле Зарплата=Коэф_А*Мин_зарплата+Коэф_В.

6. Вычислить Фонд заработной платы, используя для поля «Зарплата» таблицы сотрудники итоговую функцию СУММ. Используя режим «Подбор параметра» определить  минимальную заработную плату для заданного вариантом фонда заработной платы. В качестве изменяемой ячейки выбрать ячейку с именем «Мин_зарплата».

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

      3.3. Варианты индивидуальных  заданий 

      Таблица 3.1

 

      Таблица 3.2