Как в excel сделать 2 если

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

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

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

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

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

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

Прогноз остатков

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

Функция ЕСЛИ для задания условия в формуле

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

функция если в excel примеры функция если функция если с несколькими условиями

Функции Excel.Функция И(), ИЛИ()

На прошлом уроке «Функции Excel. Функция ЕСЛИ» была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях — вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции — И() и ИЛИ().

Функция И()

Функция И() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 И Условие 2. При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.

Функция ИЛИ()

Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 ИЛИ Условие 2. Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.

Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.

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

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

Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В — 52, 55, 88, 4, 25. Столбец С — 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.

Переходим в ячейку D1 и с помощью мастера функций вводим следующее

функция И пример

Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);»Все значения равны»;»Значения не равны») “

Пример 2.

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

Мастер функций выглядит так:

функция ИЛИ пример

Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);»Есть равные значения»;»Нет равных значений»)”

Вот и все. Функции не сложны. Применяйте их там, где встречаются условия и произносится И и ИЛИ. Так же, всеми выше перечисленными функциями можно сравнивать текстовые значения.

И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.

Пример 3.

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

Таблица следующего вида:

сложные условные формулы в Excel

Составим формулу следующего вида:

Пища для разума Почему значения указаны в вещественном виде т.е. 0,41, 0,76, можете прочитать в уроке «Формат данных в Excel»!?

Результат работы формулы:

П.С.: Если есть варианты сократить формулу(а они есть) пишите в комментариях. А пока – пока

4. Поиск и подстановка значений из одной таблицы в другую (формула ВПР)

Представим, что к нам пришла новая таблица, с новыми ценниками для товара. Хорошо, если наименований 10-20 — можно и в ручную их все «перезабить». А если таких наименований сотни? Гораздо быстрее, если бы Excel самостоятельно нашел в совпадающие наименования из одной таблицы в другой, а затем скопировал новые ценники в старую нашу таблицу.

Для такой задачи используется формула ВПР. В свое время сам «мудрил» с логическими формулами «ЕСЛИ» пока не встретил эту замечательную штуку!

Вот наш пример + новая таблица с ценниками. Сейчас нам нужно автоматически подставить новые ценники из новой таблицы в старую (новые ценники красные ).

2014-03-29 10_01_05-Microsoft Excel - Книга1

Ставим курсор в ячейку B2 — т.е. в первую ячейку, где нам нужно изменить ценник автоматически. Далее пишем формулу, как на скриншоте ниже (после скриншота будет подробное пояснение к ней).

2014-03-29 10_00_33-Microsoft Excel - Книга1

=ВПР( A2 ; $D$2:$E$5 ; 2 ), где

A2 — то значение, которое мы будем искать, чтобы взять новый ценник. В нашем случае ищем в новой таблице слово «яблоки».

$D$2:$E$5 — выделяем полностью нашу новую таблицу (D2:E5, выделение идет от верхнего левого угла к правому нижнему по диагонали), т.е. там, где будет производится поиск. Знак «$» в этой формуле необходим для того, чтобы при копировании этой формулы в другие ячейки — D2:E5 не менялись!

Важно! Поиск слова «яблоки» будет вестись только в первой колонке вашей выделенной таблицы, в данном примере «яблоки» будет искаться в колонке D.

2 — Когда слово «яблоки» будет найдено, функция должна знать, из какого столбика выделенной таблицы (D2:E5) скопировать нужное значение. В нашем примере копировать из колонки 2 (E), т.к. в первой колонке (D) мы производили поиск. Если ваша выделенная таблица для поиска будет состоять из 10 колонок, то в первой колонке производится поиск, а со 2 по 10 колонки — вы можете выбрать число для копирования.

Чтобы формула =ВПР(A2;$D$2:$E$5;2) подставила новые значения и для других наименований товара — просто скопируйте ее в другие ячейки столбца с ценниками товара (в нашем примере копируйте в ячейки B3:B5). Формула автоматически произведет поиск и копирование значения из нужной вам колонки новой таблицы.

Статистические симуляции в Excel

Что делает симуляция. Имитирует шестикратное подбрасывание монеты. В диапазоне С2:F8 подсчитывается теоретическая вероятность выпадения нуля решек, одной решки, и т.д. Подсчеты делаются для честной монеты с вероятностью выпадения решки в одном броске = 50%, и для нечестных монет, с вероятностью выпадения решки при однократном бросании = 20%, 30%, 40%. График для честной монеты представляет собой колоколообразную кривую. Максимум приходится на 3 решки и его теоретическая вероятность составляет 31% (ячейка F5). Для нечестных монет графики плотности вероятности имеют положительную асимметрию: пик сдвинут влево, а правый хвост более длинный, чем левый.

Рис. 2.1. Является ли монет честной? Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

События со случайными исходами обладают тем свойством, что ни один конкретный исход не известен заранее. Однако если наблюдать множество исходов, то можно выявить закономерности. Когда мы подбрасываем честную монету, мы не знаем, какой стороной она упадет, но если мы подбросим монету миллионы раз, мы знаем, что она приземлится решкой очень близко к 50% случаев, если только…

…монета не имеет предпочтений. Здесь они смоделированы заданием более низкой вероятности выпадения решки. Чтобы определить, справедлива монета или нет, нам нужно было бы подбросить монету много раз. В этой симуляции мы делаем только шесть бросков в каждой серии. Поэтому для некоторых серий даже при вероятности решки 50:50 мы можем получить небольшое число серий, где эта вероятность воплотилась в нужный результат – около 31%. В ситуации, показанной на рис. 2.1, мы, вероятно, объявили бы честную монету несправедливой (см. значение в ячейке U20). Всего в одной серии число решек было равно трем. Статистики называют это ошибкой первого типа: мы сделали вывод, что монета нечестная, хотя на самом деле она честная. И наоборот, мы могли объявить монеты № 2 и 3 (ячейки S20 и Т20) честными. Это ошибка II типа: мы делаем вывод, что монета честная, хотя на самом деле она нечестная. Очевидно, что шесть подбрасываний недостаточно для надежных заключений.

Что вам нужно знать. Колоколообразная кривая очень часто встречается в статистике. Она отражает нормальное распределение. Чтобы изобразить эту кривую для двоичной ситуации (да/нет, орел/решка, успех/неудача) отлично подойдет функция Excel БИНОМ.РАСП(). Кстати, все функции в Excel состоящие из двух сокращений, разделенных точкой, где вторая часть РАСП – это статистические функции, возвращающие вероятность. Кроме упомянутой функции, это также: НОРМ.РАСП(), СТЬЮДЕНТ.РАСП(), ПУАССОН.РАСП() и многие другие.

Что вам нужно сделать. В ячейке C2 введите: =БИНОМ.РАСП($B2;6;C$1;0). Формула вернет значение биноминального распределения для нуля успехов (значение в В2) при шести испытаниях и вероятности успеха в одном испытании равного 20% (С1). Обратите внимание на использование смешанных ссылок. Они введены, чтобы можно было протащить формулу на диапазон С3:F8.

В столбце F вероятность выпадения решки 50%, и максимум кривой соответствует трем успехам (ячейка F5). Это честная монета. В других столбцах вероятность выпадения решки в одном испытании меньше (от 20% до 40%). Максимум кривой смещен влево. Т.е., решка будет выпадать реже.

В ячейке C11 мы имитируем первой бросок монеты: =ЕСЛИ(СЛЧИС()<=C$10; » Р » ; » О » ). Скопируйте эту формулу на диапазон С11: F16.

В C18 мы подсчитываем процент решек: =СЧЁТЕСЛИ(C11:C16; » Р » )/6. Скопируйте формулу в С18:F18.

Мы ожидаем, что значение в ячейке F18, соответствующее честной монете, составит 50%. Поскольку мы подбрасываем монету только 6 раз, появляется шанс, что результат может отличаться.

Мы повторим серию из 6 подбрасываний монеты еще 16 раз с помощью Таблицы данных. Введите формулы в R2: =C18, S2: =D18, T2: =E18, U2: =F18. Выделите диапазон Q2:U18. Пройдите по меню Данные –> Анализ » что, если « –> Таблица данных. Оставьте поле Подставлять значения по столбцам в: пустым, а в поле Подставлять значения по строкам в: щелкните на любую пустую ячейку, например W3. Это создаст в диапазоне Q3:U18 формулу массива: <=ТАБЛИЦА(;W5)>. Почему это работает см. предыдущий раздел.

В ячейке R20 подсчитайте вероятность серий, в которых было 50% решек: =СЧЁТЕСЛИ(R2:R18;0,5)/17. Скопируйте на ячейки R20:U20.

В R2:U18 создайте условный формат, чтобы подсветить ячейки со значением 50%.

Обратите внимание, как непросто решить, справедлива монета или нет.

Глава 10. Среднее средних

Что делает симуляция. Каждая выборка представляет собой 10 случайных целых чисел в диапазоне от 0 до 10. Например, первая выборка в ячейках В2:К2. Среднее значение выборки в L2. В ячейке L22 подсчитано среднее 20 средних значений по выборке. Именно значение в L22 и называется среднее средних. В столбцах N и O расположена таблица частот средних по выборке. Эта таблица выведена на график. Среднее средних представлено на графике вертикальной линией.

Рис. 2.2. Распределение средних значений выборок

Что вам нужно знать. Каждый раз, когда вы нажимаете Shift+F9, Excel пересчитывает все случайные числа, средние по выборке и среднее средних. Обратите внимание, как средние значения в столбце L довольно сильно разнятся, потому что каждое из них основано на выборке всего из 10 случайных чисел. А вот среднее средних меняется не сильно, так как оно основано на 10*20 = 200 случайных числах. В статистике это называется законом больших чисел.

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

Читайте также:  Решение уравнений в Excel методом итераций Крамера и Гаусса

Что вам нужно сделать. Введите в ячейку B2 формулу динамического массива: =СЛМАССИВ(20;10;0;10;ИСТИНА). В ячейке L2 вычислите среднее значение первой выборки: =СРЗНАЧ(B2:K2). Скопируйте формулу в L2:L21. В ячейка L22 найдите среднее из средних: =СРЗНАЧ(L2:L21). В ячейку O2 введите формулу динамического массива: =ЧАСТОТА(L2:L21;N2:N11). Как было показано в предыдущем разделе второй аргумент функции включает все интервалы, кроме последнего (N12). Графике основан на таблице частот. Вертикальная линия на графике представляет среднее средних и основана на наборе координат, скрытых в ячейках N15:O16.

Глава 12. Нормальное распределение

Что делает симуляция. В столбце A сгенерированы 100 случайных чисел со средним 0 и стандартным отклонением 1, распределенные нормально. В столбце D представлено частотное распределение этих случайных чисел, а в столбце Е теоретические значения для стандартного нормального распределения.

Рис. 2.3. Симуляция и теоретические вероятности нормального распределения

Что вам нужно знать. Чтобы сгенерировать 100 случайных чисел, распределенных нормально можно использовать надстройку Excel Анализ данных. Поищите эту опцию на вкладке Данные в правой части ленты. Если надстройка не установлена, пройдите по меню Файл –> Параметры, перейдите на вкладку Надстройки, и в нижней части окна в поле Управление выберите Надстройки Excel, кликните Перейти. Поставьте галочку напротив опции Пакет Анализа. Нажмите Ok.

На вкладке Данные, кликните Анализ данных и выберите опцию Генерация случайных чисел. Настройте параметры:

Рис. 2.4. Настройка генератора нормально распределенных случайных чисел

К сожалению, это статичный инструмент. Чтобы сгенерить новые 100 случайных чисел, нужно повторно войти в надстройку Анализ данных и запустить генератор случайных чисел. Для графика теоретической вероятности нормального распределения воспользуемся функцией Excel НОРМ.РАСП().

Что вам нужно сделать. Мы сгенерим случайные числа введя в ячейку A1 формулу: =НОРМ.ОБР(СЛМАССИВ(100;;0;1;ЛОЖЬ);E3;E4). Эта формула массива вернет 100 случайных в одном столбце, распределенные нормально со средним 0 (ячейка Е3) и стандартным отклонением 1 (Е4). Такой массив является волатильным и будет пересчитываться при каждом нажатии Shift+F9.

В ячейке С7 задайте диапазоны агрегирования случайных значений: =ПОСЛЕД(13;;E3-3*E4;E4/2). Эта функция динамических массивов вернет 13 значений, начинающихся с трех сигм меньше среднего (E3-3*E4) с шагом в половину сигмы (Е4/2). В ячейке D7 введите: =ЧАСТОТА(A1#;C7#). Первый аргумент функции ЧАСТОТА() имеет синтаксис A1#. Такого рода ссылка означает обращение ко всему динамическому массиву, заданному формулой в ячейке А1.

В ячейку Е7: =НОРМ.РАСП(C7#;E3;E4;ЛОЖЬ). Эта стандартная функция Excel также использует свойство динамических массивов. Функция в первом аргументе ожидает одно значение, а мы «подсунули» массив С7#. Поэтому функция НОРМ.РАСП() вернет не одно значение, а «разольется» по столбцу на размер массива С7#.

Понажимайте Shift+F9 и следите за изменением столбчатой диаграммы. Линейный график, отражающий теоретические значения распределения нормальной случайной величины, остается неизменным.

Глава 15. Доверительный интервал

Что делает симуляция. Модель показывает, что для генеральной совокупности со средним значением μ = 4,5 (ячейка B1) и стандартным отклонением σ = 0,7 (B2), делая выборку размером n = 35 (B3), мы на 95% (В4) можем быть уверены, что среднее значение по выборке будет между 4,27 и 4,73. Доверительный интервал (B6) будет иным, если мы изменим степень уверенности (95%). Принимая степень уверенности 95%, мы также принимаем вероятность ошибки α = 5% (B5).

Затем в Таблице данных с двумя переменными показано, как изменяется доверительный интервал при изменении стандартного отклонения генеральной совокупности σ и размера выборки n. Это случай анализа » что, если » , основанный на вводе фиксированных значений. Поскольку симуляция изучает теоретические значения, формулы не пересчитываются при нажатии Shift+F9.

Что вам нужно знать. Ячейка B6 используется функция =ДОВЕРИТ.НОРМ(B5;B2;B3). Она имеет 3 аргумента: α = 5% (В5), σ = 0,7 (В2), n = 35 (В3). Функция возвращает величину доверительного интервала, которую следует отложить по обе стороны от среднего значения μ.

Рис. 2.5. Доверительный интервал

В отличие от предыдущих симуляций, здесь Таблица данных основана на двух переменных. Формула, на которой основаны расчеты расположена в левой угловой ячейке таблицы (В7). В ячейках С7:G7 показаны различные n, а в ячейках В8:В16 – различные σ.

Что вам нужно сделать. В ячейку B6 введите формулу: =ДОВЕРИТ.НОРМ(B5;B2;B3). В ячейку В7: =B6. Выделите диапазон. B7:G16. Пройдите по меню Данные –> Анализ » что, если « –> Таблица данных. В поле Подставлять значения по столбцам в: введите В3, а в поле Подставлять значения по строкам в: введите В2. Чтобы не ошибиться запомните правило. Когда в окне Таблицы данных предлагается Подставлять значения по столбцам в, используйте ссылку на исходное значение того параметра, которое будет в заголовках столбцов. Нажмите Ok. Это создаст в диапазоне С8:G16 формулу массива: <=ТАБЛИЦА(B3;B2)>.

Обратите внимание, что формула массива как бы связала все ячейки внутри таблицы (С8:G16). Вы не можете удалить или изменять никакую отдельную ячейку. Для изменения/удаления сначала выделите массив целиком.

Поскольку Таблица данных использует несколько значений σ и n, она не реагирует на изменения в ячейках В2 и В3. Так как среднее значение μ не используется в формулах, то изменения в В1 также не влияют на Таблицу данных. Единственным параметром, влияющим на Таблицу данных, является Степень уверенности. Чем она больше, тем больше доверительный интервал.

Глава 16. Статистическая мощность

Что делает симуляция. Модель позволяет оценить влияние ошибки I рода α (в ячейке B2 на рис. 2.6) на величину ошибки II рода β и статистическую мощность, равную 1 – β. Мощность означает вероятность выявить отличие среднего по выборке от среднего по генеральной совокупности. Или, другими словами, с некоторой степенью уверенности сказать , происходит ли выборка из генеральной совокупности, или нет. В этой модели расчеты основаны на стандартном нормальном распределении и z-значении.

Использование z-значения упрощает анализ физических кривых нормального распределения, сводя их разнообразие к стандартному виду. z-значения фактически являются единицами стандартного отклонения при среднем, равном нулю. Например, критическое значение z = 1,96 связано с ошибки 2,5% на одном конце кривой и 2,5% на другом. Т.е., при общей степени уверенности 95%.

Что вам нужно знать. Ошибка I рода описывается ситуацией, когда мы на основе выборочного исследования признали монету нечестной, хотя на самом деле она была честной (в диапазоне H24:Q24 на рис. 2.6 всего две решки и восемь орлов). Чтобы сократить вероятность ошибки I рода уменьшите α. Ошибка типа II представляет собой ситуацию, когда монета была на самом деле нечестной, но у вас было недостаточно данных, чтобы «поймать» ее (в диапазоне H26:Q6 орлов и решек поровну, хотя монета нечестная). Отличия мы объясняем простой случайностью. Чтобы снизить вероятность ошибки II рода, увеличьте размер выборки n.

Рис. 2.6. Бета и статистическая мощность при α = 2,5%

В ячейке B3 формула =ABS(НОРМ.СТ.ОБР(B2)) возвращает значение z для вероятности, выбранной в B2. Значение z для α=2,5% равно –1,96. Минус, потому что значение вероятности маленькое, т.е. точка находится слева от пика колоколообразной кривой. Левее нуля. В правой части кривой z-значение +1,96 будет соответствовать вероятности 97,5%.

Обратите внимание на часть СТ внутри функции НОРМ.СТ.ОБР(). Это говорит о том, что функция относится к стандартному нормальному распределению с μ=0 и σ=1.

Что вам нужно сделать. Выделите ячейку B2, и пройдите по меню Данные –> Работа с данными –> Проверка данных –> Проверка данных. Настройке проверку значений:

Рис. 2.7. Проверка значений в ячейке ввода α

В ячейке B3 введите: =ABS(НОРМ.СТ.ОБР(B2)). Благодаря функции ABS критическое z-значение в ячейке В3 всегда будет положительным.

В ячейку А6 введите формулу динамического массива, задающую интервалы σ: =ПОСЛЕД(17;;-4;0,5). В ячейку С6 введите формулу ошибки II рода β: =(НОРМ.СТ.РАСП(A6#+B3;ИСТИНА)-НОРМ.СТ.РАСП(A6#-B3;ИСТИНА)). В ячейке B6: =1-C6#.

Тема взаимной зависимости ошибки I рода, ошибки II рода и статистической мощности мне представляется весьма интересной. У меня родилась идея, как иначе организовать симуляцию. Реализую и опубликую ее в ближайшее время. – Здесь и далее текст, набранный с отступом, прим. Багузина.

H24:Q24 имитирует честную монету. Введите в Н24: =ЕСЛИ(СЛМАССИВ(;10;0;1;ЛОЖЬ)>0,5; » О » ; » Р » ). H26:Q26 – для нечестной монеты. В Н26 введите: =ЕСЛИ(СЛМАССИВ(;10;0;1;ЛОЖЬ)>0,3; » О » ; » Р » ). Обратите внимание, честная монета выпадет орлом и решкой 50:50, а нечестная – 30 орлов против 70 решек.

Добавьте условное форматирование для честной монеты, которая 9 или 10 раз выпала одной стороной. Для диапазона H24:Q24: =ИЛИ(СЧЁТЕСЛИ($H$24:$Q$24; » О » )>=9;СЧЁТЕСЛИ($H$24:$Q$24;»О»)<=1). А также подсветите нечестную монеты, если орлов ровно пять. Для диапазона H26:Q26: =СЧЁТЕСЛИ($H$26:$Q$26; » О » )=5.

Увеличьте α до 10%.

Рис. 2.8. Бета и статистическая мощность при α = 10%

Здесь синяя кривая – величина ошибки II рода β, красная кривая – статистическая мощность (1–β). Если сравнить с рис. 2.6, для которого α = 2,5%, видно, что статистическая мощность увеличилась, т.е., шанс обнаружить реальные различия вырос.

Глава 17. Скрытые пики

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

Что вам нужно знать. Популяции обычно включают скрытые подгруппы, которые могут оказать огромное влияние на состав всего населения. Как только кривая популяции теряет симметрию, стандартные статистические процедуры перестают работать. Проверка симметрии кривой может быть выполнена с помощью функции Excel СКОС(). Положительная асимметрия указывает на распределение с хвостом справа от среднего значения. Отрицательная асимметрия указывает на распределение с хвостом слева от среднего значения. Эмпирическое правило гласит, что существенной считается асимметрия меньше –1 или больше +1. Симметрия кривой является важным условием для многих статистических тестов, таких как t-критерий Стьюдента и дисперсионный анализ (ANOVA).

Что вам нужно сделать. В ячейках G2 и G3 с помощью Проверки данных установлен ограниченный набор значений. В ячейке А6 задайте 19 значений от нуля с шагом 10: =ПОСЛЕД(19;;0;10). В ячейке C6: =НОРМ.РАСП(A6#;D2;D3;ЛОЖЬ). D6: =C6#*1000. F6: =НОРМ.РАСП(A6#;G2;G3;ЛОЖЬ). G6: =F6#*1000. I6: =D6#+G6#.

Рис. 2.9. С увеличением среднего значения второй подгруппы асимметрия всей популяции возрастает: а) μ2=44, б) μ2=70, в) μ2=100

Пройдитесь по всем доступным значениям среднего значения субпопуляции2: от 44 до 100 (G2). А также посмотрите, как отражается на графике изменение стандартного отклонения (G3).

Глава 18. Расчет размера выборки

Что делает симуляция. В популяции с неким параметром, имеющим средне значение μ (ячейка B1) и стандартное отклонение σ (B2), использовалось лечение. Делая выборку, мы хотим определить, привело ли лечение к заметным отличиям или нет. Размер отличия он же погрешность он же эффект от лечения – в ячейке В3. Минимальный размер выборки для наблюдения эффекта от лечения с уровнем достоверности 95% рассчитывается по формуле:

Что вам нужно знать. Минимальный размер выборки рассчитывается в ячейке B8. В ячейке E5 он продублирован для использования в Таблице данных. Последняя построена по двум параметрам. Столбцы используют различные отношения погрешности (эффекта) к μ, строки – вариация соотношений σ/μ.

Изменение уровня достоверности (B7) приводит к вычислению новых критических размеров выборок в Таблице данных.

Что вам нужно сделать. В ячейке B5 введите: =B2/B1. B6: =B3/B1. B8: =B5^2*(НОРМ.СТ.ОБР(1-(1-B7)/2)/B6)^2. Значение 1,96 – критическое z-значение для уровня достоверности 95%. Помните, что уровень достоверности 95% оставляет два хвоста по 2,5%, поэтому для вычисления z-значения следует использовать формулу =НОРМ.СТ.ОБР(97,5%).

Рис. 2.10. Минимальный размер выборки для обнаружения эффекта лечения

Ячейка E5: =B8. Выделите диапазон E5:K18. Пройдите по меню Данные –> Анализ » что, если « –> Таблица данных. В поле Подставлять значения по столбцам в: введите В6, а в поле Подставлять значения по строкам в: введите В5. В диапазоне F6:K18 отражается формула массива: <=ТАБЛИЦА(B6;B5)>. Скройте результаты в диапазонах F6:K6 и F7:F18, выделив их белым шрифтом. Эти результаты не представляют интереса.

В таблице добавлено условное форматирование. Красным выделены ячейки со значениями, использованными для построения Таблицы данных (из ячеек В5, В6 и В8). Горизонтальная ось (диапазон $G$5:$K$5): =И($B$6>F$5;$B$6<=G$5). Вертикальная ось (диапазон $E$7:$E$18): =И($B$5>$E6;$B$5<=$E7). Ячейки таблицы (диапазон $G$7:$K$18): =И(И($B$5>$E6;$B$5<=$E7);И($B$6>F$5;$B$6<=G$5)).

Измените уровень достоверности (B7) сначала на 99%, а затем на 90%. Критические размеры выборок в Таблице данных обновятся. Чем выше уровень достоверности мы хотим получить, тем больший размер выборки требуются. Также обратите внимание: чем меньший эффект от лечения мы хотим обнаружить, и чем больше вариабельность в выборке (σ), тем больший размер выборки нужно взять.

Глава 19. Контроль качества

Что делает симуляция. Моделирует работу сборочной линии. Объем партии может изменяться в широком диапазоне. Например, от 100 до 1000 штук (ячейка B1). Номинальное значение μ=15 (B2) подвержено изменчивости σ=2 (B3). Конкретные значения для изделий представлен в столбце А на основе генератора случайных чисел. Для обеспечения качества мы отбираем образцы (процент в E1). Если среди отобранных образцов мене 2% дефектов (E2), мы с уверенностью 95% (E3) принимаем партию. Если процент дефектов больше, мы не принимаем всю партию.

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

Что вам нужно знать. Данные о партии в столбце A сформированы на основе формулы, поддерживающей динамические массивы. Число строк задается размером партии (В1). Для определения наибольшего количества дефектных деталей, которые позволяют не отбраковывать всю партию, используется функция БИНОМ.ОБР().

Что вам нужно сделать. Введите в ячейку A8 формулу динамического массива: =НОРМ.ОБР(СЛМАССИВ(B1;;0;1;ЛОЖЬ);B2;B3). Формула возвращает случайные значения в количестве из В1, распределенные нормально со средним из В2 и стандартным отклонением из В3. A6: =СРЗНАЧ(A8#).

В столбце D мы «отбираем» образцы для контроля качества. Доля отобранных образцов в Е1. Это случайный процесс, поэтому количество образцов может быть как меньше 10%, так и больше. Введите в D8: =ЕСЛИ((СЛМАССИВ(B1;;0;1;ЛОЖЬ)<E1);A8#; » » ). Эта формула проверяет является ли случайное значение в диапазоне от 0 до 1 меньше размера выборки (Е1). Если да, то возвращает соответствующее значение из столбца А (используя неявное пересечение). Если нет, возвращает пустое значение » » .

Читайте также:  Как выделить повторяющиеся значения в Excel разными цветами?

Рис. 2.11. Контроль качества

В столбце Е, начиная с ячейки Е8, мы решаем, является ли образец бракованным или нет. Введите в Е8: =ЕСЛИ(D8#<> » » ;ЕСЛИ((ABS(B2-D8#)/B3)>НОРМ.СТ.ОБР((1+Н1)/2); » отклонить » ; » Ok » ); » » ). Вероятность в Н1 подразумевает, что образцы признаются годными, если отличаются от номинального значения не более, чем на 2,054σ. Для μ=15 и σ=2 годные образцы попадают в диапазон 10,89–19,11.

В ячейках D6:H6 мы анализируем данные по выборке образцов. В D6 находим среднее по выборке: =СРЗНАЧ(D8#). В E6 проверяем, какая доля образцов была отобрана: =СЧЁТЕСЛИ(D8#; » >0 » )/B1. В F6, сколько образцов забраковано: =СЧЁТЕСЛИ(E8#; » отклонить » ). В G6 – каким может быть предельное число забракованных образцов по условиям задачи: =БИНОМ.ОБР(СЧЁТЕСЛИ(D8#; » >0 » );E2;E3). В Н6 решаем принять или отклонить всю партию: =ЕСЛИ(F6>G6; » отклонить » ; » + » ).

Далее мы создаем шаблон для Таблицы данных. Мы ссылаемся из диапазона К6:Р6 на ячейки А6 и D6:H6. Выберите диапазон J6:P27. Пройдите по меню Данные –> Анализ » что, если « –> Таблица данных. Поле Подставлять значения по столбцам в: оставьте пустым, а в поле Подставлять значения по строкам в: кликните на любую пустую ячейку, например, R7. В диапазоне К7:Р27 появятся формулы массива: <=ТАБЛИЦА(;R7)>.

Теперь вы можете «поиграть» значениями в ячейках, выделенными на листе светло коричневым цветом.

Форматирование

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

  1. Шаг 1. Переходите к блоку Стили на главной вкладке, нажимаете кнопку Условное форматирование, опускаетесь вниз и щелкаете по строке Создать правило.

  1. Шаг 2. Появившееся окно содержит шаблоны правил. Выбираете последнюю строку и вписываете формулу для сравнения в специальное поле. Выражение позволяет сравнивать построчно два столбца.

Знак <> обозначает неравно на языке Excel.

  1. Шаг 3. Если формула принимает значение ИСТИНА, то ячейка форматируется заданным образом. Делается это через кнопку Формат. В появившемся окне настраиваете параметры шрифта и ячейки.

  1. Шаг 4. Чтобы подтвердить формат и закрепить правило, дважды нажимаете ОК. После этого разные ячейки подсветятся по заданным условиям.

На заметку! Для изменения диапазона применяемого закона форматирования по заданным условиям, используйте строку Управление правилами.

Как в excel сделать выборку из таблицы по условию?

Выборка данных в Microsoft Excel

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

=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000
Если приходиться работать с большими таблицами определенно найдете в них дублирующийся суммы разбросаны вдоль целого столбца.

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

Как сделать выборку в Excel по условию

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

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

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение.

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

В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

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

Как работает выборка по условию

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам.

ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах.

Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

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

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18.

Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ.

В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

Следующий шаг – это определение в каких именно строках диапазона находится каждое минимальное значение. Это нам необходимо по причине определения именно первого наименьшего значения.

Реализовывается данная задача с помощью функции СТРОКА, она заполняет элементы массива в памяти программы номерами строк листа. Но сначала от всех этих номеров вычитается номер на против первой строки таблицы – B5, то есть число 5.

Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа.

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

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

Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18.

В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

Как выбрать значение с наибольшим числом в Excel

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

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

Как в Excel выбрать первое минимальное значение кроме нуля:

Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами.

Скачать пример выборки из таблицы в Excel.

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

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

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

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

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

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

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

  1. Выделите первый столбец таблицы A1:A19.
  2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
  3. В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
  4. Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

В результате мы получили список данных с уникальными значениями (фамилии без повторений).

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

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

  1. Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
  2. На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
  3. В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

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

Примечание. Если данные для выпадающего списка находятся на другом листе, то лучше для такого диапазона присвоить имя и указать его в поле «Источник:». В данном случае это не обязательно, так как у нас все данные находятся на одном рабочем листе.

Выборка ячеек из таблицы по условию в Excel:

  1. Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
  2. Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

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

Скачать пример выборки из списка с условным форматированием.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel.

Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат.

Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

Первый способ: Применение расширенного автофильтра На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.

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

Когда эти действия выполнены, в шапке таблицы должны появиться пиктограммы для запуска фильтрования. Они будут отображены острием вниз небольшими треугольниками в правом крае ячеек. Нажимаете на этот значок в начале того столбца, по которому и собираетесь сделать выборку. Запуститься меню, в нем переходите в «Текстовые фильтры» и выбираете «Настраиваемый фильтр…».

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

После фильтрации остаются только те строчки, в которых сумма выручки превышает значение 10000 (как пример).

В этом же столбце есть возможность добавить и второе условие. Нужно снова вернуться в окно пользовательской фильтрации, а в его нижней части установить другую границу отбора. Переключатель выставляете в позицию «Меньше», а в поле справа вписываете «15000».

В таблице останутся только те строки, в которых сумма выручки не меньше 10000, но и не больше 15000.

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

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

=СУММ(A2:A10) Суммирует значения в ячейках A2:10.

=СУММ(A2:A10;C2:C10) Суммирует значения в ячейках A2:10, а также ячейки C2: C10.

Первое число для сложения. Это может быть число 4, ссылка на ячейку, например B6, или диапазон ячеек, например B2:B8.

Это второе число для сложения. Можно указать до 255 чисел.

Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.

Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.

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

А теперь попробуйте проверить правильность записей. Гораздо проще поместить эти значения в отдельные ячейки и использовать их в формуле СУММ. Кроме того, значения в ячейках можно отформатировать, чтобы привести их к более наглядному виду, чем если бы они были в формуле.

Ошибки #ЗНАЧ!, если ячейки по ссылкам содержат текст вместо чисел

Допустим, вы используете формулу такого вида:

Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.

Читайте также:  Как возвести число к степени в Excel с помощью формулы и оператора

Ошибка #ССЫЛКА! при удалении строк или столбцов

При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически.

Формулы не обновляют ссылки при вставке строк или столбцов

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

Функция СУММ — отдельные ячейки или диапазоны.

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

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

Такая формула будет обновляться при добавлении и удалении строк.

Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.

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

Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между 8:00 и 12:00 для вычисления заработной платы, можно воспользоваться формулой =(«12:00»-«8:00»)*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на 24. В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов).

Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на 24. Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (5:36, т. е. 5 часов 36 минут).

Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Одновременное выполнение двух условий

Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И».

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

Для этого используем следующую формулу:

=ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)

Нажимаем клавишу Enter, чтобы отобразить результат в ячейке.

Аналогично примерам выше, растягиваем формулу на остальные строки.

Одновременное выполнение двух условий

Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И».

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

Для этого используем следующую формулу:

=ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)

Нажимаем клавишу Enter, чтобы отобразить результат в ячейке.

Аналогично примерам выше, растягиваем формулу на остальные строки.

Как в таблице Excel подсчитать ячейки, которые соответствуют нескольким критериям

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

Некоторые из этих формул используют функцию СЧЁТЕСЛИМН, которая была введена в Excel 2007. В целях совместимости я также предложил альтернативную формулу, которая работает с предыдущими версиями Excel.

Использование критерия И

Критерий И подсчитывает ячейки, если все указанные условия выполнились. Типичным примером является формула, которая считает количество значений, входящих в числовой диапазон. Например, вы захотели подсчитать, сколько ячеек содержат значение, большее 0 и меньшее либо равное 12. Любая ячейка, которая имеет положительное значение, меньшее или равное 12, будет включена в подсчет.

Следующая формула, которая работает только в Excel 2007 и более поздних версиях, считает ячейки, которые имеют значение от 0 до 12, в диапазоне, названном Данные: =СЧЁТЕСЛИМН(Данные;»>=0″;Данные;» .

Аргументы функции СЧЁТЕСЛИМН всегда идут парами. Эта формула имеет два набора парных аргументов: первый аргумент в каждой паре является интересующим вас диапазоном, а второй аргумент — критерием. Значения учитываются для подсчета, если они отвечают всем критериям, указанным для каждого аргумента пары.

Для совместимости с предыдущими версиями Excel используйте такую формулу: =СЧЁТЕСЛИ(Данные;»>0″)-СЧЁТЕСЛИ(Данные;»>12″) .

Данная формула использует разговорный английский и подсчитывает количество значений, больших 0, а затем отнимает количество значений, больших 12. В результате получается количество ячеек со значениями, большими 0 и меньшими либо равными 12. Создание такого рода формул может привести к путанице, поскольку формула ссылается на условие «>12» , даже если цель заключается в подсчете значений, меньших или равных 12. Альтернативный метод заключается в использовании формулы массива, например такой, как следующая (создание формул такого типа вам может показаться проще): =СУММ((Данные>0)*(Данные .

При вводе формулы массива нажмите Ctrl+Shift+Enter, а не просто Enter.

На рис. 116.1 показана таблица, которую я использую для некоторых из приведенных ниже примеров. Здесь приведены данные по объему продаж, категоризованные по таким параметрам, как месяц, агент и тип. Таблица содержит именованные диапазоны, соответствующие меткам в строке 1. Иногда критерий подсчета основан на ячейках, отличных от ячеек, которые подсчитываются. Вы можете, например, захотеть вычислить количество продаж, отвечающих следующему критерию: месяц — январь, агент — Селезнев, объем больше 1000.

Рис. 116.1. Таблица демонстрирует различные методы подсчета, использующие несколько критериев

Рис. 116.1. Таблица демонстрирует различные методы подсчета, использующие несколько критериев

Следующая формула (только для Excel 2007 и более поздних версий) использует три набора парных аргументов для возвращения количества: =СЧЁТЕСЛИМН(Месяц:»Январь»;Агент:»Селезнев»;0бъем;»>1000″) . Для совместимости с предыдущими версиями Excel указывайте следующую формулу для подсчета количества элементов, которые отвечают всем трем критериям: =СУММПРОИЗВ((Месяц=»Январь»)*(Агент=»Селезнев»)*(Объем>1000)) .

Рис. 116.2. Для подсчета строк, отвечающих нескольким критериям, применяется фильтрация

Рис. 116.2. Для подсчета строк, отвечающих нескольким критериям, применяется фильтрация

Если ваши данные находятся в таблице, созданной после вызова команды , вы можете испольВставка ► Таблицы ► Таблицазовать фильтрацию для отображения только тех строк, которые отвечают нескольким критериям. На рис. 116.2 показаны данные, отфильтрованные таким образом, чтобы отображались только январские продажи Селезнева, объем которых превышает 1000. Я также добавил строку Итого к этой таблице, которая показывает количество. Однако стоит отметить, что фильтрация не может быть использована для критерия ИЛИ, который мы обсудим далее.

Использование критерия ИЛИ

Для подсчета ячеек с использованием критерия ИЛИ иногда можно применять несколько функций СЧЁТЕСЛИ. Следующая формула, например, подсчитывает количество экземпляров 1,3 и 5 в диапазоне, названном Данные: =СЧЁТЕСЛИ(Данные;1)+СЧЁТЕСЛИ(Данные;3)+СЧЁТЕСЛИ(Данные;5) .

Вы можете также использовать функцию СЧЁТЕСЛИ в формуле массива. Так, следующая формула массива возвращает тот же результат, что и предыдущая формула: =СУММ(СЧЁТЕСЛИ(Данные;<1;3:5>)) .

Введите формулу массива и нажмите Ctrl+Shift+Enter.

Если вы основываете свой критерий ИЛИ на ячейках, отличных от тех, которые подсчитываются, то функция СЧЁТЕСЛИ не работает (см. рис. 116.1). Предположим, вы хотите подсчитать количество продаж, которые отвечают одному из следующих критериев:

  • месяц — январь;
  • агент — Селезнев;
  • объем больше 1000.

Следующая формула массива возвращает корректное количество: =СУММ(ЕСЛИ((Месяц=»Январь»)+(Агент=»Селезнев»)+(Объем>1000);1)) .

Объединение критериев И и ИЛИ

Вы можете комбинировать критерии И и ИЛИ при подсчете. Возможно, вы захотите подсчитать продажи, которые соответствуют такому критерию: месяц — январь, агент — Селезнев или Петров. Эта формула массива возвращает количество продаж, которые отвечают критерию:
=СУММ((Месяц=»Январь»)*ЕСЛИ((Агент=»Селезнев»)+(Агент=»Петров»);1)) .

Неправильное построение вложенных функций ЕСЛИ

Часто бывает, что необходимо применить более 1 условия.
Допустим нам надо при сумме заказа свыше 15000 руб, сделать скидку 10%, а свыше 20000 руб 15%.

Excel воспринимает до 7 уровней вложения функций и проверяет условия начиная с первого.

Например, мы напишем формулу неправильно:
=ЕСЛИ(B2>15000;B20,9;ЕСЛИ(B2>20000;B20,85;B2))

То есть если мы напишем первым условием В2>15000, то для суммы свыше 20000, скидка 15% применяться не будет.

Например, для ячейки В4 проверится первое Лог_выражение (B4>15000), Excel увидит, что это выражение верно и применит скидку 10%.

Ошибка в построении функции ЕСЛИ

Поэтому начинать будем с суммы заказа больше 20%.

Примеры выбора выполнения формулы по условию пользователя

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

Синтаксис функции ВЫБОР:

Первый аргумент функции ВЫБОР называется «Номер индекса» и определяет, который из очередных аргументов должен быть возвращен через функцию. Данный аргумент может принимать числовые значения от 1-го и до максимального количества последующих аргументов, то есть до 254. Очередные последующие 254 аргумента содержат значения, которое и будет возвращать функция. Если аргумент «Номер индекса» содержит число 1, функция будет возвращать содержимое второго аргумента. А если номер индекса = 2, будет возвращен третий аргумент и т.д.

Последующие аргументы после первого «Номер индекса» называются «Значение1»;«Значение2»… «Значение254». В выше приведенной формуле они определяют 3 способа агрегирования данных по продажам, которые может выбирать пользователь:

  1. Функция СУММ – суммирует числа в диапазоне ячеек.
  2. СЧЁТ – подсчитывает количество непустых ячеек с числами.
  3. СРЗНАЧ – выводит среднее арифметическое значение для диапазона чисел в ячейках.

Значение в первом аргументе функции ВЫБОР вычисляется функцией ПОИСКПОЗ, которая в данном примере возвращает число 1, 2 или 3 в зависимости от того какую позицию в выпадающем списке выберет пользователь в ячейке E6:

Сам выпадающий список заполняется значениями ссылаясь на диапазон ячеек E2:E4.

Если пользователь выберет из выпадающего списка опцию «Сумма», функция ПОИСКПОЗ вернет в результате вычисления число 1 для первого аргумента функции ВЫБОР. В итоге целая формула вернет результат вычисления функции СУММ, которая находится во втором аргументе. Если же пользователь выберет опцию «Количество», то ПОИСКПОЗ вернет число 2, а целая формула результат вычисления СЧЁТ из третьего аргумента.

Как показано выше на рисунке пользователь выбрал опцию «Среднее», поэтому функция ПОИСКПОЗ вернула число 3, а потом ВЫБОР выполнила функцию СРЗНАЧ из четвертого аргумента. В результате целая формула вернула ее среднее значение.

Секреты Excel, 10 лучших и полезных!


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

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

Итак, что же это за возможности:

  1. Зафиксированная ячейка – позволит создать абсолютную ссылку и при сдвиге формулы обеспечит стабильный и точный результат.
  2. Нумерованный список – быстрое создание автоматической и динамической нумерации вашего списка для удобства в работе.
  3. Транспонация значений – это возможность перекидывать ваши данные со строки в столбец и наоборот.
  4. Скрыть или свернуть столбцы и строки – возможность скрыть или свернуть данные вашей таблицы для удобства использования их в работе.
  5. Выпадающий фиксированный список – возможность закрепления за ячейкой или диапазоном ячеек чётко определённые значение и их количество.
  6. Горячи клавши в Excel – список комбинаций клавиш, позволяющие вам производить вам мгновенные операции в программе Excel.
  7. Печать выделенного диапазона – это возможность, которая позволяет в один клик мышки задать вам необходимую область таблицы которую необходимо распечатать.
  8. Настройка панели быстрого доступа – позволит вам создать то меню функций, которые идеально вам подходит для работы с Excel и создаётся вами исключительно для вас.
  9. Сводная таблица – создание интеллектуальной и полуавтоматической таблицы для упорядочивания данных, удобства визуализации информации и вычисления согласно заданных критериев.
  10. Сцепить/отцепить значения в ячейках – позволит вам соединять и разъединять значения и аргументы в том объёме который вам необходим для работы.

А на этом у меня всё! Я очень надеюсь, что все 10 секретов Excel вам пригодятся в работе и станут незаменимыми и удобными. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

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