Рубрики
Формулы

Почему ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A), как исправить?

ВПР (VLOOKUP), пожалуй, одна из самых ходовых функций в MicrosoftExcel. Эта статья предназначена для тех, кто знает, как ей пользоваться. Если это не про вас, читайте описание функции здесь. Но сегодня речь пойдет о другом – у всех она рано или поздно начинает возвращать ошибку #Н/Д (#N/A). Почему она возникает, и как с ней бороться.

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

  1. Не могу найти значение
    Первый и самых ходовой вариант – вы ищете значение, которого нет в диапазоне. Решение проблемы предельно простое:

(1) добавить нужную строку в диапазон, в котором происходит поиск,

(2) изменить последний параметр на ИСТИНУ (или 1), если вас устраивает неточный поиск – что редко бывает правдой, но как вариант, я указать был обязан, или

(3) если вас устраивает отсутствие значения, но не устраивает ошибка, встройте функцию ВПР (VLOOKUP) в функцию ЕСЛИОШИБКА (IFERROR), подробнее про нее здесь.

Простая проверка, копируете искомое значение, выделяете диапазон, где производится поиск, нажимаете Найти (Ctrl+ F), вставляете искомое значение и ищете. Если программа не находит результат, проблема описана выше. Если находит, все становится немного интереснее – читайте дальше.

  1. Разные форматы
    Excel очень трепетно относится к тому, как хранятся данные в ячейке. Если число хранится в формате текста, программа сообщит вам об этом двумя способами: нарисует зеленый треугольник в левом верхнем углу ячейки и, если выравнивание не установлено, поместит ваше горе-число в левую часть ячейки (обычно числа выровнены по правому краю). Кроме того число 100, хранящееся как число, и число 100, хранящееся как текст, — это два разных значения. Поэтому функция ВПР (VLOOKUP) не будет находит число 100 в диапазоне, где есть текст 100. Все варианты решения проблемы сводятся к тому, что надо привести все значения к одному формату (либо числовому, либо текстовому). Как это сделать:

(1) перевести текст в число можно по одной ячейке (скажем, она одна не в том формате), изменив формат, а потом войдя в ее редактирование (F2) и нажав Enter,

(2) другой вариант сделать то же самое для большего диапазона – воспользоваться функционалом Найти и заменить. Обычно число представлено в виде текста, если таблица была выгружена из внешней программы, скажем SAPили 1С. Обычно поиск производится по номенклатурным номерам. Если так, то они часто начинаются с одной и той же цифры, с единицы, двойки и т.п. Если так, то вам повезло. Выделяйте диапазон, меняйте формат на числовой и жмите Найти и заменить (Ctrl + H). Вбивайте найти 1, заменить на 1 (если все номера начинаются с 1), заменить все. Через некоторое время (если таблица большая) все значения будут приведены к одному формату.

(3) с помощью функции ЗНАЧЕН (VALUE) или ТЕКСТ (TEXT), причем их можно встраивать непосредственно в функцию ВПР (VLOOKUP), чтобы не создавать отдельную колонку.

В очень редких случаях бывает, что и это не помогло. Тогда возможен еще один вариант, из-за которого функция может возвращать именно эту ошибку. Этот вариант прост до безумия, тем не менее именно он может поставить в ступор. Именно с таким вариантом я столкнулся сегодня, что и навело меня на мысль написать эту статью.

Симптомы: поиск находит одно или несколько (этот вариант даже интереснее) совпадений, форматы одинаковые (в моем случае поиск производился по маске WBSэлемента – не число, выглядит приблизительно так X.XX.XXX.XX.XXX.XXXXX). При поиске найденному варианту соответствовало нужное мне значение, но формула упорно выдавала ошибку «нет данных».

  1. Искомое значение равно ошибке #Н/Д (#N/A)
    Позвольте напомнить вам, как работает функция ВПР (VLOOKUP) – она ищет значение в первой колонке указанного диапазона, при первом совпадении отсчитывает нужное количество колонок и берет из нужной колонки значение. Ключевая часть этой теории – «первое совпадение». В моем случае нужный мне элемент затрат попадался несколько раз. Во всех случаях, кроме первого, ему соответствовало правильное искомое значение. А в первом – ошибка #Н/Д (#N/A). Ее функция и подтянула.

Вариант решения – убрать строку с ошибкой или переставить ее в конец диапазона.

Для вашего удобства я суммировал эти три ошибки в одну таблицу, на которую вы можете посмотреть как на картинку

или скачать по этой ссылке

Если вы хотите не просто найти первое совпадение, но и просуммировать или посчитать все совпадения, вам помогут функции СУММЕСЛИМН (SUMIFS) и СЧЕТЕСЛИМН (COUNTIFS). Хотите еще лучше разбираться в функциях Excel – купите себе эту книгу.

Рубрики
Формулы

Функции MIN (МИН), MAX (МАКС), SMALL (НАИМЕНЬШИЙ), LARGE (НАИБОЛЬШИЙ)

В данной статье описаны функции MIN (МИН), MAX (МАКС), SMALL (НАИМЕНЬШИЙ), LARGE (НАИБОЛЬШИЙ). Приведен синтаксис и примеры использования.

Как легко догадаться, данные функции используются для вычисления наибольших и наименьших величин.

Как же работают функции MIN (МИН), MAX (МАКС), SMALL (НАИМЕНЬШИЙ), LARGE (НАИБОЛЬШИЙ)?

Функция MIN (МИН) определяет наименьшее значение, встречающиеся в выбранном диапазоне. MAX (МАКС), соответственно, наибольшее. Синтаксис предельно прост.

=MIN(диапазон1;[диапазон2]…)

=MIN(диапазон1;[диапазон2]…)

Диапазон может быть указан каким угодно образом: строки, колонки, диапазоны, — ссылки могут быть абсолютными и относительными. Кроме того, можно указывать конкретные числа вместо диапазонов.

Например, =МИН(-2;1;12.24) вернет значение -2. =МАКС(-2;1;12.24) вернет 12.24.

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

Итак, функция SMALL (НАИМЕНЬШИЙ) возвращает определенное по счету наименьшее значение.

Например, вам необходимо определить второе наименьшее значение из диапазона — функция SMALL (НАИМЕНЬШИЙ) вам в этом поможет. Синтаксис следующий:

=SMALL(массив;k)

массив — диапазон, в котором программа будет производить поиск,
k — номер наименьшего по счету значения. В предыдущем примере — это 2.

Предположим, вам надо найти второе наименьшее значение, встречающееся в колонках A и B. Функция будет выглядеть так: =SMALL(A:B) или =SMALL($A:$B) в зависимости от того, как вы хотите, чтобы она вела себя при копировании.

Функция LARGE (НАИБОЛЬШИЙ), соответственно, возвращает определенное по счету наибольшее значение в диапазоне. Синтаксис такой же как и у функции SMALL (НАИМЕНЬШИЙ).

=LARGE(массив;k)

массив — диапазон, в котором программа будет производить поиск,
k — номер наибольшего по счету значения.

Рубрики
Формулы

Функция ROUND (ОКРУГЛ)

Когда я впервые готовил финансовую отчетность компании я столкнулся с одним идиотским требованием. Почему-то при расчетах итогов необходимо игнорировать точные суммы. Например, вы готовите отчетность в тысячах рублей, а за этими тысячами, которые попадают на баланс стоят конкретные цифры в рублях. Суммирование чисел 1,400 и 1,300 с округлением до тысяч равно не трем тысячам (2,700 до тысяч), а двум тысячам (округленные 1,000 плюс 1,000).

Чтобы избежать кучи ручной работы по округлению можно воспользоваться функцией ROUND (ОКРУГЛ).

Синтаксис: =ROUND(number;num_digits)

number — число, которое необходимо округлить,

num_digits — число знаков после запятой, до которых необходимо округлить число.

Обратите внимание, что число знаков после запятой может быть как положительным, так и отрицательным. Если число отрицательное, то округление будет происходить до того разряда целых, которое указано как отрицательное число. Например, округление до -1 знака после запятой — это округление до десятков. До -3 знака — это округление до тысяч. Вы можете это увидеть в следующем примере.

У вас остались вопросы или вам хочется высказаться по этой или другим темам — пожалуйста, оставьте комментарии или задайте свой вопрос здесь.

Рубрики
Формулы

Функции COUNTIFS (СЧЕТЕСЛИМН) и COUNTBLANK (СЧИТАТЬПУСТОТЫ)

В данной статье описана функции COUNTIFS (СЧЕТЕСЛИМН) и COUNTBLANK (СЧИТАТЬПУСТОТЫ), их применение, синтаксис, примеры.

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

Функция COUNTIFS (СЧЕТЕСЛИМН) является следующим шагом эволюции для COUNTIF (СЧЕТЕСЛИ) и легко справляется с любой задачей, с которой справлялась старая формула. Поэтому с вашего позволения мы ее проигнорируем.

Синтаксис: =countifs(criteria_range1;criteria1;[criteria_range2];[criteria2]…)

criteria_range1 — диапазон 1, в котором проверять выполнение условия 1

criteria1 — условие 1

criteria_range2, 3, … — диапазон 2, 3 и т.д., в котором проверять условия 2, 3 и т.д. соответственно

criteria2, 3, … — условия 2, 3

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

Еще важно: все диапазоны должны быть одного размера. Удобно использовать столбцы (или строки) вместо конкретных диапазонов. Об этом читайте здесь.

Пример:

Есть огромная база организаций. Посчитать количество организаций, удовлетворяющих нескольким критериям, например, (1) количество персонала более 10 и обороты больше 1,500.

Функция будет работать следующим образом.

(1) Проверяем первую ячейку первого диапазона — B2. Условие выполнено (больше 10). Значит проверяем первую ячейку второго диапазона. Условие не выполнено (1000<1500). Счет — 0.

(2) Проверяем вторую ячейку первого диапазона — B3. Условие выполнено (200>10). Значит проверяем вторую ячейку второго диапазона. Условие выполнено (2500>1500). Счет 1 (добавили 1 к предыдущему условию).

(3) Проверяем третью ячейку первого диапазона — B4. Условие не выполнено (3<10). Второй диапазон можно не проверять.

(4) Весь диапазон проверен. Вернуть 1 — один случай, где выполняются все условия.

Функция COUNTBLANKS (СЧИТАТЬПУСТОТЫ) является частным случаем описанной выше функции. Она, как можно догадаться из названия, считает количество пустых ячеек в выбранном диапазоне. Синтаксис ее предельно прост:

=COUNTBLANKS(диапазон)

Ее можно также записать следующим образом, =COUNTIFS(диапазон;»»)

У вас остались вопросы или вам хочется высказаться по этой или другим темам — пожалуйста, оставьте комментарии или задайте свой вопрос здесь.

Рубрики
Формулы

Функции SUM (СУММ) и COUNT (СЧЕТ)

В Excel есть две функции, которыми я, пожалуй, чаще всего пользуюсь в работе: SUM (СУММ) и COUNT (СЧЕТ). Первая возвращает сумму членов, указанных в скобках, а вторая — их количество.

У них абсолютно одинаковый синтаксис и логика применения.

Синтаксис: =SUM(number1,[number2],[number3]…)

=COUNT(number1,[number2],[number3]…)

number1, 2, 3… — это ячейки, диапазоны, числа, другие функции (возвращающие числа) или все вместе вперемешку.

Пример применения

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

Если вы хотите просуммировать несколько диапазонов, между которыми есть промежуточные итоги, использование функции SUM (СУММ) может быть не очень подходящим. Советую посмотреть эту статью.

У вас остались вопросы или вам хочется высказаться по этой или другим темам — пожалуйста, оставьте комментарии или задайте свой вопрос здесь.

Рубрики
Формулы

Функция AVERAGE (СРЗНАЧ)

В Microsoft Excel встроено много базовых функций, которые помогают упростить частые рутинные операции. Например, расчет среднего арифметического реализован с помощью готовой функции AVERAGE (СРЗНАЧ).

Синтаксис: =AVERAGE(number1,[number2],[number3]…)

number1, 2, 3 и т.д. значения, из которых рассчитывается средняя арифметическая. Значения можно задавать как по одному, так и диапазонами.

Пример

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

Математически средняя арифметическая рассчитывается как сумма членов, деленная на их количество. Поэтому данную функцию можно (теоретически — на практике этого делать нет никакой необходимости) записать с помощью функций SUM (СУММ) и COUNT (СЧЕТ). Так, например, формула в ячейке B8 в примере могла бы выглядеть как =SUM(B2:B6)/COUNT(B2:B6). При этом она будет возвращать тот же результат — 182.

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

Рубрики
Формулы

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

Часто работаете со средневзвешенными величинами? Часто рассчитываете ожидаемые значения (expected values)? Значит часто считаете сумму произведений. Если вы еще не пользуетесь функцией SUMPRODUCT (СУММПРОИЗВ), то она вам понравится!

Стыдно признаться, но еще совсем недавно, если мне надо было посчитать сумму произведений, я построчно считал произведения, а потом их суммировал. А потом я нашел функцию SUMPRODUCT (СУММПРОИЗВ). Она суммирует произведения двух диапазонов.

Синтаксис: SUMPRODUCT(array1;array2;[array3]…)

array1, 2, 3… — диапазоны данных, значения из которых будут перемножаться поячеечно. Они обязательно должны быть одного размера.

Пример: расчет средневзвешенной

В данном примере рассчитывается средняя сумма расходов на рекламу на категорию клиентов, взвешенная на обороты по категориям.

Как рассчитать средневзвешенную? Математически для этого построчно умножаются обороты на расходы на рекламу, суммируем полученные произведения и делим полученный результат на сумму оборотов.

Как работает формула SUMPRODUCT (СУММПРОИЗВ)

Рассмотрим на примере вверху.

Функция SUMPRODUCT(C2:C3;D2:D3) делает следующее:

(1) взять первое значение из диапазона C2:C3 (то есть 1,000,000.00), умножить его на первое значение из диапазона D2:D3 (то есть 100,000.00), произведение запомнить (100,000,000,000.00)

(2) взять второе значение из диапазона C2:C3 (то есть 2,500,000.00), умножить его на второе значение из диапазона D2:D3 (то есть 25,000.00), произведение сложить с сумой всех предыдущих произведений (в данном случае с 100,000,000,000.00), получается 162,500,000,000.00.

(3) если бы диапазоны были длиннее, то второй шаг бы повторялся до конца диапазонов.

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

Рубрики
Формулы

Функции YEAR (ГОД), MONTH (МЕСЯЦ) и DAY (ДЕНЬ)

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

Для этого в Microsoft Excel встроены функции YEAR (ГОД), MONTH (МЕСЯЦ) и DAY (ДЕНЬ).

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

=year(serial_number)
=month(serial_number)
=day(serial_number)

где serial_number — это дата, из которой берется год, месяц, день.

Пример:

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Функция TODAY (СЕГОДНЯ)

Функция TODAY (СЕГОДНЯ) возвращает сегодняшнюю дату, которая установлена в Windows. Позвольте предложить вам несколько вариантов ее использования.

Во-превых, синтаксис: =TODAY()

В скобках ничего указывать не надо, но скобки ставить обязательно.

Использование для условного форматирования

Предположим, вы ведете некоторый список дел по супер-сложному проекту и хотите визуально отслеживать проблемные области, там где срок уже прошел. Почему бы не автоматизировать этот процесс с помощью функциональности условного форматирования (Conditional formatting)?

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

Аналогичным образом функция TODAY (СЕГОДНЯ) может быть использована в обычных формулах, например SUMIFS, IF и т.п.

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Функция DATE (ДАТА)

Иногда бывает необходимо сравнить какую-то дату на странице со вполне определенной датой. Например, просуммировать все операции, которые произошли до 31 декабря 2011 года. Нам в этом поможет формула SUMIFS (СУММЕСЛИМН). Проблема с ней в том, что дату для сравнения (31 декабря 2011) надо указывать в числовом формате, иначе Excel воспринимает ее как текст.

Любая дата в Excel, если перевести ее в общий формат будет выглядеть как число. Например, 31 декабря 2011 года — это 40908. Проверять дату каждый раз таким образом действительно неудобно, поэтому и была придумана функция DATE (ДАТА).

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

Синтексис: =DATE(year;month;day)

year — год
month — месяц
day — день

Пример:

=DATE(2011;12;21) — возворащает 40908 или 31.12.2011, если перевести в формат даты.

Вот так она будет встроена внутрь какой-нибудь функции.

Обратите внимание, что если прописать условие как «<30.06.2011», то оно не будет работать, так как «30.06.2011» — это текст, а текст по-умолчанию равен нулю.

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.