Как в офисе

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

  • ключ — обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон — обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца — обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
  • интервальный_просмотр — необязательный аргумент. Логическое значение указывающее тип просмотра:
    • ЛОЖЬ — функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
    • ИСТИНА — функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

    На изображении приведено 3 таблицы. Первая и вторая таблицы располагают исходными данными. Третья таблица собрана из первых двух.В первой таблице приведены категории товара и расположение каждой категории.Во второй категории имеется список всех товаров с указанием цен.Третья таблица содержать часть товаров для которых необходимо определить цену и расположение.

    Различие интервальных просмотров ВПР

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

    Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

    В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж № 2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

    Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

    Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы — * (звездочка — любое количество любых символов) и ? (один любой символ). Например, для искомого значения »*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

    Также данная функция может искать значения в массивах — =ВПР(1;<2;"Два":1;"Один«>;2;ЛОЖЬ) — результат выполнения строка «Два».

    Создание раскрывающегося списка

    Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

    1. Вручную через «точку-с-запятой» в поле «Источник».
    2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
    3. Назначить имя для диапазона значений и в поле источник вписать это имя.

    Любой из вариантов даст такой результат.

    ВПР: Поиск значений

    В работе с несколькими таблицами пригодится функция ВПР. Например, у вас есть два списка — в одном номера товаров, его заказчики, цена и количество, а в другом — те же номера товаров и их описание. Такие таблицы могут быть очень большими, а ID предметов обычно стоят не по порядку и повторяются. Чтобы узнать, какой товар скрывается под определенным номером, используйте функцию ВПР.

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

    Аргументами ВПР являются искомое значение, таблица, номер столбца, из которого надо вытащить информацию, и интервальный просмотр. В последнем аргументе необходимо задать ЛОЖЬ, если вы ищете точное значение, и ИСТИНА для поиска приблизительного.

    ВПР считается сложной функцией. Она может обработать большое количество данных и в считанные секунды найдет нужное вам значение.

    Обзор математических функций в Excel (Часть 1). 10 самых полезных математических функций Переход в меню Файл в Эксель Переход в Параметры программы Excel Запуск восстановления Microsoft Office через Панель управления Windows 10 Включение строки формул в Параметрах программы Эксель как вставить формулу в таблицу excel Переход в раздел Программы и компоненты в Панели управления Windows 10 как вставить формулу в эксель Обзор математических функций в Excel (Часть 1). 10 самых полезных математических функций

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

    Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

    Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

    • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, “C3”, “R3C3” или “D8:D9”.
    • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
      • ИСТИНА – стиль A1. Является значением по умолчанию;
      • ЛОЖЬ – стиль R1C1.

      =ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
      =ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
      =ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
      =ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
      Вложение функции ДВССЫЛ со ссылкой на диапазон:

      Список с автозаполнением

      Способ 1

      Теперь при добавлении новых элементов они также появятся в готовом выпадающем перечне.

      Способ 2

        Выделить имеющийся перечень наименований и присвоить ему имя в левой строке формул.

      Способ 3

        Выделить содержание, во вкладке «Главная» выбрать «Форматировать как таблицу». В появившемся окне обязательно поставить галочку рядом с пунктом «Таблица с заголовками».

      Если в нижние строки вписать новые значения, они тут же отобразятся в готовом перечне.

      Математические функции

      Чтобы определить модуль числа, используйте функцию ABS.

      Функция ABS

      СЛЧИС и СЛУЧМЕЖДУ

      И функция СЛЧИС (RAND), и функция СЛУЧМЕЖДУ (RANDBEWTEEN) могут генерировать случайные числа на лету. СЛЧИС (RAND) создает длинные десятичные числа от нуля до 1. СЛУЧМЕЖДУ (RANDBETWEEN) генерирует случайные целые числа между двумя заданными числами.

      Функции СЛЧИС и СЛУЧМЕЖДУ

      =СЛЧИС() // случайное число от 0 до 1

      =СЛУЧМЕЖДУ (1;100) // Случайное число от 1 до 100

      ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ и ЦЕЛОЕ

      Чтобы округлить значения в большую или меньшую сторону, используйте функцию ОКРУГЛ (ROUND). Для принудительного округления до заданного количества цифр в большую сторону используйте ОКРУГЛВВЕРХ (ROUNDUP). Для принудительного округления в меньшую сторону используйте ОКРУГЛВНИЗ (ROUNDDOWN). Чтобы полностью удалить десятичную часть числа, используйте функцию ЦЕЛОЕ (INT).

      Базовые функции округления

      =ОКРУГЛ (12,325;1) // возвращает 12,3

      =ОКРУГЛВВЕРХ (12,325;1) // возвращает 12,4

      =ОКРУГЛВНИЗ (12,325;1) // возвращает 12,3

      =ЦЕЛОЕ (12,325) // возвращает 12

      ОКРУГЛТ, ОКРВВЕРХ и ОКРВНИЗ

      Чтобы округлить значения до ближайшего кратного, используйте функцию ОКРУГЛТ (MROUND). Функция ОКРВНИЗ (FLOOR) и функция ОКРВВЕРХ (CEILING) также округляются до заданного кратного. ОКРВНИЗ (FLOOR) заставляет округлять вниз, а ОКРВВЕРХ (CEILING) заставляет округлять вверх.

      Функции ОКРУГЛТ, ОКРВВЕРХ, ОКРВНИЗ

      =ОКРУГЛТ (12,325;0,25) // возвращает 12,25

      =ОКРВВЕРХ (12,325;0,25) // возвращает 12,5

      =ОКРВНИЗ (12,325;0,25) // возвращает 12,25

      Функция ОСТАТ (MOD) возвращает остаток после деления. Это звучит скучно, но ОСТАТ (MOD) появляется во всех видах формул, особенно в формулах, которые должны делать что-то «каждый раз». В приведенном ниже примере вы увидите, как ОСТАТ (MOD) возвращает ноль каждое третье число, когда делитель равен 3:

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

      Функция СУММПРОИЗВ (SUMPRODUCT) — это мощный и универсальный инструмент для работы со всеми видами данных. Вы можете использовать СУММПРОИЗВ (SUMPRODUCT) для простого подсчета и суммирования на основе критериев, и вы можете использовать его изящными способами, которые просто не работают с СЧЁТЕСЛИМН (COUNTIFS) и СУММЕСЛИМН (SUMIFS). В приведенном ниже примере мы используем СУММПРОИЗВ (SUMPRODUCT) для подсчета и суммирования заказов в марте.

      Функция СУММПРОИЗВ

      =СУММПРОИЗВ (—(МЕСЯЦ (B5:B12)=3)) // считает количество заказов в марте

      =СУММПРОИЗВ (—(МЕСЯЦ (B5:B12)=3);C5:C12) // считает сумму заказов в марте

      Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является «агрегатной функцией», которая может выполнять ряд операций с набором данных. В общем, ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) может выполнять 11 операций, включая СУММ (SUM), СРЗНАЧ (AVERAGE), СЧЁТ (COUNT), МАКС (MAX), МИН (MIN) и т.д.

      Ключевой особенностью ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) является то, что он будет игнорировать строки, которые были «отфильтрованы» из таблицы Excel и строки, которые были скрыты вручную. В приведенном ниже примере ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) используется для подсчета и суммирования только 7 видимых строк в таблице:

      Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

      =ПРОМЕЖУТОЧНЫЕ.ИТОГИ (3;B5:B13) // возвращает 7 =ПРОМЕЖУТОЧНЫЕ.ИТОГИ (9;F5:F13) // возвращает 2 390

      Как и ПРОМЕЖУТОЧНЫе.ИТОГИ (SUBTOTAL), функция АГРЕГАТ (AGGREGATE) может также выполнять ряд агрегатных операций над набором данных и, при необходимости, игнорировать скрытые строки. Ключевые различия заключаются в том, что АГРЕГАТ (AGGREGATE) может выполнять больше операций (всего 19), а также может игнорировать ошибки.

      В приведенном ниже примере АГРЕГАТ (AGGREGATE) используется для выполнения операций МИН (MIN), МАКС (MAX), НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL), игнорируя ошибки. Обычно ошибка в ячейке B9 не позволяет этим функциям возвращать результат.

      Функция АГРЕГАТ

      =АГРЕГАТ (4;6;B5:B13) // MАКС, пропускает ошибки, возвращает 100

      =АГРЕГАТ (5;6;B5:B13) // MИН, пропускает ошибки, возвращает 9

      • ref_text (ссылка_на_текст) – текстовая строка, которая содержит в себе ссылку на ячейку или именованный диапазон;
      • [a1] – логическое значение, которое определяет тип ссылки используемой в аргументе ref_text (ссылка_на_текст). Значения аргумента могут быть TRUE (ссылка указана в формате “А1”) или FALSE (ссылка указана в формате “R1C1”). Если не указать этот аргумент, то Excel автоматически определит его значение как TRUE.
      • Функция INDIRECT (ДВССЫЛ) это волатильная функция (используйте с осторожностью);
      • Она пересчитывает значения каждый раз, когда вы открываете Excel файл, и каждый раз когда вычисление запускается на рабочем листе Excel;
      • Так как волатильные функции постоянно обновляются и производят вычисления, это, в свою очередь, замедляет работу вашего Excel файла.
      • Аргумент текстовой ссылки может выглядеть как:
        – ссылка на ячейку, которая содержит ссылку на ячейку в формате “A1” или “R1C1”.
        – ссылка на ячейку в двойных кавычках.
        – именованный диапазон, возвращающий ссылку

      Функция ЛЕВСИМВ в Excel

      Итак, начнём знакомство с функцией ЛЕВСИМВ с ее синтаксиса, замечу, он довольно прост, но всё же требует описания:

      = ЛЕВСИМВ(текст; количество_нужных_знаков), где:

      Function LEVS v Excel 2 Функции ЛЕВСИМВ и ПРАВСИМВ в Excel

      • Текст – это текст, текстовая строка или ссылка на текст в ячейке, которая содержит в себе знаки нужные к извлечению;
      • Количество нужных знаков – это то количество знаков, которое будет извлекать функция ЛЕВСИМВ.

      Стоит также помнить некоторые нюансы использования функции ЛЕВСИМВ, которые помогут вам не совершать ненужные ошибки:

      • В случае когда «Количество нужных знаков» больше, нежели длина текста, тогда функция ЛЕВСИМВ вернет весь текст;
      • Когда «Количество нужных знаков» не указано, то по умолчанию условия система воспринимает как равно 1;
      • Обязательно «Количество нужных знаков» должно ровняться или быть больше нуля.

      Простой пример:

      Function LEVS v Excel 4 Функции ЛЕВСИМВ и ПРАВСИМВ в Excel

      Теперь предлагаю вашему вниманию пример простого использования нашей функции без дополнительных комбинаций:

      Сложный пример:

      Function LEVS v Excel 5 Функции ЛЕВСИМВ и ПРАВСИМВ в Excel

      В случаях, когда количество знаков, которые должна извлечь функция ЛЕВСИМВ неизвестно, можно произвести определение нужного количества знаков по некому признаку, например «/», и совместно с функцией НАЙТИ произвести отбор:

      Как в эксель сделать выпадающий список с другого листа?

      Я таблицу уже создал . Теперь создадим новый лист в документе и назовем его «база». Лист потом этот можно скрыть, чтобы случайно не сломать документ. В произвольном месте этого нового листа сохраним наш будущий список, в этом примере его можно дополнить пустыми строками и время от времени добавлять в них новые позиции.

      Выделяем нужную область и в верхней левом верхнем углу присваиваем ей имя «Список_Кондит». У нас есть теперь первый ИМЕНОВАННЫЙ диапазон в нашей книге эксель. Чтобы потом диапазонами управлять, нужно открыть вкладку «Формулы» и найти там «Диспетчер имен»:

      Следующий шаг — идем обратно в наш лист где создана основная таблица. Выделяем в графе нужные строки. Идем во вкладку «Данные»- «Проверка данных». В строке «тип данных» выбираем «Список»….

      А в строке «Источник» копируем адрес нашего диапазона из «диспетчера имен»…

      Теперь из списка можно выбирать готовые значения. Если список длинный, то прокручиваем вниз. Но нельзя будет менять шрифты, цвета — то есть форматировать содержимое, искать по букве наименование.

      Функция ЛИСТЫ для подсчета количества листов в рабочей книге

      Рассмотрим теперь пример работы функции ЛИСТЫ. Довольно часто бывает так, что в рабочей книге Excel расположено слишком много листов. Выяснить их точное количество визуально не представляется возможным, именно с этой целью и создана функция ЛИСТЫ.

      В данной функции всего 1 аргумент – «Ссылка» да и то необязательный для заполнения. Если его не заполнять тогда функция возвращает общее количество листов, созданных в текущей рабочей книге файла Excel. При необходимости можно заполнить аргумент. Для этого в нем необходимо указать ссылку на рабочую книгу, в которой необходимо подсчитать общее количество листов, созданных в ней.

      Пример2. Предположим у нас имеется фирма по производству мягкой мебели, и у нее есть множество документов, которые содержатся в рабочей книге Excel. Нам необходимо вычислить точное число этих документов, так как каждый из них имеет свое название, то для того чтобы визуально вычислить их количество потребуется время.

      На рисунке ниже показано примерное количество листов:

      Чтобы организовать подсчет всех листов, необходимо воспользоваться функцией ЛИСТЫ. Просто ставим знак равенства «=» и вписываем функцию, не заполняя ее аргументов в скобках. Вызов данной функции показан ниже на рисунке:

      В результате получим следующее значение: 12 листов.

      Таким образом мы узнали, что в нашей фирме имеется 12 документов, содержащихся в рабочей книге Excel. Этот простой пример наглядно иллюстрирует работу функции ЛИСТЫ. Данная функция может стать полезной для руководителей, офисных сотрудников, менеджеров по продажам.

      Разбор функции ДВССЫЛ (INDIRECT) на примерах

      На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

      Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.

      «Ну ОК», — скажете вы. «И что тут полезного?».

      Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.

      Пример 1. Транспонирование

      пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

      Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN) .

      Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 и т.д.

      Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE) , то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

      Пример 2. Суммирование по интервалу

      Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET) . Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM) :

      Пример 3. Выпадающий список по умной таблице

      Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

      Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

      Пример 4. Несбиваемые ссылки

      Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:

      Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.

      Пример 5. Сбор данных с нескольких листов

      Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

      Допустим, что форма, размеры, положение и последовательность товаров и месяцев во всех таблицах одинаковые — различаются только числа.

      Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:

      Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.

      Подводные камни

      При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

      • Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
      • С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический — без проблем.
      • ДВССЫЛ является волатильной (volatile) или «летучей» функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.

      Примеры использования функции ДВССЫЛ

      Пример 1. Ссылка на ячейку

      Начнем с простой задачи, который мы уже частично разобрали.
      Введем произвольное значение в ячейку A1, теперь чтобы сделать ссылку на ячейку введем формулу =ДВССЫЛ(«A1»), например, в ячейку A2:

      Пример 2. Ссылка на другой лист

      Немного усложним задачу, и применим формулу ДВССЫЛ для ссылки на другой лист.
      Перейдем на любой другой лист книги и вводим формулу =ДВССЫЛ(«Пример_1!A1»), где лист Пример_1 — лист из первого примера:

      Пример 3. Функции

      Рассмотрим примеры с одновременным применением функции ДВССЫЛ и других функций.

      Функция СУММ

      Предположим, что у нас имеется таблицами с помесячными данными о продажах фруктов за 12 месяцев, и мы хотим просуммировать продажи накопленным итогом за переменное количество месяцев:


      Функция СУММ с прямой ссылкой на диапазон решает эту задачу, например, можно применить формулу =СУММ(B2:B5) для подсчета продаж апельсинов.
      Однако тогда при изменении периода нам придется менять и диапазон в исходной формуле.
      Обойдем эту проблему записав диапазон в текстовом виде с использованием ссылок на другие ячейки — запишем формулу =СУММ(ДВССЫЛ(B15&2&»:»&B15&(1+$A16))), где ячейка A16 отвечает за номер периода:


      Расписывая по шагам данную формулу, мы в конце получим формулу =СУММ(B2:B5), что нам и требовалось.

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

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


      Записываем в оценку кандидатов формулу =ДВССЫЛ(«G»&ПОИСКПОЗ(B2;$F$1:$F$6;1)), где с помощью функции ПОИСКПОЗ находим относительное положение оценки кандидата в критерии оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для каждого кандидата.

      Обратите внимание, что функция ДВССЫЛ не работает, если ссылка указывает на ячейку или диапазон в закрытой книге.
      Подробно ознакомиться со всеми разобранными примерами — скачать пример.

      Трюк №25. Как в Excel при проверке данных заставить Excel использовать список на другом рабочем листе

      Один из параметров, доступных при проверке данных, это параметр Список (List), то есть удобный раскрывающийся список, из которого пользователи могут выбрать определенные элементы. Но есть один недостаток — если вы попытаетесь сослаться на список, находящийся на другом рабочем листе, то получите сообщение, что это невозможно. К счастью, при помощи очередного трюка невозможное можно сделать возможным.

      Заставить Excel при проверке данных ссылаться на список на другом рабочем листе можно двумя способами — при помощи именованных диапазонов и функции ДВССЫЛ (INDIRECT).

      Способ 1. Именованные диапазоны

      Вероятно, самый простой и быстрый способ выполнить эту задачу — присвоить имя диапазону, где размещается список. Для этого упражнения мы предполагаем, что диапазону присвоено имя MyRange. Выделите ячейку, в которой должен будет появиться этот раскрывающийся список, и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =MyRange. Щелкните на кнопке ОК. Теперь список (который находится на другом рабочем листе) можно использовать как список проверки.

      Способ 2. Функция ДВССЫЛ (INDIRECT)

      Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, который представляет собой адрес ячейки. Ячейку, содержащую функцию ДВССЫЛ (INDIRECT), можно использовать как ссылочную ячейку и применять эту возможность для связи с рабочим листом, где находится нужный список.

      Предположим, список находится на листе Sheet1 в диапазоне $А$1:$А$10 . Щелкните любую ячейку на другом рабочем листе, где должен будет появиться этот список проверки. Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующую функцию: =INDIRECT(«Sheet1!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet1!$А$1:$А$10») . Убедитесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl окажется в вашем раскрывающемся списке проверки.

      Если имя рабочего листа, на котором расположен список, содержит пробелы, функцию ДВССЫЛ (INDIRECT) нужно записать так: =INDIRECT(«‘Sheet 1’!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet 1!$А$1:$А$10») . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком. Апострофы ограничивают название листа.

      Преимущества и недостатки обоих методов

      У именованных диапазонов и функции ДВССЫЛ (INDIRECT) при использовании их для связи со списком на другом рабочем листе есть преимущества и недостатки.

      Преимущество использования именованного диапазона в данном сценарии заключается в том, что изменение названия листа не повлияет на список проверки. nЭто подчеркивает недостаток функции ДВССЫЛ (INDIRECT) — а именно, любое изменение названия листа не будет автоматически отражаться в функции ДВССЫЛ (INDIRECT), поэтому придется вручную изменить функцию, указав новое название листа.

      Преимущество функции ДВССЫЛ (INDIRECT): если из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованных диапазонов — если удалить ячейки или строки из именованного диапазона, эти изменения не повлияют на список проверки.

      Другие примеры использования оператора ЕСЛИ

      Функцию ЕСЛИ можно использовать для обхода встроенных ошибок деления на ноль, и еще в ряде случаев

      Функцию ЕСЛИ можно использовать для обхода встроенных ошибок деления на ноль, и еще в ряде случаев

      Очень часто в Экселе возникает такая ошибка, как «ДЕЛ/0», т.е. деление на 0. Как правило, она появляется в техслучаях, когда копируется формула «A/B», а число B в некоторых ячейках равняется нулю. Этого можно избежать, если использовать оператор ЕСЛИ. Для этого необходимо написать так: =ЕСЛИ(B1=0; 0; A1/B1). Получается, что если в ячейке B1 будет ноль, то Excel сразу же выдаст ноль, в противном случае программа поделит A1 на B1 и выдаст результат.

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

      • до 1000 — 0%;
      • от 1001 до 3000 — 3%;
      • от 3001 до 5000 — 5%;
      • свыше 5001 — 7%.

      К примеру, в Excel есть условная база данных клиентов и информация о том, сколько они потратили на покупки. Задача состоит в том, чтобы рассчитать для них скидку. Для этого можно написать так: =ЕСЛИ(A1>=5001; B1*0,93; ЕСЛИ(А1>=3001; B1*0,95;..). Суть ясна: проверяется общая сумма покупок, и когда она, к примеру, больше 5001 рублей, то умножается на 93% стоимости товара (ячейка B1*0,93), когда больше 3001 рублей, то умножается на 95% стоимости товара и т.д. Такую формулу легко можно использовать и на практике: уровень объема продаж и уровень скидок устанавливается на ваше усмотрение.

      Таким образом, применять функцию ЕСЛИ можно практически в любой ситуации, функциональность Microsoft Excel это позволяет. Главное — правильно составить формулу, чтобы результат не оказался ошибочным.

Ссылка на основную публикацию