Использование встроенных функций обработки массивов MS Excel

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

«Использование встроенных функций обработки массивов MS Excel.»

Цель работы. Освоение  использования функций в расчетных таблицах МS Excel .

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

Функции ссылки и автоподстановки

Разделы в "Функции ссылки и автоподстановки"

18 элементы

Функции ссылки и поиска

Функция АДРЕС

Функция ОБЛАСТИ

Функция ВЫБОР

Функция СТОЛБЕЦ

Функция ЧИСЛСТОЛБ

Функция ГПР

Функция ГИПЕРССЫЛКА

Функция ИНДЕКС

Функция ДВССЫЛ

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

Функция ПОИСКПОЗ

Функция СМЕЩ

Функция СТРОКА

Функция ЧСТРОК

Функция ДРВ

Функция ТРАНСП

ВПР

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

Буква В в названии функции ВПР означает «вертикально». Функция ВПР используется вместо функции ГПР, если сравниваемые значения расположены в столбце слева от искомых данных.

Синтаксис

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

Искомое_значение  — значение, которое должно быть найдено в первом столбце табличногомассива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.). Этот аргумент может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.

Таблица  — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.

Номер_столбца   — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д. Если значение аргумента «номер_столбца»:

1 2 3 4 5 6 7 8 9 10

А B C Плотность Вязкость Температура 0,457 3,55 500 0,525 3,25 400 0,616 2,93 300 0,675 2,75 250 0,746 2,57 200 0,835 2,38 150 0,946 2,17 100 1,09 1,95 50 1,29 1,71 0

Формула	Описание (результат)
=ВПР(1;A2:C10;2)	Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке (2,17)

      Выполните расчеты в одном файле (РАСЧЕТЫ)  на разных листах.

Задание 1  Дана таблица, содержащая список разрядов и соответст­вующих им коэффициентов. Составьте таблицу "Зарплата", если для каждого преподавателя известен присвоенный ему разряд.

Решение.

1. Создайте таблицу по образцу Таблица 1, соблюдая шрифты и пе­реносы по словам.

	А	В		С	D	Е	F	G	Н
1	Ведомость
2								Справочная таблица
3	минимум		110,00р.					Разряд	Коэф.
4								9	2,81
5	Фамилия		Разряд	Зарплата				10	3,15
6	Колыванов		11					11	4,51
7	Сергеева		13					12	6,11
8	Конышева		13					13	8,45
9	Петров		14					14	10,1
10	Самарцев		11					15	12,3

2. Заполните ячейки С6:С10. Зарплата каждого преподавателя вы­числяется по формуле: коэффициснт*минимум. Каждый коэффициент оп­ределяется в соответствии с разрядом. Например, у Колыванова 11 разряд, следовательно его коэффициент 4,51. Для определения коэффициента по данному разряду можно воспользоваться функцией ПРОСМОТР из катего­рии Ссылки и массивы.

• Распишите аргументы функции на примере первого человека в списке.

 Данные для поиска - разряд (В6), область поиска - список разрядов ($G$4:$G$10); результат поиска - определяется из списка коэффици­ентов ($Н$4:$Н$10);

• Запишите в тетрадь функцию просмотра: ПPOCMOTP(B6;$G$4:$G$10;$Н$4:$Н$10).

3.    Занесите в ячейку С6 формулу:

=ПРОСМОТР(В6;$G$4:$G$10;$Н$4:$Н$10)*$В$3.

4.     Проверьте результат. В ячейке С6 должна получиться сумма 496,10 р.

5.     Размножьте полученную формулу на клетки С7:С10.

6.     Проверьте результат. В ячейках С7:С10 должны получиться сум­мы 929,50 р.; 929,50 p.; 1111,00 р.; 496,10 р. соответственно.

Задание 2.

1.  Введите массивы  данные, начиная со строки  10 – ТАБЛИЦА ДЛЯ ПРОСМОТРА ЗАПАСОВ ОБОЕВ.

2.  Назовите блок ячеек В14:Е23 – ЗАПАСЫ.

Создайте основную таблицу:

3.  Введите заголовок в А1 – СПРАВКА по ЗАПАСАМ.

Введите в ячейку  А3 – Введите код товара

Внимание  возможно в ячейке  В4 появиться сообщение об ошибке, оно исчезнет при вводе значения в ячейку В3

4.  Введите в ячейку  А4 – Код цены

5.  Введите в ячейку  А5 – Цена продажи

6.  Введите в ячейку  А6 – Остаток запасов

7.  Введите в ячейку  А7 – Уровень заказов

8.  Ячейку В3 – оставьте пустой

9.  В ячейку В4 – введите формулу выбора по заданному значению (кода товара) из блока ЗАПАСЫ – Код цены

= ВПР(В3;ЗАПАСЫ;2)

10.         Установите денежный формат в ячейке В5 и введите формулу для поиска Цены продажи по Коду цены (В4) в Таблице кодов цены

= ВЫБОР(В4;Н14;Н15;Н16;Н17;Н18;Н19)

11.         В ячейках В6 и В7 задайте формулы которые по Коду товара  найдут Остаток запасов и Уровень заказов.

12.         Введите значение КОДА ТОВАРА в ячейку В3 – проверьте правильность выбора.

13.         Добавьте в А8 –  заголовок Описание  и В8 введите формулу поиска (выбора из таблицы) описания обоев.

	A	B	C	D	E	F	G	H
10	ТАБЛИЦА ДЛЯ ПРОСМОТРА ЗАПАСОВ ОБОЕВ						ТАБЛИЦА КОДОВ ЦЕНЫ
11
12	Описание	Код товара	Код цены	Остаток запасов	Уровень заказов		Код цены	Цена продажи
13
14	Созвездие, зеленый	1245	6	24	10		1	€ 7,50
15	Созвездие, синий	1246	6	25	10		2	€ 7,75
16	Папоротник, зеленый	1247	3	12	10		3	€ 8,00
17	Папоротник, коричневый	1248	3	16	10		4	€ 10,00
18	Ромашки, красный	1249	4	38	20		5	€ 11,50
19	Ромашки, голубой	1250	4	20	20		6	€ 12,35
20	Ромашки, желтый	1251	4	12	20
21	Фестиваль, розовый	1252	5	10	10
22	Фестиваль, синий	1253	5	25	10
23	Фестиваль, зеленый	1254	1	15	20

Задание 3.

Дана таблица, содержащая сведения о доставке продукции, необходимо заполнить столбик наименование и цену, используя справочную таблицу (ее ввести ниже)

	Транспортная ведомость  на доставку продукции
Месяц	Код	Наименование  товара	Количество	Цена	Сумма
январь	1111115		24
январь	1111111		21
январь	1111114		33
январь	1111112		42
февраль	1111116		30
февраль	1111111		12
февраль	1111116		33
февраль	1111112		39
февраль	1111116		27
февраль	1111111		42
март	1111113		15
март	1111112		18
март	1111116		15
март	1111114		18
март	1111115		33
март	1111113		36
апрель	1111114		42
апрель	1111115		15

Для заполнения полей «Наименование  товара», «Цена” использовать данные справочной таблицы и специальные функции из раздела Ссылки и массивы:

ВПР(),

ПРОСМОТР()

Справочная таблица
Код	Цена	Наименование
1111111	100р.	Гайки
1111112	239р.	Гвозди
1111113	313р.	Болты
1111114	259р.	Шурупы
1111115	50р.	Шайбы
1111116	239р.	Винты

Справочную таблицу разместить на листе 2.