Функции поиска (функции горизонтального и вертикального просмотра).

Применяются когда невозможно или неудобно использовать функцию ЕСЛИ.

Функция ГПР. Используется для поиска данных в горизонтальной таблице.

Формат функции: =ГПР(что_искать; где_искать; из_какой_строки_взять_результат; как_искать).

Здесь как_искать (тип поиска)это: 0 – поиск точный.

1 – поиск интервальный, если не найдено точное значение, искомым считается ближайшее меньшее

Поиск ведется всегда в первой строке блока поиска (“где искать”). Результат извлекается из параллельной нижележащей строки с указанным номером относительно первой строки блока (нумерация строк ведется внутри блока, начиная с №1).

Имеется вертикальный аналог функции ГПР – функция ВПР (для работы с вертикально расположенными тарифами)

=ВПР(что_искать; где_искать; из_какого_солбца_взять_результат; как_искать).

Поиск ведется в первом столбце блока поиска. Результат извлекается из параллельного столбца справа с заданным номером.

Задание 9. Повременная оплата с учетом разряда. Рассчитать заработную плату, зависящую от числа отработанных дней в месяце, разряда и премии. Для вычисления собственно зарплаты (область D7: D9) нужно число дней умножить на тариф, зависящий от разряда рабочего

зарплата=тариф_по_разряду • дней.

Для розыска разрядного тарифа понадобится функция ГПР. Так, формула для Петра

зарплата_Петра=ГПР(разряд_Петра; тарифная_сетка; строка_“тариф”; поиск_точный) • дней или

D7 =ГПР(C7; B$2: F$4; 2; 0)*B7. (=100р•10дн)– строка результата “тариф” имеет №2 внутри блока поиска, строка “премия” ­– №3. Премия также зависит от разряда. Напишите формулу E7=ГПР(............................

Другая постановка. Пусть премия дается только при отработке > 5 дней

E7=ЕСЛИ(B7> 5; ГПР(…); 0)*D7 или иначе E7=ГПР(…)*D7*ЕСЛИ(B7> 5; 1; 0). Здесь ГПР(…) это

Еще задача. Отработавшим > 25 дней к премии добавляется еще 10% E7=...........................

Задание 10. Конвертирование валюты. При внешнеторговых операциях расчет с поставщиками выполняется в долларах, а с внутренними покупателями – в рублях и нужно конвертировать в рубли исходную (в момент поступления) и текущую (“на сегодня”) цены товара. Информация хранится в двух таблицах: таблице курса доллара (столбцы А, В) и таблице расчета рублевого эквивалента товара. Содержимое первого – последовательные значения дат и цены $. В клетке E1 предъявляется текущая дата. В основной таблице содержатся сведения о дате закупки товара и его закупочной цене в $ (столбцы D и F). В столбце G вычисляется рублевая цена товара на момент покупки, в H – его сегодняшняя рублевая цена. Для столбцов G, H понадобится функция ВПР с четвертым аргументом =1, т.е. поиск даты в курсовой таблице будет не точным, а интервальным, поскольку некоторых дат там нет и стоимость доллара тогда берется равной курсу ближайшей предыдущей даты, для которой она имеется (так, для 9 и 10 января берется курс за 8-е, для 13.янв – за 12-е).

Цена_закупки_в_руб. = Курс_$_на_день_закупки • цена_закупки_в_$ или

Цена_закупки_в_руб. = ВПР(дата_закупки; курсовая_таблица; столбец _“цена_$”;

поиск_интервальный) • цена_закупки_в_$ или

G3 =ВПР(D3; A$2: B$10; 2; 1)*F3. – нижняя граница (B10) блока поиска берется с запасом для ввода новых дат и курсов $.

	А	В	C	D	E	F	G	H	I	A B C   Доход % налога       12%     18%     свыше 25%     Вычисление налогов   Имя Доход Налог   Иван   ?   Петр   ?   Анна   ?   J
	Дата	Цена $		Сегодня	13.янв
2	04.янв	20р		Дата закупки	Товар	Цена($) закупки	Цена (р) закупки	Цена (р) сегодня	Цена +30%	Цена+ 40/50%
	05.янв	21р		04.янв	Стол	100$	? р	? р	? р	? р
	06.янв	22р		06.янв	Стул	50$	? р	? р	? р	? р
	07.янв	23р		09.янв	Тумба	80$	? р	? р	? р	? р
	08.янв	24р		10.янв	Шкаф	100$	? р	? р	? р	? р
	11.янв	25р		11.янв	Палас	70$	? р	? р	? р	? р
	12.янв	26р		12.янв	Софа	100$	? р	? р	? р	? р

Вычислить рублевый эквивалент товара на сегодня (столбец H):

Цена_сегодня_в_руб.=Курс_$_на_сегодня • цена_закупки_в_$. Записать H3=ВПР( ……………………………….…………….

Усложним задачу. Цена_сегодня, найденная в такой постановке, дает себестоимость товара на сегодня. Чтобы иметь прибыль надо продать его дороже (например на 30%). Напишите формулу в столбце I (не используя содержимое столбца H):

I3=ВПР(................................................................................................................................................

Усложним задачу. Пусть прибыль начисляется в зависимости от цены_закупки. На товар ценой менее 100$ она составляет 50%, на остальные – 40%. Напишите формулув столбце J (можно использовать H):

J3=ЕСЛИ(F3.......................................................................................

Еще. Товар, закупленный более 100 дней назад продается по себестоимости H, остальное, как в J.

К3= ЕСЛИ(..........................................................................................

Замечания: 1. Даты следует вводить в числовом виде, так 4.1 будет автоматически преобразовано в 04.янв.

2. Единицы валюты (р и $) непосредственно (руками) вводить нельзя. Они устанавливаются форматированием данных.

Задание 11. Прогрессивный налог. Вычислить годовой подоходный налог с граждан. С дохода до 15т.руб. бе­рется налог в размере 12%, с дохода до 35т – 18%, с дохода свыше 35т – 25%. Причем более высокий налог берется с той частью дохода, которая находится в соответствующем диапазоне. Например, с дохода в 60т, налог будет таков:

		таким образом, для дохода в 60т. руб. налог=12%•15+18%•20+25%•25=11, 65т.

Формула в общем виде: налог=ЕСЛИ(доход< 15; 12% • доход;

ЕСЛИ(доход< 35; 12% • 15+18% • (доход-15); 12% • 15+18% • (35-15)+25% • (доход-35)))

Записать формулу вычисления налога, используя только адреса (а не константы) и “заморозив” нужные ссылки.

C7=ЕСЛИ(B7< A$2; B$2*B7; ЕСЛИ(B7< A$3;...............

..................................

Задание 12. Начисление стипендии. В сессию студентами сдавались два экзамена (вводится оценка) и зачета (вводится символ “+”). Неудовлетворительные оценки не выставляются (клетка остается пустой). Вычислить средний балл и стипендию, которая назначается студентам таким образом:

отличники – 3 минимальные зарплаты (МЗ)

сдавшие без троек (хорошисты) – 2 минимальные зарплаты

остальные сдавшие (троечники) – 1 минимальная зарплата

несдавшие – 0

Средний балл (F4) – среднее арифметическое оценок экзаменов, но только если сессия сдана, в противном случае он принимается равным нулю:

средний_балл=ЕСЛИ(сессия_сдана; то (логика+этика)/2; иначе 0) или

средний_балл=ЕСЛИ(логика_сдана И этика_сдана И химия_сдана

И право_сдано; (логика+этика)/2; 0) или

средний_балл=ЕСЛИ(логика> 2 И этика> 2 И химия=”+” И право=”+”; (логика+этика)/2; 0)

Отсюда в виде формулы для первого студента (Петра):

F4 =ЕСЛИ(И(B4> 2; С4> 2; D4=”+”; E4=”+”); (B4+С4)/2; 0).

Средний балл можно вычислить и иначе, исходя от обратного – если не сдана какая-либо из дисциплин, он принимается равным 0, иначе – среднее арифметическое:

средний_балл=ЕСЛИ(сессия_не_сдана; то 0; иначе (логика+этика)/2) или

=ЕСЛИ(логика_не_сдана ИЛИ этика_не_сдана ИЛИ химия_не_сдана ИЛИ право_не_сдано; 0; (логика+этика)/2) или

=ЕСЛИ(логика=0 ИЛИ этика=0 ИЛИ химия=0 ИЛИ право=0; 0; (логика+этика)/2)

F4 =ЕСЛИ(ИЛИ(B4=0; С4=0; D4=0; E4=0); 0; (B4+С4)/2). – пустая клетка считается равной нулю

а можно, используя функцию подсчета занятых клеток СЧЁТЗ, записать и так

F4 =ЕСЛИ(СЧЁТЗ(B4: E4)< 4; 0; (B4+С4)/2).

Найдем стипендию:

стипендия=ЕСЛИ(отличник; 3; ЕСЛИ(хорошист; 2; ЕСЛИ(троечник; 1; иначе 0))) • МЗ или

стипендия=ЕСЛИ(ср._балл=5; 3; ЕСЛИ(И(логика> 3; этика> 3; ср._балл> 0); 2; ЕСЛИ(ср._балл> 0; 1; иначе 0))) • МЗ

Окончательно для Петра (МЗ вынесем за ЕСЛИ):

G4 =ЕСЛИ(F4=5; 3; ЕСЛИ(И(B4> 3; C4> 3; F4> 0); 2; ЕСЛИ(F4> 0; 1; 0)))*G$1.

Самостоятельно напишите выражение для стипендии при обратном порядке анализа успеваемости:

стипендия=ЕСЛИ(двоечник; 0; ЕСЛИ(троечник; 1; ЕСЛИ(хорошист; 2; иначе 3))) • МЗ

G4 =ЕСЛИ(............................................................................