Критерий независимости хи-квадрат в EXCEL

Критерий независимости хи-квадрат используется для определения связи между двумя категориальными переменными. Примерами пар категориальных переменных являются: Семейное положение vs. Уровень занятости респондента; Порода собак vs. Профессия хозяина, Уровень з/п vs. Специализация инженера и др. При вычислении критерия независимости проверяется гипотеза о том, что между переменными связи нет. Вычисления будем производить с помощью функции MS EXCEL 2010 ХИ2.ТЕСТ() и обычными формулами.

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

Данная таблица называется таблицей сопряжённости признаков (или факторной таблицей, англ. Contingency table). Элементы на пересечении строк и столбцов таблицы обычно обозначают O ij (от англ. Observed, т.е. наблюденные, фактические частоты).

Нас интересует вопрос «Влияет ли Семейное положение на Занятость?», т.е. существует ли зависимость между двумя методами классификации выборки ?

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

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

В этом случае семейное положение однозначно определяет занятость (см. файл примера лист Пояснение ). И наоборот, примером полной независимости является другой результат опроса:

Обратите внимание, что процент занятости в этом случае не зависит от семейного положения (одинаков для женатых и не женатых). Это как раз совпадает с формулировкой нулевой гипотезы . Если нулевая гипотеза справедлива, то результаты опроса должны были бы так распределиться в таблице, что процент занятых был бы одинаковым независимо от семейного положения. Используя это, вычислим результаты опроса, которые соответствуют нулевой гипотезе (см. файл примера лист Пример ).

Сначала вычислим оценку вероятности, того, что элемент выборки будет иметь определенную занятость (см. столбец u i ):

где с – количество столбцов (columns), равное количеству уровней переменной «Семейное положение».

Затем вычислим оценку вероятности, того, что элемент выборки будет иметь определенное семейное положение (см. строку v j ).

где r – количество строк (rows), равное количеству уровней переменной «Занятость».

Теоретическая частота для каждой ячейки E ij (от англ. Expected, т.е. ожидаемая частота) в случае независимости переменных вычисляется по формуле: E ij =n* u i * v j

Известно, что статистика Х 2 при больших n имеет приблизительно ХИ2-распределение с (r-1)(c-1) степенями свободы (df – degrees of freedom):

Примечание : Вышеуказанная статистика при с=1 используется для вычисления критерия согласия Пирсона ХИ-квадрат (см. статью Проверка гипотез критерием хи-квадрат Пирсона в MS EXCEL ).

Если вычисленное на основе выборки значение этой статистики «слишком большое» (больше порогового), то нулевая гипотеза отвергается. Пороговое значение вычисляется на основании уровня значимости , например с помощью формулы =ХИ2.ОБР.ПХ(0,05; df) .

Примечание : Уровень значимости обычно принимается равным 0,1; 0,05; 0,01.

При проверке гипотезы также удобно вычислять p-значение , которое мы сравниваем с уровнем значимости . p -значение рассчитывается с использованием ХИ2-распределения с (r-1)*(c-1)=df степеней свободы.

Если вероятность, того что случайная величина имеющая ХИ2-распределение с (r-1)(c-1) степенями свободы примет значение больше вычисленной статистики Х 2 , т.е. P <Х 2 (r-1)*(c-1) >Х 2 >, меньше уровня значимости , то нулевая гипотеза отклоняется.

В MS EXCEL p-значение можно вычислить с помощью формулы =ХИ2.РАСП.ПХ(Х 2 ;df) , конечно, вычислив непосредственно перед этим значение статистики Х 2 (это сделано в файле примера ). Однако, удобнее всего воспользоваться функцией ХИ2.ТЕСТ() . В качестве аргументов этой функции указываются ссылки на диапазоны содержащие фактические (Observed) и вычисленные теоретические частоты (Expected).

Если уровень значимости > p -значения , то означает это фактические и теоретические частоты, вычисленные из предположения справедливости нулевой гипотезы , серьезно отличаются. Поэтому, нулевую гипотезу нужно отклонить.

Использование функции ХИ2.ТЕСТ() позволяет ускорить процедуру проверки гипотез , т.к. не нужно вычислять значение статистики . Теперь достаточно сравнить результат функции ХИ2.ТЕСТ() с заданным уровнем значимости .

Примечание : Функция ХИ2.ТЕСТ() , английское название CHISQ.TEST, появилась в MS EXCEL 2010. Ее более ранняя версия ХИ2ТЕСТ() , доступная в MS EXCEL 2007 имеет тот же функционал. Но, как и для ХИ2.ТЕСТ() , теоретические частоты нужно вычислить самостоятельно.

СОВЕТ : О проверке других видов гипотез см. статью Проверка статистических гипотез в MS EXCEL .

Функция ПРОЦЕНТИЛЬ.ВКЛ()

Начиная с версии MS EXCEL 2010 для расчета процентилей используется функция ПРОЦЕНТИЛЬ.ВКЛ() – английское название PERCENTILE.INC(). В более ранних версиях MS EXCEL использовался ее аналог – функция ПЕРСЕНТИЛЬ() .

Напомним определение процентиля , данное выше: K-й Процентиль представляет такое собой значение Х в наборе данных, которое разделяет набор на две части: одна часть содержит K% данных, меньших Х , а другая часть содержит все остальные значения набора (т.е. 1- K % данных б о льших Х).

Разберем детально как работает функция ПРОЦЕНТИЛЬ.ВКЛ() .

Пусть имеется массив значений ( выборка ). В массиве 49 значений, массив расположен в диапазоне B15:B63 , имеются повторы значений , массив для удобства отсортирован по возрастанию (см. файл примера , лист ПРОЦЕНТИЛЬ.ВКЛ ).

Рассчитаем 0,08-ю процентиль ( 8%-процентиль ) с помощью формулы =ПРОЦЕНТИЛЬ.ВКЛ(B15:B63; 0,08) . Получим, что 0,08-я процентиль равна 4,84.

Проанализируем, что мы получили.

  • Во-первых, значения 4,84 нет в массиве (есть 4 и 5), т.е. функция ПРОЦЕНТИЛЬ.ВКЛ() интерполирует значения.
  • Во-вторых, процент значений меньших 4,84 равен не точно 8%, а 8,16%=4/49*100% (т.к. всего 4 значения в массиве меньше 4,84). Это произошло, т.к. в выборке относительно мало значений.
  • Другой причиной расхождения могут стать повторы. Например, заменив, первые 4 значения в массиве (т.е. 1; 2; 3; 4) числом 5, мы получим вместо 8,16% – 0%. Это произошло потому, что теперь 0,08-я процентиль равна 5, а в выборке нет ни одного значения меньше 5.

Как видно из рисунка ниже первое значение ( минимальное , равное 1) является 0-й процентилью .

Соответственно, 1-й процентилью (100% процентилью ), является максимальное значение равное 120 (см. файл примера лист ПРОЦЕНТИЛЬ.ВКЛ).

Как видно из рисунка, следующее за минимальным значением (т.е. число 2) является 0,0208-й процентилью . Значение 0,0208 или 2,08% – это (k-1)*1/(n-1), где n – это количество значений в массиве (в нашем массиве n=49), а k – это позиция числа в массиве (в данном случае k=2, где 2 – это позиция, а не само число).

Поясним эту формулу. Для вычисления процентили принимается, что весь диапазон значений массива (от мин до макс) разбит n значениями на равные интервалы (их всего n-1). Соответственно, 1/(n-1), это «ширина» интервала (весь диапазон равен 1 или 100%). Обратите внимание, что «ширина» интервала не зависит от данных, а только от их количества. В нашем случае «ширина» интервала равна 0,0208 или 2,08%.

Приведем алгоритм расчета 12,50%-процентили функцией ПРОЦЕНТИЛЬ.ВКЛ() (см. ячейку С21 ):

  • ПРОЦЕНТИЛЬ.ВКЛ() определяет «ширину» интервала (в долях или процентах): =1/(49-1)=0,0208;
  • подсчитывает Количество интервалов , которые были укладываются в 12,50%, т.е. =12,50%/2,08%=6 (значение процентиля кратно ширине интервала, т.е. делится нацело);
  • 6-й интервал располагается между числами массива 10 и 11. Верхняя граница последнего 6-го интервала равна 11;
  • Следовательно, 12,50%-я процентиль равна 11 (см. ячейку B21 ).

По аналогии с непрерывной функцией распределения (см. статью про квантили ), получается, что 12,50% значений должно быть меньше полученного числа 11 (в соответствии с определением процентиля ). Фактически получается, что таких значений 6 (1; 2; 3; 4; 5; 10) и процент значений меньших 11 равен 12,24%=6/49 (причины расхождения: повторы и небольшое количество значений).

Если значение процентиля не кратно ширине интервала (ширина интервала равна 1/(n-1)), то имеет место интерполирование. Например, вспомним результат вычисления 0,08-й процентили равный 4,84. Значение процентили (т.е. не результат, а %) равно 0,08 (8%), что соответствует 3-м целым интервалам (8%/2,08%= 3 ,84) и некой доли (0,84) от ширины следующего интервала. Границами этого «неполного» интервала являются значения 4 ( 0,0625-я процентиль ) и 5 ( 0,0833-я процентиль ). Т.к. разница между 5 и 4 равна 1, то умножая «пройденную» долю интервала (0,84) на длину интервала в абсолютных значениях (=5-4=1), получаем 0,84. В итоге получаем 4,84: 4 – левая граница интервала + часть следующего (5-4)*0,84.

Если бы в массиве вместо 5 было значение 6, то значение 0,08-й процентили было бы равно 5,68 (4 – левая граница интервала + (6-4)*0,84=1,68).

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

Примечание : Некоторые значения процентилей имеют специальные названия:

  • 25-я процентиль называется 1-й квартилью;
  • 50-я процентиль называется Медианой (2-я квартиль);
  • 75-я процентиль называется 3-й квартилью.

Функции поиска в Excel: ВПР, ГПР, СМЕЩ

При финансовом моделировании достаточно часты задачи поиска текстовых или численных данных в больших таблицах. При этом используются разнообразные функции поиска. Рассмотрим основные.

Функция ВПР в Excel ищет заданное значение (первый аргумент) в первом столбце указанной таблицы (второй аргумент) и возвращает значение из указанного столбца (третий аргумент) этой таблицы. Важное замечание: таблица должна быть отсортирована по возрастанию по значениям первого столбца, иначе функция будет работать некорректно. Если таблица не отсортирована, можно использовать эту же функцию в варианте с 4 аргументами. Четвертый логический аргумент задаёт, точное ли значение искать (если аргумент равен ЛОЖЬ) или можно подобрать похожее значение (�?СТ�?НА). Понять разницу помогут примеры.

Функция ГПР в Excel работает аналогично ВПР, только в горизонтальном направлении: перебирает таблицу по столбцам, ищет значения в первом столбце и возвращает значения этого же столбца, но в указанной ячейке.

Функция смещения СМЕЩ позволяет получать значения, отстоящие от начальной ячейки (первый аргумент) на заданное количество строк (второй аргумент) и столбцов (третий аргумент). Второй и третий аргумент могут быть целыми числами, в т.ч. отрицательными (если необходимо обратиться к ячейке левее и выше начальной).

При использовании этой функции зачастую возможен выход за рамки вычисляемых таблиц, поэтому её нужно использовать на пару с функцией ЕСЛ�?ОШ�?БКА.

Практическая работа: функции поиска и смещения

Обратите внимание на табличку «месяц – выполнение плана» внизу слева на листе «основные функции» файла uchebnik — funkcii. Смысл её в том, чтобы при выборе месяца программа показывала соответствующий показатель, это часто бывает удобно на сводных таблицах отчётности.

В ячейке А29 сделайте возможность выбора месяца с января по декабрь. Для этого выберите меню Данные – кнопка Проверка данных, выберите тип данныхСписок и задайте диапазон А2:А13 в строке �?сточник. Теперь значения в этой ячейке могут быть заданы только путём выбора из списка.

В ячейке В29 введите выражение для функции: «=ВПР(A29;A1:F14;6;ЛОЖЬ)».

Разберём подробно. Первый аргумент – что функция ВПР будет искать, в данном случае значение месяца. Поиск будет производиться в таблице A1:F14 (второй аргумент) в первом столбце. При нахождении нужного значения функция вернёт соответствующее значение из шестого столбца (третий аргумент). Четвёртым аргументом указано, что в данном случае интересует точное совпадение.

Для разбора второго примера использования функции ВПР прочитайте условие задачи 2 на этом же листе.

Постройте вспомогательную табличку в произвольном месте из двух столбцов. В первом столбце будут пороговые значения выполнения плана продаж (0%, 95%, 98%, 100%), во втором – соответствующая сумма бонуса. Отформатируйте эту таблицу по вкусу (включая числовой формат для процентных значений), выделите её и задайте имя диапазона (правая кнопка мыши – �?мя диапазона… — задайте произвольное имя, например Условия_бонуса). Теперь переходим к использованию функции ВПР. В ячейке Н2 введите функцию «=ВПР(F2;Условия_бонуса;2)» (используйте то имя диапазона, которое задали). Протяните эту формулу вниз до декабря.

Разберём использование функции. Условием начисления бонуса является процент выполнения плана, поэтому функция берёт значение процента из большой таблицы и сопоставляет его с маленькой табличкой бонусов, извлекая из второго столбика этой таблички размер бонуса. Обратите внимание, что четвёртый аргумент функции ВПР сейчас задавать нет необходимости, поскольку маленькая табличка (в которой происходит работа функции ВПР) отсортирована по возрастанию значений первого столбца.

Для исследования функции СМЕЩ откройте лист «Смещение». В нём в строчку записаны знакомые значения отгрузок по месяцам. Теперь по условиям задачи необходимо вычислить значение поступлений от клиентов в зависимости от разных условий платежа: 1, 2 месяца отсрочки или оплата авансом (0 месяцев). �?спользуем функцию СМЕЩ.

Для начала, сделайте возможность выбора значения отсрочки. Встаньте на ячейку В9, меню Данные – кнопка Проверка данных, выберите тип данных – целое число, Значение – между, минимум -0, максимум – 2. Теперь для удобства задайте имя этой ячейке: правая кнопка – �?мя диапазона… — задайте, например, «Отсрочка». Поставьте пока значение 0 – отсрочки нет.

Перейдите к таблице поступлений. В ячейку В12 запишите формулу «=СМЕЩ(B7;0;-Отсрочка)» (обратите внимание, что значение ячейки берётся с минусом). Протяните формулу до декабря (не до итогового значения!). Пока формула работает нормально.

Поставьте значение отсрочки – 2 месяца. Теперь первое значение в таблице поступлений – ошибка (поскольку формула адресовала несуществующую ячейку), а в февраль попал заголовок предыдущей таблицы, итоговое значение не вычисляется. Добавьте функцию ЕСЛ�?ОШ�?БКА: «=ЕСЛ�?ОШ�?БКА(СМЕЩ(B7;0;-Отсрочка);0)». Теперь ошибка адресации отсечена, итог вычисляется, но появление текста в таблице портит общую картину. Доработаем функцию, умножив на единицу возвращаемое функцией СМЕЩ значение: «=ЕСЛ�?ОШ�?БКА(СМЕЩ(B7;0;-Отсрочка)*1;0)». Теперь при умножении текстового значения на единицу возникает ошибка вычисления (текст нельзя умножать на число), которая также отсекается функцией ЕСЛ�?ОШ�?БКА. Для численных значений программа выдаёт верный результат.

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: Как находить значения, которые находятся слева покажет эту возможность в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP(“lookup value”,A1:C10,2)
=ВПР(“lookup value”;A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

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

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.

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

С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.

Используем несколько ВПР в одной формуле

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

Читайте также:  Макрос для выделения ячеек со снятой защитой на листе Excel

Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new), куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old).

Руководство по функции ВПР в Excel

Чтобы добавить цены из второй таблицы поиска в основную таблицу, необходимо выполнить действие, известное как двойной ВПР или вложенный ВПР.

    Запишите функцию ВПР, которая находит имя товара в таблице Lookup table 1, используя SKU, как искомое значение:

На рисунке ниже виден результат, возвращаемый созданной нами формулой:

Руководство по функции ВПР в Excel

ADF тест для парного трейдинга в Excel

Еще

Полезная статья с сайта www.quantinsti.com о тесте на коинтеграцию, применяемому в парном трейдинге.

Как вы знаете, для реализации стратегии парного трейдинга необходимо проведение тестов на коинтеграцию используемых инструментов, и для этой цели часто применяют дополненный тест Дики-Фулера (ADF). Тем не менее, при поиске критериев коинтеграции, ADF не стоит в первых рядах. Скорее, его можно найти по запросу «тестирование на единичный корень (unit root)».

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

Если вы хотите изучить тест подробно, то прочитайте статью по следующей ссылке: http://robotwealth.com/exploring-mean-reversion-and-cointegration-part-2/

Шаг 1: Получение данных двух активов, к которым можно применить ADF

В этом примере мы используем компании с Йоханнесбургской биржи JSE:

ADF тест для парного трейдинга в Excel

Шаг 2: Применение линейной регрессии к двум активам, используя серию наблюдений

В экселе должен быть подключен пакет Data Analysis.

Возьмем серию из 60 наблюдений. Убедитесь, что вывод остатков регрессии отмечен галочкой, как показано ниже:

ADF тест для парного трейдинга в ExcelADF тест для парного трейдинга в Excel

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

Проверьте вывод остатков регрессии в результатах:

ADF тест для парного трейдинга в Excel

Коэффициент при переменной X 0.78255 будет использоваться в качестве коэффициента хэджирования.

Шаг 3: Расчет разницы остатков регрессии

Создадим новую колонку Delta, в которую поместим значения разницы остатков:

ADF тест для парного трейдинга в Excel

Шаг 4: Вычислим остаток регрессии t-1

В следующей колонке поместим значение остатка, сдвинутое на 1 шаг по времени:

ADF тест для парного трейдинга в Excel

Шаг 5: Применим линейную регрессию к колонкам Delta и t-1

ADF тест для парного трейдинга в Excel

Шаг 6: Сравним статистику t теста с критическим значением

Для случая отклонения нулевой гипотезы о присутствии единичного корня, t статистика должна быть меньше порогового значения. Пороговое значение для ADF имеет собственное распределение, ниже дан пример некоторых таких значений для разного размера выборки, по временным сериям с трендом и без:

ADF тест для парного трейдинга в Excel

Для наших данных:

ADF тест для парного трейдинга в Excel

  • Мы возьмем пороговое значение, равное -2.89, так как у нас серия из менее 100 наблюдений
  • Наша t статистика равна — 3.369
  • Таким образом, нулевая гипотеза отклонена и мы можем утверждать, что данные коинтегрированы.

Заключение

Тест необходимо проводить при получении каждого нового наблюдения, и, конечно, это не совсем удобно делать в Excel. Если вы хотите запустить стратегию парного трейдинга, которая будет применять тестирование на коинтеграцию с помощью ADF, то рекомендуем перенести указанную методику на языки R, C++ и т.д.

Функция ПОРОГ для определения порогового значения в Excel

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

Содержание:

  • 2.1. Выполните точное совпадение Vlookup и приблизительное совпадение Vlookup
    • Сделайте точное соответствие Vlookup в Excel
    • Сделайте приблизительное соответствие Vlookup в Excel
    • Vlookup и вернуть второе или n-е совпадающее значение
    • Vlookup и вернуть последнее совпадающее значение
    • Vlookup сопоставление значений между двумя заданными значениями или датами с помощью формулы
    • Vlookup сопоставление значений между двумя заданными значениями или датами с помощью полезной функции
    • 3.1. Двусторонний поиск с функцией Vlookup (Vlookup в строке и столбце)
    • 3.2. Значение соответствия Vlookup на основе двух или более критериев
    • 3.3. Vlookup возвращает несколько совпадающих значений с одним или несколькими условиями
      • Vlookup все совпадающие значения на основе одного или нескольких условий по горизонтали
      • Vlookup все совпадающие значения на основе одного или нескольких условий по вертикали
      • Vlookup все совпадающие значения на основе одного или нескольких условий в одну ячейку
      • Vlookup и суммирование всех совпавших значений в строке или нескольких строках
      • Vlookup и суммирование всех совпавших значений в столбце или нескольких столбцах
      • Vlookup и суммирование первых или всех совпавших значений с помощью мощной функции
      • Vlookup и суммируйте все совпавшие значения в строках и столбцах
      • Vlookup для объединения двух таблиц на основе одного ключевого столбца
      • Vlookup для объединения двух таблиц на основе нескольких ключевых столбцов
      • 4.1. Vlookup для получения форматирования ячеек (цвет ячейки, цвет шрифта) вместе со значением поиска
      • 4.2. Сохранять формат даты из возвращаемого значения Vlookup
      • 4.3. Vlookup и возврат соответствующего значения с комментарием ячейки
      • 4.4. Работайте с текстом и действительными числами в Vlookup

      Введение в функцию ВПР – синтаксис и аргументы

      Синтаксис функции ВПР:

      Аргументы:

      Lookup_value: Значение, которое вы хотите найти. Он должен быть в первом столбце диапазона table_array.

      Table_array: Диапазон данных или таблица, в которой находятся столбец значения поиска и столбец значения результата.

      Col_index_num: Номер столбца, из которого будет возвращено совпадающее значение. Он начинается с 1 в крайнем левом столбце массива таблиц.

      Range_lookup: Логическое значение, определяющее, будет ли функция ВПР возвращать точное или приблизительное совпадение.

      • Примерное совпадение1 / ИСТИНА: Если точное совпадение не найдено, формула выполняет поиск ближайшего совпадения – наибольшего значения, которое меньше искомого значения. В этом случае вам следует отсортировать столбец подстановки в порядке возрастания.
        = ВПР (значение_просмотра, массив_таблиц, индекс_столбца, ИСТИНА)
        = ВПР (значение_просмотра, массив_таблиц, индекс_столбца, 1)
      • Точное совпадение0 / ЛОЖЬ: Используется для поиска значения, точно равного искомому значению. Если точное совпадение не найдено, будет возвращено значение ошибки # N / A.
        = ВПР (значение_просмотра, массив_таблиц, индекс_столбца, ЛОЖЬ)
        = ВПР (значение_просмотра, массив_таблиц, индекс_столбца, 0)

      Ноты:

      • 1. Функция Vlookup ищет значение только слева направо.
      • 2. Если есть несколько совпадающих значений на основе значения поиска, только первое совпадение будет возвращено с помощью функции Vlookup.
      • 3. Он вернет значение ошибки # Н / Д, если искомое значение не может быть найдено.

      Основные примеры ВПР

      1. Выполните точное совпадение Vlookup и приблизительное совпадение Vlookup

      Сделайте точное соответствие Vlookup в Excel

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

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

      1. Примените приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:

      2. Затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, и вы получите нужные результаты. Смотрите скриншот:

      • 1. В приведенной выше формуле F2 – это значение, которое вы хотите вернуть с совпадающим значением, A2: D7 это массив таблицы, число 3 – это номер столбца, из которого возвращается совпадающее значение, а НЕПРАВДА относится к точному совпадению.
      • 2. Если значение вашего критерия не найдено в диапазоне данных, отобразится значение ошибки # N / A.
      Сделайте приблизительное соответствие Vlookup в Excel

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

      Например, если у вас есть данные следующего диапазона, указанных заказов нет в столбце «Заказы», ​​как получить ближайшую скидку в столбце B?

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

      2. Затем перетащите маркер заполнения вниз к ячейкам, чтобы применить эту формулу, и вы получите приблизительные совпадения на основе заданных значений, см. Снимок экрана:

      Ноты:

      • 1. В приведенной выше формуле D2 – это значение, относительное значение которого вы хотите вернуть, A2: B9 это диапазон данных, число 2 указывает номер столбца, в котором возвращается совпадающее значение, и ИСТИНА относится к приблизительному совпадению.
      • 2. Приблизительное совпадение вернет наибольшее значение, которое меньше вашего конкретного значения поиска.
      • 3. Чтобы использовать функцию Vlookup для получения приблизительного значения соответствия, вы должны отсортировать крайний левый столбец диапазона данных в порядке возрастания, в противном случае будет возвращен неправильный результат.

      2. Выполните Vlookup с учетом регистра в Excel

      По умолчанию функция Vlookup выполняет поиск без учета регистра, что означает, что она обрабатывает символы нижнего и верхнего регистра как идентичные. Иногда вам может потребоваться выполнить поиск с учетом регистра в Excel, функции Index, Match и Exact или функции Lookup и Exact могут оказать вам услугу.

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

      Формула 1: использование функций ТОЧНЫЙ, ИНДЕКС, ПОИСКПОЗ

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

      2, Затем нажмите Shift + Ctrl + Enter одновременно, чтобы получить первый результат, а затем выберите ячейку формулы, перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, тогда вы получите правильные результаты, которые вам нужны. Смотрите скриншот:

      Ноты:

      • 1. В приведенной выше формуле A2: A10 столбец, содержащий конкретные значения, в которых вы хотите искать, F2 это значение поиска, C2: C10 это столбец, из которого будет возвращен результат.
      • 2. Если найдено несколько совпадений, эта формула всегда будет возвращать первое совпадение.

      Формула 2: использование функций поиска и точных данных

      1. Примените приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:

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

      Ноты:

      • 1. В приведенной выше формуле A2: A10 столбец, содержащий конкретные значения, в которых вы хотите искать, F2 это значение поиска, C2: C10 это столбец, из которого будет возвращен результат.
      • 2. Если найдено несколько совпадений, эта формула всегда будет возвращать последнее совпадение.

      3. Значения Vlookup справа налево в Excel

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

      4. Vlookup второе, n-е или последнее совпадающее значение в Excel

      Обычно, если при использовании функции Vlookup найдено несколько совпадающих значений, возвращается только первая совпадающая запись. В этом разделе я расскажу о том, как получить второе, n-е или последнее совпадающее значение с помощью функции Vlookup.

      Vlookup и вернуть второе или n-е совпадающее значение

      Предположим, у вас есть список имен в столбце A, учебный курс, который они приобрели, в столбце B, и теперь вы ищете 2-й или n-й учебный курс, купленный данным клиентом. Смотрите скриншот:

      1. Чтобы получить второе или n-е совпадающее значение на основе заданных критериев, примените следующую формулу массива в пустую ячейку:

      2, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, а затем выберите ячейку формулы, перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, и все вторые совпадающие значения на основе заданных имен будут отображены одновременно, см. снимок экрана:

      Примечание:

      • В этой формуле, A2: A14 диапазон со всеми значениями для поиска, B2: B14 это диапазон совпадающих значений, из которых вы хотите вернуться, E2 это значение поиска, а последнее число 2 указывает второе совпадающее значение, которое вы хотите получить, если вы хотите вернуть третье совпадающее значение, вам просто нужно изменить его на 3, как вам нужно.
      Vlookup и вернуть последнее совпадающее значение

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

      5. Vlookup сопоставление значений между двумя заданными значениями или датами

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

      Vlookup сопоставление значений между двумя заданными значениями или датами с помощью формулы

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

      2. Затем перетащите дескриптор заполнения, чтобы заполнить эту формулу в другие нужные вам ячейки, и теперь вы получите все сопоставленные записи на основе заданного значения, см. Снимок экрана:

      Ноты:

      • 1. В приведенной выше формуле A2: A6 – диапазон меньших значений и B2: B6 диапазон больших чисел в вашем диапазоне данных, E2 это заданное значение, которое вы хотите получить соответствующее значение, C2: C6 это данные столбца, из которых вы хотите извлечь.
      • 2. Эту формулу также можно использовать для извлечения совпадающих значений между двумя датами, как показано на скриншоте ниже:
      Vlookup сопоставление значений между двумя заданными значениями или датами с помощью полезной функции

      Если вам больно с приведенной выше формулой, здесь я представлю простой инструмент – Kutools for Excel, С его ПОИСК между двумя ценностями функцию, вы можете вернуть соответствующий элемент на основе определенного значения или даты между двумя значениями или датами, не запоминая никаких формул. Нажмите, чтобы загрузить Kutools for Excel прямо сейчас!

      6. Использование подстановочных знаков для частичных совпадений в функции Vlookup

      В Excel подстановочные знаки могут использоваться в функции Vlookup, которая позволяет выполнить частичное совпадение для значения поиска. Например, вы можете использовать Vlookup для возврата совпадающего значения из таблицы на основе части значения поиска.

      Предположим, у меня есть ряд данных, как показано на скриншоте ниже, теперь я хочу извлечь оценку на основе имени (а не полного имени). Как можно было решить эту задачу в Excel?

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

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

      Ноты:

      • 1. В приведенной выше формуле E2 & ”*” это значение поиска, значение в E2 и горизонтальное распределение * подстановочный знак («*» обозначает любой символ или любые символы), A2: C11 это диапазон поиска, число 3 столбец, содержащий возвращаемое значение.
      • 2. Vlookup при использовании подстановочных знаков вы должны установить режим точного совпадения с FALSE или 0 для последнего аргумента в функции Vlookup.

      Советы:

      1. Найдите и верните совпадающие значения, заканчивающиеся определенным значением, примените эту формулу: =VLOOKUP(“*”&E2, $A$2:$C$11, 3, FALSE)

      2. Чтобы найти и вернуть совпадающее значение на основе части текстовой строки, независимо от того, находится ли указанный текст впереди, позади или посередине текстовой строки, вам просто нужно соединить два символа * вокруг ссылки на ячейку или текста. Пожалуйста, используйте эту формулу: =VLOOKUP(“*”&D2&”*”, $A$2:$B$11, 2, FALSE)

      7. Значения Vlookup из другого листа

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

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

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

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

      Примечание: В приведенной выше формуле:

      • A2 представляет значение поиска;
      • Техническая спецификация – это имя рабочего листа, на котором вы хотите найти данные (если имя листа содержит пробелы или знаки пунктуации, вы должны заключить имя листа в одинарные кавычки, в противном случае вы можете напрямую использовать имя листа, например = VLOOKUP (A2, Даташит! $ A $ 2: $ C $ 15,3,0));
      • A2: C15 это диапазон данных в листе данных, по которому мы ищем данные;
      • номер 3 – это номер столбца, который содержит совпадающие данные, из которых вы хотите вернуться.

      8. Значения Vlookup из другой книги

      В этом разделе мы поговорим о поиске и возврате совпадающих значений из другой книги с помощью функции Vlookup.

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

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

      2. Затем перетащите и скопируйте эту формулу в другие ячейки, которые вам нужны, см. Снимок экрана:

      Ноты:

      • 1. В приведенной выше формуле:
        B2 представляет значение поиска;
        [Список продуктов.xlsx] Лист1 – это имя книги и рабочего листа, из которых вы хотите найти данные (ссылка на книгу заключена в квадратные скобки, а вся книга + лист заключена в одинарные кавычки);
        A2: B6 – это диапазон данных на листе другой книги, для которого мы ищем данные;
        номер 2 – это номер столбца, который содержит совпадающие данные, из которых вы хотите вернуться.
      • 2. Если рабочая книга поиска закрыта, полный путь к файлу книги поиска будет показан в формуле, как показано на следующем снимке экрана:

      9. Vlookup и вернуть пустой или конкретный текст вместо значения ошибки 0 или # Н / Д

      Обычно, когда вы применяете функцию vlookup для возврата соответствующего значения, если соответствующая ячейка пуста, она вернет 0, а если соответствующее значение не найдено, вы получите значение ошибки # N / A, как показано ниже. Вместо отображения значения 0 или # N / A с пустой ячейкой или другим значением, которое вам нравится, это Vlookup возвращает пустое или определенное значение вместо 0 или н / д пошаговое руководство может оказать вам услугу.

      Расширенные примеры ВПР

      1. Двусторонний поиск с функцией Vlookup (Vlookup в строке и столбце)

      Иногда вам может потребоваться двумерный поиск, что означает одновременное выполнение Vlookup как в строке, так и в столбце. Скажем, если у вас есть следующий диапазон данных, и теперь вам может потребоваться получить стоимость для определенного продукта в указанном квартале. В этом разделе будет представлена ​​некоторая формула для работы с этой работой в Excel.

      Формула 1: использование функций ВПР и ПОИСКПОЗ

      В Excel вы можете использовать комбинацию функций ВПР и ПОИСКПОЗ для двустороннего поиска, примените следующую формулу в пустую ячейку и нажмите Enter ключ для получения результата.

      Примечание: В приведенной выше формуле:

      • H1: значение подстановки в столбце, на основе которого вы хотите получить соответствующее значение;
      • A2: E6: диапазон данных, включая заголовки строк;
      • H2: искомое значение в строке, на основе которой вы хотите получить соответствующее значение;
      • A1: E1: ячейки заголовков столбцов.

      Формула 2: использование функций ИНДЕКС и ПОИСКПОЗ

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

      Примечание: В приведенной выше формуле:

      • B2: E6: диапазон данных, из которого нужно вернуть совпавший элемент;
      • H1: значение подстановки в столбце, на основе которого вы хотите получить соответствующее значение;
      • A2: A6: заголовки строк содержат продукт, который вы хотите найти.
      • H2: искомое значение в строке, на основе которой вы хотите получить соответствующее значение;
      • B1: E1: заголовки столбцов содержат квартал, который вы хотите найти.

      2. Значение соответствия Vlookup на основе двух или более критериев

      Вам легко найти соответствующее значение на основе одного критерия, но если у вас есть два или более критериев, что вы можете сделать? Функции ПРОСМОТР или ПОИСКПОЗ и ИНДЕКС в Excel могут помочь вам быстро и легко решить эту задачу.

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

      Формула 1: использование функции ПРОСМОТР

      Примените приведенную ниже формулу в ячейку, в которой вы хотите получить результат, а затем нажмите клавишу Enter, см. Снимок экрана:

      Ноты:

      • 1. В приведенной выше формуле:
        A2: A12 = G1: означает поиск критериев G1 в диапазоне A2: A12;
        B2: B12 = G2: означает поиск критериев G2 в диапазоне B2: B12;
        D2: D12: диапазон, в котором вы хотите вернуть соответствующее значение.
      • 2. Если у вас более двух критериев, вам просто нужно объединить другие критерии в формулу, например: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

      Формула 2: использование функций ИНДЕКСТ и ПОИСКПОЗ

      Комбинация функций Index и Match также может использоваться для возврата совпадающего значения на основе нескольких критериев. Скопируйте или введите следующую формулу:

      Затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить нужное относительное значение. Смотрите скриншот:

      Ноты:

      • 1. В приведенной выше формуле:
        A2: A12 = G1: означает поиск критериев G1 в диапазоне A2: A12;
        B2: B12 = G2: означает поиск критериев G2 в диапазоне B2: B12;
        D2: D12: диапазон, в котором вы хотите вернуть соответствующее значение.
      • 2. Если у вас более двух критериев, вам просто нужно объединить новые критерии в формулу, например: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

      3. Vlookup возвращает несколько совпадающих значений с одним или несколькими условиями

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

      Vlookup все совпадающие значения на основе одного или нескольких условий по горизонтали

      Vlookup все совпадающие значения на основе одного условия по горизонтали:

      Для Vlookup и возврата всех совпадающих значений на основе одного конкретного значения по горизонтали общая формула:

      1. Примените приведенную ниже формулу в пустую ячейку и нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, см. снимок экрана:

      2. Затем выберите первую ячейку формулы и перетащите маркер заполнения в правые ячейки, пока не отобразится пустая ячейка и не будут извлечены все соответствующие элементы, см. Снимок экрана:

      Советы:

      Если в возвращенном списке есть повторяющиеся совпадающие значения, чтобы игнорировать дубликаты, используйте эти формулы, затем нажмите Enter чтобы получить первый результат: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),””)

      Продолжайте вводить эту формулу: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),””) в ячейку рядом с первым результатом, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить второй результат, затем перетащите эту формулу в правые ячейки, чтобы получить все другие совпадающие значения, пока не отобразится пустая ячейка, см. снимок экрана:

      Vlookup все совпадающие значения на основе двух или более условий по горизонтали:

      Для Vlookup и возврата всех совпадающих значений на основе более конкретных значений по горизонтали общая формула:

      1. Примените следующую формулу в пустую ячейку, в которую вы хотите вывести результат:

      2. Затем выберите ячейку формулы и перетащите маркер заполнения в правые ячейки, пока не отобразится пустая ячейка и не будут возвращены все совпадающие значения на основе определенных критериев, см. Снимок экрана:

      Внимание: Для получения дополнительных критериев вам просто нужно объединить lookup_value и lookup_range в формулу, например: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2) * (–(lookup_value3 =lookup_range3)))), ROW(return_range) – m, “”), COLUMN() – n)),””) .

      Vlookup все совпадающие значения на основе одного или нескольких условий по вертикали

      Vlookup все совпадающие значения на основе одного условия по вертикали:

      Для Vlookup и возврата всех совпадающих значений на основе одного конкретного значения по вертикали общая формула:

      1. Скопируйте или введите следующую формулу в ячейку, в которой вы хотите получить результат, и нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, см. снимок экрана:

      2. Затем выберите первую ячейку формулы и перетащите дескриптор заполнения вниз в другие ячейки, пока не отобразится пустая ячейка, а все соответствующие элементы не будут перечислены в столбце, см. Снимок экрана:

      Советы:

      Чтобы игнорировать дубликаты в возвращаемых совпадающих значениях, используйте следующие формулы: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),””)

      Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, а затем перетащите эту ячейку формулы вниз в другие ячейки, пока не отобразится пустая ячейка, и вы получите нужный результат:

      Vlookup все совпадающие значения на основе двух или более условий по вертикали:

      Для Vlookup и возврата всех совпадающих значений на основе более конкретных значений по вертикали общая формула:

      1. Скопируйте приведенную ниже формулу в пустую ячейку и нажмите Shift + Ctrl + Enter вместе, чтобы получить первый совпавший элемент.

      2. Затем перетащите ячейку формулы вниз в другие ячейки, пока не отобразится пустая ячейка, см. Снимок экрана:

      Внимание: Для получения дополнительных критериев вам просто нужно объединить lookup_value и lookup_range в формулу, например: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2) * (–(lookup_value3 =lookup_range3)))), ROW(return_range) – m, “”), ROW() – n)),””) .

      Vlookup все совпадающие значения на основе двух или более условий в одну ячейку

      Если вы хотите, чтобы Vlookup возвращал несколько совпадающих значений в одну ячейку с указанным разделителем, новая функция TEXTJOIN может помочь вам быстро и легко решить эту задачу.

      Vlookup все совпадающие значения на основе одного условия в одну ячейку:

      Примените приведенную ниже простую формулу в пустую ячейку и нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить результат:

      Советы:

      Чтобы игнорировать дубликаты в возвращаемых совпадающих значениях, используйте следующие формулы: =TEXTJOIN(“,”, TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, “”), 0),””)=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, “”))

      Vlookup все совпадающие значения на основе двух или более условий в одну ячейку:

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

      Ноты:

      1. Функция ОБЪЕДИНЕНИЕ доступна только в Excel 2019 и Office 365.

      2. Если вы используете Excel 2016 и более ранние версии, воспользуйтесь функцией, определяемой пользователем, из следующих статей:

      4. Vlookup для возврата всей или целой строки сопоставленной ячейки

      В этом разделе я расскажу о том, как получить всю строку совпадающего значения с помощью функции Vlookup.

      1. Скопируйте или введите приведенную ниже формулу в пустую ячейку, в которую вы хотите вывести результат, и нажмите Enter ключ, чтобы получить первое значение.

      2. Затем перетащите ячейку формулы вправо, пока не отобразятся данные всей строки, см. Снимок экрана:

      Внимание: В приведенной выше формуле F2 это значение поиска, на основе которого вы хотите вернуть всю строку, A1: D12 диапазон данных, который вы хотите использовать, A1 указывает номер первого столбца в диапазоне данных.

      Советы:

      Если на основе сопоставленного значения найдено несколько строк, чтобы вернуть все соответствующие строки, примените эту формулу: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),””),ROW()-1)),””) , а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первый результат, затем перетащите маркер заполнения прямо в ячейки, см. снимок экрана:

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

      5. Выполните несколько функций Vlookup (вложенный Vlookup) в Excel

      Иногда вам может потребоваться поиск значений в нескольких таблицах, если какая-либо из таблиц содержит заданное значение поиска, как показано на скриншоте ниже, в этом случае вы можете объединить одну или несколько функций Vlookup вместе с функцией ЕСЛИОШИБКА для выполнения множественного поиска.

      Общая формула для вложенной функции Vlookup:

      Примечание:

      • искомое_значение: ценность, которую вы ищете;
      • Table1, Table2, Table3, . таблицы, в которых существует значение поиска и возвращаемое значение;
      • кол: номер столбца в таблице, из которого вы хотите вернуть совпадающее значение.
      • : Используется для точного совпадения.

      1. Примените следующую формулу в пустую ячейку, куда вы хотите поместить результат:

      2. Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все совпавшие значения были возвращены, как показано ниже:

      Ноты:

      • 1. В приведенной выше формуле J3 это ценность, которую вы ищете; A3: B7, D3: E7, G3: H7 – это диапазоны таблицы, в которых существует значение поиска и возвращаемое значение; Номер 2 – номер столбца в диапазоне, из которого нужно вернуть совпадающее значение.
      • 2. Если искомое значение не может быть найдено, отображается значение ошибки. Чтобы заменить ошибку читаемым текстом, используйте следующую формулу: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),”can’t find”)))

      6. Vlookup, чтобы проверить, существует ли значение на основе данных списка в другом столбце

      Функция Vlookup также может помочь вам проверить, существуют ли значения на основе другого списка, например, если вы хотите найти имена в столбце C и просто вернуть Да или Нет, если имя найдено или нет в столбце A, как показано ниже. показано.

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

      2. Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить этой формулой, и вы получите нужный результат, см. Снимок экрана:

      Внимание: В приведенной выше формуле C2 искомое значение, которое вы хотите проверить; A2: A10 это список диапазона, из которого будут найдены значения поиска; номер 1 – это номер столбца, из которого вы хотите получить значение в своем диапазоне.

      7. Vlookup и суммирование всех совпавших значений в строках или столбцах

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

      Vlookup и суммирование всех совпавших значений в строке или нескольких строках

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

      Vlookup и просуммируйте первые совпавшие значения в строке:

      1. Скопируйте или введите следующую формулу в пустую ячейку и нажмите Shift + Ctrl + Enter ключи вместе, чтобы получить первый результат.

      2. Затем перетащите дескриптор заполнения вниз, чтобы скопировать эту формулу в другие нужные ячейки, и все значения в строке первого совпадающего значения были суммированы вместе, см. Снимок экрана:

      Внимание: В приведенной выше формуле: H2 это ячейка, содержащая искомое значение; A2: F9 – это диапазон данных (без заголовков столбцов), который включает значение поиска и совпадающие значения; Номер 2,3,4,5,6 <> – номера столбцов, используемые для расчета итогового значения диапазона.

      Vlookup и просуммируйте все совпавшие значения в нескольких строках:

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

      Внимание: В приведенной выше формуле: H2 искомое значение поиска; A2: A9 заголовки строк, содержащие искомое значение; B2: F9 диапазон данных числовых значений, которые вы хотите суммировать.

      Vlookup и суммирование всех совпавших значений в столбце или нескольких столбцах

      Vlookup и суммируйте первые совпавшие значения в столбце:

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

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

      Внимание: В приведенной выше формуле: H2 искомое значение поиска; B1: F1 заголовки столбцов, содержащие искомое значение; B2: F9 диапазон данных числовых значений, которые вы хотите суммировать.

      Vlookup и просуммируйте все совпавшие значения в нескольких столбцах:

      Чтобы Vlookup суммировать все совпавшие значения в нескольких столбцах, вы должны использовать следующую формулу:

      Внимание: В приведенной выше формуле: H2 искомое значение поиска; B1: F1 заголовки столбцов, содержащие искомое значение; B2: F9 диапазон данных числовых значений, которые вы хотите суммировать.

      Vlookup и суммирование первых или всех совпавших значений с помощью мощной функции

      Возможно, приведенные выше формулы вам сложно запомнить, в этом случае я порекомендую удобную функцию – Поиск и сумма of Kutools for Excel, с этой функцией вы можете получить результат максимально легко. Нажмите, чтобы загрузить Kutools for Excel прямо сейчас!

      Vlookup и суммируйте все совпавшие значения в строках и столбцах

      Если вы хотите суммировать значения, когда вам нужно сопоставить столбец и строку, например, чтобы получить общую стоимость продукта Sweater в месяц Mar, как показано на скриншоте ниже.

      Пожалуйста, примените следующую формулу к ячейке, а затем нажмите клавишу Enter, чтобы получить результат, см. Снимок экрана:

      Внимание: В приведенной выше формуле: B2: F9 это диапазон данных числовых значений, которые вы хотите суммировать; B1: F1 is заголовки столбцов содержат искомое значение, по которому вы хотите просуммировать; I2 это значение подстановки в заголовках столбцов, которые вы ищете; A2: A9 заголовки строк содержат искомое значение, по которому вы хотите суммировать; H2 – это поисковое значение в заголовках строк, которые вы ищете.

      8. Vlookup для объединения двух таблиц на основе одного или нескольких ключевых столбцов

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

      Vlookup для объединения двух таблиц на основе одного ключевого столбца

      Например, у вас есть две таблицы, первая таблица содержит данные о продуктах и ​​названиях, а вторая таблица содержит продукты и заказы. Теперь вы хотите объединить эти две таблицы, сопоставив общий столбец продуктов в одну таблицу.

      Формула 1: Использование функции ВПР

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

      Внимание: В приведенной выше формуле A2 ценность, которую вы ищете, E2: F8 таблица для поиска, число 2 – номер столбца в таблице, из которого нужно получить значение.

      Формула 2: использование функций ИНДЕКС и ПОИСКПОЗ

      Если ваши общие данные в правой части и возвращенные данные в левом столбце во второй таблице объединить столбец порядка, функция Vlookup не сможет выполнить эту работу. Чтобы искать справа налево, вы можете использовать функции ИНДЕКС и ПОИСКПОЗ, чтобы заменить функцию Vlookup.

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

      Внимание: В приведенной выше формуле A2 это искомое значение, которое вы ищете, E2: E8 диапазон данных, которые вы хотите вернуть, F2: F8 диапазон поиска, который содержит значение поиска.

      Vlookup для объединения двух таблиц на основе нескольких ключевых столбцов

      Если две таблицы, которые вы хотите объединить, имеют несколько ключевых столбцов, для объединения таблиц на основе этих общих столбцов вам могут помочь функции ИНДЕКС и ПОИСКПОЗ.

      Общая формула для объединения двух таблиц на основе нескольких ключевых столбцов:

      1. Примените приведенную ниже формулу в пустую ячейку, в которую вы хотите поместить результат, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое совпадающее значение, см. снимок экрана:

      Внимание: В приведенной выше формуле ссылки на ячейки представлены на скриншоте ниже:

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

      9. Соответствующие значения Vlookup на нескольких листах

      Вы когда-нибудь пробовали использовать значения Vlookup на нескольких листах? Предположим, у меня есть следующие три листа с диапазоном данных, и теперь я хочу получить часть соответствующих значений на основе критериев из этих трех листов, чтобы получить результат, как показано на скриншоте ниже. В этом случае Значения Vlookup на нескольких листах пошаговое руководство может оказать вам услугу.

      Соответствующие значения VLOOKUP сохраняют форматирование ячеек

      1. Vlookup для получения форматирования ячеек (цвет ячейки, цвет шрифта) вместе со значением поиска

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

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

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

      2. В открытом Microsoft Visual Basic для приложений окно, скопируйте ниже код VBA в окно кода.

      Код VBA 1: Vlookup для получения форматирования ячеек вместе со значением поиска

      3. Все еще в Microsoft Visual Basic для приложений окна, нажмите Вставить > модуль, а затем скопируйте приведенный ниже код VBA 2 в окно модуля.

      Код VBA 2: Vlookup для получения форматирования ячеек вместе со значением поиска

      4. После ввода приведенных выше кодов нажмите Инструменты > дело в Microsoft Visual Basic для приложений окно. Затем проверьте Среда выполнения сценария Microsoft флажок в Ссылки – VBAProject диалоговое окно. Смотрите скриншоты:

      5, Затем нажмите OK чтобы закрыть диалоговое окно, а затем сохранить и закрыть окно кода, теперь вернитесь на рабочий лист и примените эту формулу: =LookupKeepFormat(E2,$A$1:$C$10,3) в пустую ячейку, в которую вы хотите вывести результат, а затем нажмите клавишу Enter. Смотрите скриншот:

      Внимание: В приведенной выше формуле E2 это значение, которое вы будете искать, A1: C10 это диапазон таблицы, а число 3 – это номер столбца таблицы, в которую нужно вернуть совпадающее значение.

      6. Затем выберите первую ячейку результата и перетащите маркер заполнения вниз, чтобы получить все результаты вместе с их форматированием. Смотрите скриншот.

      2. Сохранять формат даты из возвращаемого значения Vlookup

      Обычно при использовании функции Vloook для поиска и возврата значения соответствующего формата даты, некоторый числовой формат будет отображаться, как показано ниже. Чтобы сохранить формат даты из возвращаемого результата, вы должны заключить функцию ТЕКСТ в функцию Vlookup.

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

      Внимание: В приведенной выше формуле E2 это внешний вид, A2: C9 это диапазон поиска, число 3 это номер столбца, значение которого вы хотите вернуть, mm/dd/yyy – это формат даты, который вы хотите сохранить.

      3. Vlookup и возврат соответствующего значения с комментарием ячейки

      Вы когда-нибудь пробовали, чтобы Vlookup возвращал не только совпадающие данные ячеек, но и комментарий ячейки, а также в Excel, как показано на следующем снимке экрана? Для решения этой задачи вам может помочь следующая функция, определяемая пользователем.

      1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

      2. Нажмите Вставить > модуль, затем скопируйте и вставьте следующий код в окно модуля.

      Код VBA: Vlookup и возврат соответствующего значения с комментарием ячейки:

      3. Затем сохраните и закройте окно кода, введите эту формулу: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) в пустую ячейку, чтобы найти результат, а затем перетащите маркер заполнения, чтобы скопировать эту формулу в другие ячейки, теперь совпадающие значения и комментарии возвращаются сразу, см. снимок экрана:

      Внимание: В приведенной выше формуле D2 это значение поиска, которое вы хотите вернуть, соответствующее значение, A2: B9 таблица данных, которую вы хотите использовать, число 2 – это номер столбца, который содержит совпадающее значение, которое вы хотите вернуть.

      4. Работайте с текстом и действительными числами в Vlookup

      Например, у меня есть диапазон данных, номер идентификатора в исходной таблице – это числовой формат, в ячейке поиска, которая хранится как текст, при применении обычной функции Vlookup отображается результат ошибки # N / A, как показано ниже. показано. В этом случае, как вы можете получить правильную информацию, если номер поиска и исходный номер в таблице имеют другой формат данных?

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

      Установка порогового значения в таблице

      Помощь в написании контрольных, курсовых и дипломных работ здесь.

      Вложения

      Рабочий файл.xlsx (9.3 Кб, 8 просмотров)

      Формула расчёта при достижении порогового значения
      Здравствуйте. Прошу помочь в решении задачи: 1. По заявлению считаются календарные дни, включая.

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

      Определение оптимального порогового значения сигнала на фоне шума
      Всем привет! Стоит задача декодировать сигнал со звукового файла. Для декодирования нужно выделить.

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

      Помощь в написании контрольных, курсовых и дипломных работ здесь.

      Как отобразить значения в таблице, соответствующие коду в другой таблице
      Здравствуйте. Есть три таблицы: Tbl_authors – Таблица авторов Поля: – Id_Author (первичный.

      Поиск записей в одной таблице, где значения ключевого поля не совпадают с полем в другой таблице
      Имеется Access XP, надо создать запрос для поиска записей в таблице ТОВАРЫ,где значения ключевого.

      Поиск значения в таблице StringGrid (в неупорядоченной таблице)
      Здравствуйте! Очень нужна помощь! Есть таблица StringGrid1 с данными( номер, ФИО, возраст.

      Поиск максимального значения в стобце умной таблице, и генерация значения+1
      Всем привет! Кто может подсказать, как писать код в VBA в таком случае. Имеется умная таблица.

      Формула расчёта при достижении порогового значения

      Здравствуйте.
      Прошу помочь в решении задачи:
      1. По заявлению считаются календарные дни, включая праздники и выходные.
      2. При достижении 100 дней считаются только рабочие дни, т.е. за минусом выходных и праздничных дней.

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

      Помощь в написании контрольных, курсовых и дипломных работ здесь.

      Формула для расчета среднемесячного значения
      Добрый день, на работе столкнулась с проблемой. Сейчас попробую ее описать и приложу файлик пример.

      Какое событие возникает при достижении значения ProgressBar максимального значения
      Всем привет, возможно ли определить конец прогресс бара? Например у БэкГраундВоркера конец это -.

      Удаление строк из файла при достижении n-значения
      Приветствую. Сразу к сути, есть ряд текстовых файлов, в которые каждые 10мин добавляются новые.

      Установка порогового значения в таблице
      Добрый день, уважаемые форумчане! Есть таблица, где идет расчет бонуса продавцу. Этот бонус должен.

      Функция ЕСЛИ в MS Excel с несколькими условиями.

      Предположим, что у нас есть таблица с результатами сдачи контрольной работы студентами ВУЗа. Результаты контрольной работы оцениваются в баллах. От 0 до 100. Где все, что выше 90 баллов, отлично. Выше 80 баллов, это хорошо. Выше 70 баллов, это удовлетворительно. Ниже 70 баллов, это плохо.

      Формула функции ЕСЛИ будет выглядеть вот так:

      Где ячейка С3, первая из проверяемых ячеек в столбце Оценка. Далее функция протянута вниз по столбцу Результат, до конца таблицы.

      Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.

      Таблица выглядит вот так:

      Функция ЕСЛИ в MS Excel с несколькими условиями

      В столбце Результат, в зависимости от количества баллов, функция ЕСЛИ вернула значение, согласно заданному критерию в формуле функции.

      Функция ПОРОГ для определения порогового значения в Excel

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

      Ограничьте результат формулы до максимального или минимального значения

      Чтобы справиться с этой задачей, вам нужно только применить функцию Max или Min в Excel.

      Ограничить результат формулы до максимального значения (100)

      Выберите ячейку, в которую вы поместите формулу, введите эту формулу = МИН (100; (СУММ (A5: A10))) , A5: A10 – это диапазон ячеек, который вы суммируете, и нажмите Enter. Теперь, если сумма больше 100, будет отображаться 100, если нет, отобразится сумма.

      Суммирование больше 100, показать 100
      doc limi максимум минимум 1
      Суммирование меньше 100, показать суммирование
      doc limi максимум минимум 2

      Ограничить результат формулы до минимального значения (20)

      Выберите ячейку, в которую вы помещаете формулу, введите это = MaX (20; (СУММ (A5: A10))) , A5: A10 – это диапазон ячеек, который вы суммируете, и нажмите Enter. Теперь, если сумма меньше 20, будет отображаться 20; если нет, отобразите суммирование.

      Аргументы функции СЧЁТЕСЛИ в MS Excel.

      После того, как была выбрана функция СЧЁТЕСЛИ, появляется диалоговое окно Аргументы функции. В данном диалоговом окне есть два поля для заполнения:

      1. Диапазон, здесь необходимо указать диапазон ячеек, в которых будет осуществляться поиск ячеек с значениями, которые отвечают критериям поиска. Выбрать необходимый диапазон можно сначала поставив курсор на поле Диапазон, а потом выделив необходимые ячейки.
      2. Критерий, здесь необходимо указать условие (критерий поиска), согласно которому функция будет искать и подсчитывать количество ячеек в заданном Диапазоне.

      Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры

      Доверительные интервалы для нового наблюдения Y и среднего значения

      Вычислив параметры простой линейной регрессионной модели Y=aX+β+ε мы получили точечную оценку значения нового наблюдения Y при заданном значении Хi, а именно: Ŷ= a * Хi + b

      Ŷ также является точечной оценкой для среднего значения Yi при заданном Хi. Но, при построении доверительных интервалов используются различные стандартные ошибки .

      Стандартная ошибка нового наблюдения Y при заданном Хi учитывает 2 источника неопределенности:

      • неопределенность связанную со случайностью оценок параметров модели a и b ;
      • случайность ошибки модели ε.

      Учет этих неопределенностей приводит к стандартной ошибке S(Y|Xi), которая рассчитывается с учетом известного значения Xi.

      где SS xx – сумма квадратов отклонений от среднего значений переменной Х:

      Примечание : Se – стандартная ошибка коэффициента регрессии ( наклона а ).

      В MS EXCEL 2010 нет функции, которая бы рассчитывала эту стандартную ошибку , поэтому ее необходимо рассчитывать по вышеуказанным формулам.

      Доверительный интервал или Интервал предсказания для нового наблюдения (Prediction Interval for a New Observation) построим по схеме показанной в разделе Проверка значимости взаимосвязи переменных (см. файл примера лист Интервалы ). Т.к. границы интервала зависят от значения Хi (точнее от расстояния Хi до среднего значения Х ср ), то интервал будет постепенно расширяться при удалении от Х ср .

      Границы доверительного интервала для нового наблюдения рассчитываются по формуле:

      Аналогичным образом построим доверительный интервал для среднего значения Y при заданном Хi (Confidence Interval for the Mean of Y). В этом случае доверительный интервал будет уже, т.к. средние значения имеют меньшую изменчивость по сравнению с отдельными наблюдениями ( средние значения, в рамках нашей линейной модели Y=aX+β+ε, не включают ошибку ε).

      Стандартная ошибка S(Yср|Xi) вычисляется по практически аналогичным формулам как и стандартная ошибка для нового наблюдения:

      Как видно из формул, стандартная ошибка S(Yср|Xi) меньше стандартной ошибки S(Y|Xi) для индивидуального значения .

      Границы доверительного интервала для среднего значения рассчитываются по формуле:

      Функция ПОРОГ для определения порогового значения в Excel

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

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

      Эта статья демонстрирует шаги использования Диаграмма области пороговых значений of Kutools for Excel.

      Создание диаграммы с областями пороговых значений в Excel

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

      1. Включите функцию, нажав Kutools > Графики > Сравнение категорий > Диаграмма области пороговых значений.

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

      Заметки:

      3. Затем нажмите OK в появлении Kutools for Excel диалоговое окно.

      На этом диаграмма области пороговых значений завершена.

      Kutools for Excel

      Описанная выше функциональность – лишь одна из 300 мощных функций Kutools for Excel.

      Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатная загрузка и использование в течение 60 дней.

      Заключение

      Когда функция ВПР работает с базами данных, аргумент Range_lookup (Интервальный_просмотр) должен принимать FALSE (ЛОЖЬ). А значение, введённое в качестве Lookup_value (Искомое_значение) должно существовать в базе данных. Другими словами, идёт поиск точного совпадения.

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

      Например: Мы хотим определить, какую ставку использовать в расчёте комиссионных для продавца с объёмом продаж $34988. Функция ВПР возвращает нам значение 30%, что является абсолютно верным. Но почему же формула выбрала строку, содержащую именно 30%, а не 20% или 40%? Что понимается под приближенным поиском? Давайте внесём ясность.

      Когда аргумент Range_lookup (Интервальный_просмотр) имеет значение TRUE (ИСТИНА) или опущен, функция ВПР просматривает первый столбец и выбирает наибольшее значение, которое не превышает искомое.

      Важный момент: Чтобы эта схема работала, первый столбец таблицы должен быть отсортирован в порядке возрастания.

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