Технология работы в электронной таблице. Разработка программы расчета с клиентами за проживание в гостинице
1. Постановка задачи.
Информация о ценах на гостиничные услуги, курсе доллара и доходах гостиницы за определенный период хранится на четырех листах:
Лист 1. Расценки.
Цены проживания и бронирования
| Тип номера | Цена в сутки одного койко-места, долл. |
| 1-местный | 33 |
| 2-местный | 55 |
| Цена бронирование | 4,4 |
| Тип номера | Цена в сутки одного койко-места, долл. |
| 1-местный | 33 |
| 2-местный | 55 |
| Цена бронирование | =СРЗНАЧ(B3:B4)*10% |
Лист 2. Курс доллара.
Лист 3. Расчёты с клиентами гостиницы.
| Скидка на проживание | 15% | |||||||
| Клиент | Бронь | Проживание | Цена номера | Оплата | Скидка | Оплата со скидкой | Общая сумма | |
| с: | по: | |||||||
| Иванов | да | 01.12.2009 | 20.12.2009 | 55 | ||||
| Сидоров | да | 11.12.2009 | 16.12.2009 | 33 | ||||
| Каспарян | нет | 01.12.2009 | 05.12.2009 | 55 | ||||
| Кавтарадзе | нет | 01.12.2009 | 03.12.2009 | 55 | ||||
| Хван | нет | 02.12.2009 | 25.12.2009 | 33 | ||||
Лист 4. Доход гостиницы.
| Дата | Число проживающих в номерах | Число бронирований | Доход гостиницы | ||
| Одноместных | Двухместных | долл. | руб. | ||
| 11.12.2009 | 34 | 51 | 11 | ||
| 12.12.2009 | 28 | 59 | 16 | ||
| 13.12.2009 | 40 | 62 | 10 | ||
| 14.12.2009 | 35 | 49 | 8 | ||
| 15.12.2009 | 30 | 33 | 15 | ||
| 16.12.2009 | 25 | 42 | 20 | ||
2. Формулы для расчета.
Он = (tк- tн)*Цном,
Он – оплата номера;
tк – дата съезда;
tн – дата заезда;
Цном – цена номера.
Скидка за оплату за проживание в отеле: С = ЕСЛИ ((tк – tн)>10), Он *15%, Он) если количество дней проживания в отеле больше 10.
Доплата: D = ((Цод+Цдв)/2)*10%, если номер забронирован. D – доплата, Цод – цена одноместного номера, Цдв – двухместного номера.
Общая сумма оплаты за проживание: если номер забронирован Соб = С+D.
В результате получается:
Расчёты с клиентами гостиницы (в режиме отображения формул)
Таблица в режиме отображения значения
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Функция ВПР в MS Excel. Описание и примеры использования.
Функция ВПР в MS Excel. Описание и примеры использования.
В данной статье, на простых примерах, описаны варианты использования функции ВПР MS Excel. Важные аспекты и возможные ошибки, которые возникают при использование данной функции. Функция ВПР в Excel.
Как вызвать функцию ВПР. Функция ВПР в Excel
В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.
Появляется диалоговое окно Вставка функции. В строке Поиск функции вводим ВПР. Нажимаем найти. По результатам поиска, в пункте Выберите функцию, появляется ВПР. Нажимаем на нее левой кнопкой мыши два раза или нажимаем ОК. Появляется непосредственно диалоговое окно функции ВПР – Аргументы функции.
Теперь перейдем непосредственно к вариантам применения функции ВПР.
Первый вариант использования функции ВПР.
Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.
Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.
Вызываем функцию ВПР, как описано выше.
Аргументы функции. Функция ВПР в Excel.
Искомое_значение.
Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.
Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).
Таблица.
Можно присвоить нашему диапазону имя, и прописать его в строке Таблица.
Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.
В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты 
Номер_столбца.
Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.
Интервальный _просмотр.
В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.
Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.
Вот как это выглядит все вместе.
Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.
Второй вариант использования функции ВПР.
У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.
Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.
В диалоговом окне, Аргументы функции прописываем следующие данные:
Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).
Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).
Номер_столбца. Поскольку в таблице указан конкретный столбец, то здесь мы ставим цифру 1, так как искомое значение и данные, которые мы хотим перенести совпадают.
Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.
Нажимаем ОК и протягиваем функцию по всему столбцу. В некоторых ячейках, вместо искомого значения появляется ошибка формулы: #Н/Д. В данном случае, это значит, что заданные критерии поиска не были обнаружены в проверяемом столбце.
В нашем примере, в Таблице №2 есть Значение 17 и Значение 10. При проверке, в ячейки с функцией ВПР, вместо искомого значения появилась ошибка #Н/Д. Это значит, что в Таблице №1 нет ячейки с Значением 17 и Значением 10.
Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.
Обратите внимание. Функция ВПР в Excel.
Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.
Пример, в Таблицу №1, добавили столбец Категория, и теперь столбец Название конфет уже не первый, а второй. Если мы укажем в строке Таблица, в качестве диапазона, все ячейки Таблицы №1, то функция ВПР не сработает (ошибка — #Н/Д), так как она будет осуществлять точный поиск в столбце Категория, Таблицы №1, значений из столбца Название конфет, Таблицы №2. И не найдет точных совпадений.
Это особенность, так же говорит нам о том, что значения, которые мы хотим перенести в нашу таблицу, должны быть справа, от столбца, в котором происходит поиск по заданному критерию.
Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.
Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).
Возможные ошибки.
#Н/Д — столбец таблицы, по которому происходит поиск, не крайний левый в диапазон поиска.
#Н/Д — диапазон таблицы, в которой происходит поиск, не закреплен. Нужно использовать либо абсолютные ссылки ($), либо присвоить диапазону Заданное имя.
#Н/Д – функция ВПР не находить точного совпадения в диапазоне поиска по заданному значению поиска.
#Н/Д – возможно необходимо отсортировать диапазон, в котором происходит поиск, по возрастанию.
#ССЫЛКА! – возможно номер столбца, который указан в строке Номер_столбца, указан неверно, и функция не находит данные, которые должна перенести.
10 формул Excel, которые пригодятся каждому
Не нужно тратить время на подсчёты в Microsoft Excel вручную, ведь существует множество формул, которые помогут быстро справиться с поставленными задачами и повысить точность ваших отчётов. Мы собрали 10 наиболее полезных формул, которые вы сможете выучить за один день.
Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)
Функция СУММ позволяет вычислить сумму двух или более чисел. В этой формуле вы также можете использовать ссылки на ячейки.
С помощью формулы вы можете:
Англоязычный вариант: =COUNT(A1:A10)
Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).
СЧЁТЗ
Англоязычный вариант: =COUNTA(A1:A10)
С помощью данной формулы можно подсчитать количество заполненных ячеек в одном ряду, то есть тех, в которых есть не только числа, но и другие знаки. Преимущество формулы – её можно использовать для работы с любым типом данных.
ДЛСТР
Англоязычный вариант: =LEN(A1)
Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.
СЖПРОБЕЛЫ
Англоязычный вариант: =TRIM(A1)
Данная функция помогает избавиться от пробелов, не включая при этом пробелы между словами. Эта опция может быть чрезвычайно полезной, особенно в тех ситуациях, когда вы вносите в таблицу данные из другого источника и при вставке появляются лишние пробелы.
Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.
ЛЕВСИМВ, ПСТР и ПРАВСИМВ
=ЛЕВСИМВ(адрес_ячейки; количество знаков)
=ПРАВСИМВ(адрес_ячейки; количество знаков)
=ПСТР(адрес_ячейки; начальное число; число знаков)
Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).
Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.
Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».
Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».
Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».
Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)
Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)
Функция ВПР работает как телефонная книга, где по фрагменту известных данных – имени, вы находите неизвестные сведения – номер телефона. В формуле необходимо задать искомое значение, которое формула должна найти в столбце таблицы.
Например, у вас есть два списка: первый с паспортными данными сотрудников и их доходами от продаж за последний квартал, а второй – с их паспортными данными и именами. Вы хотите сопоставить имена с доходами от продаж, но, делая это вручную, можно легко ошибиться.
Эта формула не такая простая, как предыдущие, тем не менее она очень полезна в работе.
Формула: =ЕСЛИ(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)
Англоязычный вариант: =IF(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)
Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.
Снова обратимся к примеру из сферы продаж: допустим, что у каждого продавца есть установленная норма по продажам. Вы использовали формулу ВПР, чтобы поместить доход рядом с именем. Теперь вы можете использовать оператор «ЕСЛИ», который будет выражать следующее: «ЕСЛИ продавец выполнил норму, вывести выражение «Норма выполнена», если нет, то «Норма не выполнена».
В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:
=ЕСЛИ(B1>C1; «Норма выполнена»; «Норма не выполнена»)
Функция «ЕСЛИ» покажет нам, выполнил ли первый продавец свою норму или нет. После можно скопировать и вставить эту формулу для всех продавцов в списке, значение автоматически изменится для каждого работника.
СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ
Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)
=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)
Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)
Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.
Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.
Используя функции на предыдущем примере, мы можем узнать:
СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.
СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.
СЧЁТЕСЛИ – количество продавцов, выполнивших норму.
Конкатенация
Формула: =(ячейка1&» «&ячейка2)
За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&» «&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединёнными данными появился пробел.
Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).
Кстати, все перечисленные формулы можно применять и в Google‑таблицах.
Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.
Функция HLOOKUP (ГПР) в Excel
Синтаксис функций ВПР и ГПР
Функции имеют 4 аргумента:
! Если значения в диапазоне отсортированы в возрастающем порядке (либо по алфавиту), мы указываем ИСТИНА/1. В противном случае – ЛОЖЬ/0.
Как пользоваться функцией ВПР в Excel: примеры
Для учебных целей возьмем таблицу с данными:
| Формула | Описание | Результат |
| Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение. |  | |
| Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено». |  | |
| Если «бананы» сменить на «груши», результат будет «Найдено» |  | |
| Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА. |  Мы узнаем, были ли продажи 05.08.15 | |
| Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными. |  Мы захотели узнать, кто работал 8.06.15. | |
| Поиск приблизительного значения. |  |
Как пользоваться функцией ГПР в Excel: примеры
Для учебных целей возьмем такую табличку:
| Формула | Описание | Результат |
| Поиск значения ячейки I16 и возврат значения из третьей строки того же столбца. |  | |
| Еще один пример поиска точного совпадения в другой табличке. |  |
Применение ГПР на практике ограничено, так как горизонтальное представление информации используется очень редко.
Как же конкретно работает формула ВПР
Следствия для формул вида I
Следствия для формул вида II
Если искомое значение встречается в первом столбце массива несколько раз, то формула выберет первую строку для последующего извлечения данных.
Как считать доходность инвестиций в Excel
В Excel для расчета доходности инвестиций с учетом ввода/вывода денег используется функция ЧИСТВНДОХ (XIRR) — это функция, которая возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Как ей пользоваться? Возьмем пример из статьи Арсагеры:
Доходность портфеля составила 8,004% годовых.
Введем эти данные в Excel. В первой колонке указываем суммы, во второй даты.
Жмем ОК, получаем доходность — 8,009% годовых.
Если бы мы считали по простой формуле, то получили бы результат (1300-1200)/1200=8,3%. Вроде бы разница небольшая, но в других примерах разница может составить несколько процентов.
Функцию в ячейку так же можно вписать руками. Для этого в пустой ячейке впишите текст: =ЧИСТВНДОХ(A1:A4;B1:B4), номера ячеек укажите свои.
Расчет внутренней ставки доходности облигаций в Excel
Пример 2. В таблице Excel внесены данные об операциях с облигацией (покупка, выплата купонов, погашение). Выплаты производились несистематично. Определить прибыль и внутреннюю ставку доходности облигации за указанный период.
Вид таблицы данных:
Для определения прибыли используем формулу:
То есть, определяем разницу между суммарными выплатами по купонам + погашением и расходами на приобретение облигации:
Для определения ставки доходности используем формулу:
На практике подобные расчеты имеют смысл для достаточно больших периодов времени, поскольку на рынке зачастую возникают кризисные явления. Оптимальный период расчета доходности инвестиционного портфеля с использованием функции ЧИСТВНДОХ – от 5 лет и более.
Пример расчёта
Для расчёта нам будет необходима табличная программа. Это может быть Microsoft Excel, бесплатный OpenOfiice Calc, Apple Numbers, или какая-нибудь другая. Сперва нам будет необходимо подготовить таблицу с датами вводов и выводов на счёт, и суммами. Обычно такую таблицу легко получить из брокерского отчёта. Вводы на счёт необходимо ввести со знаком минус, а выводы ( доход) — со знаком плюс. В последней строке таблицы необходимо поставить текущую ликвидационную стоимость портфеля. Выглядеть это может примерно следующим образом:
Здесь приведён выдуманный пример инвестирования в течении шести лет. За весь срок на счёт было положено 475 тысяч рублей, а выведено 30 тысяч на срочные нужды. Далее нужно просто использовать функцию XIRR (ЧИСТВНДОХ по-русски), которая даст значение среднегодовой доходности. Первым параметром необходимо указать суммы, а вторым — даты. Посмотрим, какой у нас получится результат:
Оказалось, что доходность составила 9,35% годовых. На самом деле наш инвестор не был слишком успешен, ведь за указанный период его доходность была чуть больше средней официальной инфляции.
Теперь вы сможете посчитать доходность своих инвестиций даже в случае, если они делались в разное время, разными суммами, а также иногда совершались выводы денег. Полезно иногда делать такой расчёт, чтобы сравнивать свои результаты с инвестиционным планом, да и просто убеждаться, что был выбран правильный подход к инвестированию.
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.
Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:
При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.
Теперь таблица для поиска выглядит следующим образом:
Функция ВПР в Excel – Добавление вспомогательного столбца
Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:
Аргументы функции
Дополнительная информация
Интерактивный отчет для анализа прибыли и убытков в Excel
Пример 3. В таблице хранятся данные о доходах и расходах мелкого предприятия по номерам месяцев. Создать форму для быстрого расчета абсолютного значения разницы доходов и расходов по номеру месяца, позволяющая определять, был ли закончен отчетный период с прибылью или убытком.
Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):
В ячейку F2 запишем следующую формулу:
Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.
В ячейке G2 запишем формулу:
Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.
Особенности использования функции ГПР в Excel
Функция имеет следующую синтаксическую запись:
=ГПР( искомое_значение;таблица;номер_строки; [интервальный_просмотр])
Найти значения продаж для выбранного региона
Еще раз напомню, что функция HLOOKUP (ГПР) ищет значение только в верхней строке таблицы. В этом примере мы найдём итоги продаж для выбранного региона. Нам важно получить правильное значение, поэтому используем такие настройки:
Формула в ячейке C7 такая:
Если название региона в первой строке таблицы не найдено, результатом функции HLOOKUP (ГПР) будет #N/A (#Н/Д).
Найти показатель для выбранной даты
Обычно при использовании функции HLOOKUP (ГПР) требуется точное совпадение, но иногда приблизительное совпадение подходит больше. Например, если показатели меняются в начале каждого квартала, а в качестве заголовков столбцов используются первые дни этих кварталов (см. рисунок ниже). В таком случае, с помощью функции HLOOKUP (ГПР) и приблизительного соответствия, Вы найдёте показатель, который актуален для заданной даты. В этом примере:
Формула в ячейке D5 такая:
Если дата не найдена в первой строке таблицы, функция HLOOKUP (ГПР) найдет ближайшее наибольшее значение, которое меньше аргумента lookup_value (искомое_значение). В данном примере искомое значение – 15 марта. Его в строке с датами нет, поэтому формула возьмет значение 1 января и вернет 0,25.
