Формула в excel vpr как суммировать подходящие значения

Обновлено: 20.11.2024

Simtools.xlam — это надстройка для Microsoft Excel. Simtools добавляет статистические функции и процедуры для моделирования методом Монте-Карло и анализа рисков в электронных таблицах. Он также содержит простой инструмент аудита (ранее называвшийся Formlist), который добавляет процедуры для отображения формул любого выбранного диапазона. Simtools был разработан для облегчения применения теории вероятностей и статистики, а также для помощи менеджерам в проведении комплексного анализа решений. Эти программы распространяются как бесплатное программное обеспечение для индивидуального использования и могут свободно распространяться среди студентов и преподавателей академического учреждения. Все остальные права защищены.

Установка SIMTOOLS

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

Затем, чтобы установить Simtools в Excel, используйте последовательность команд File:Options:Add-Ins:Manage ExcelAddIns:Go на компьютере с Windows или последовательность команд Tools:Addins на Mac и выберите «Simtools " в диалоговом окне "Доступные надстройки" или найдите Simtools.xlam, где вы его сохранили. После установки «SimTools» должна появиться в виде вкладки на ленте в Excel. Если он не отображается, вам может потребоваться добавить папку, в которую вы поместили simtools.xlam, в качестве надежного расположения для Excel, выбрав Файл: Параметры: TrustCenter: TrustCenterSettings: TrustedLocations: AddNewLocation.

Для Excel 2007 и более поздних версий: нажмите здесь, чтобы просмотреть файл SIMTOOLS.XLAM, который с марта 2017 года включает Formlist.

Примечание. Если ваш браузер преобразует xlam-файлы в zip-файлы или у вас возникли другие проблемы с загрузкой, нажмите здесь, чтобы загрузить zip-файл, содержащий Simtools.xlam с файлами PDF, документирующими его функции.< /p>

(Чтобы получить только Formlist для Excel 2007 и более поздних версий: FORMLIST.XLAM.)

(Для более старых версий Excel до 2007 года вы можете щелкнуть здесь, чтобы загрузить старый файл simtools.xla и старый formlist.xla. Более старые версии simtools.xla и formlist.xla для более старой версии Excel 5 MS Office также доступны.)

Решение проблем с загрузкой файлов XLA: если ваш браузер запускает Excel вместо сохранения файлов на диск, вам следует щелкнуть правой кнопкой мыши по приведенным выше ссылкам и выбрать параметр «Сохранить как» во всплывающем меню. . Если ваш браузер изменит расширение имени файла .xlam на .xlsx, вы сможете использовать эти файлы после их переименования, чтобы восстановить правильное .xlam имя файла.
Если у вас возникли проблемы с функциями надстроек в книге xls, созданной на другом компьютере, вам следует использовать процедуру редактирования ссылок Excel.

Возможности SIMTOOLS 3.4:

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

Обратные функции кумулятивной вероятности. Эти функции, наряду с функцией Excel НОРМИНОБ или НОРМ.ОБР, можно использовать для генерации случайных величин, когда первый параметр (называемый "вероятность" или "рандпроб") генерируется функцией СЛУЧАЙ() или КОРАНД:

BETINV(вероятность, среднее, стандартное отклонение, нижняя граница, верхняя граница) возвращает обратные кумулятивные значения для бета-случайной величины, параметризованные ее средним значением и стандартным отклонением. Когда первым параметром является RAND, BETINV дает ограниченную случайную величину. (Нижняя и верхняя границы по умолчанию равны 0 и 1.)
БИНОМИНОБ(вероятность, n, p) возвращает обратные кумулятивные значения для биномиальной случайной величины. Если первым параметром является RAND, функция БИНОМИНВ дает ограниченную целочисленную случайную величину в диапазоне от 0 до n со средним значением n*p.
DISCRINV(randprob, values, probabilities) возвращает обратные кумулятивные значения для дискретной случайной величины. Если первым параметром является RAND, функция DISCRINV возвращает дискретную случайную величину с возможными значениями и соответствующими вероятностями в заданных диапазонах.
EXPOINV(вероятность, среднее) возвращает обратные кумулятивные значения для экспоненциальной случайной величины. Если первым параметром является RAND, функция EXPOОБР возвращает неотрицательную случайную величину (часто используется для случайного времени ожидания).
GAMINV(вероятность, среднее, стандартное отклонение) возвращает обратные кумулятивные значения для гамма-случайной величины, параметризованные ее средним значением и стандартным отклонением. Если первым параметром является RAND, GAMINV возвращает неотрицательную случайную величину.
GENLINV(вероятность, кварт1, кварт2, кварт3, наименьшее, максимальное) возвращает обратные кумулятивные значения для обобщенно-логнормальной случайной величины, вероятность которой ниже значения кварт1 (вершина первого квартиль), 50% вероятность ниже четверти 2 и 75% вероятность ниже четверти 3.Обобщенная логнормальная случайная величина представляет собой постоянную плюс или минус логнормальную случайную величину. Когда первым параметром является RAND(), GENLINV дает случайную величину, которая может быть положительной или отрицательной, но ограничена в сторону более узкого диапазона квартилей. Если указаны необязательные минимальное и максимальное значения (удовлетворяющие минимальное

Возможности модуля FORMLIST в Simtools.xlam:

FORMLIST добавляет одну процедуру макроса в меню инструментов Excel:

СПИСОК ФОРМУЛ — это процедура проверки формул, используемых в выбранном диапазоне. В соответствии с этой процедурой формулы из всех ячеек в диапазоне аудита могут быть перечислены в виде текста в любом выбранном столбце. Если ячейка является частью формулы массива, диапазон этого массива отображается с формулой (новая в версии 1.5). Список также может включать имена, определенные в рабочей книге (новое в версии 1.4).
Если выбранный диапазон вывода совпадает с диапазоном аудита, СПИСОК ФОРМУЛ вместо этого переключает формулу/текст, вставляя одинарную кавычку ['] перед начальным знаком равенства [=] в каждой формуле для отображения формул. на месте как текст. Второе применение этого переключателя формулы/текста удаляет эти кавычки, чтобы восстановить формулы. На формулы массива не влияет переключатель формула/текст.

FORMLIST также добавляет две функции Lookup-Reference:

FORMULAS(auditRange) активно отображает текущие формулы в диапазоне аудита в виде массива в столбце. Чтобы отобразить все формулы, функцию ФОРМУЛЫ следует ввести как формулу массива в диапазон в одном столбце, который включает больше ячеек, чем формул в контрольном диапазоне. Слова "ФОРМУЛЫ ИЗ ДИАПАЗОНА" должны появиться под последней отображаемой формулой.
FORMRC(auditCell) возвращает формулу указанной ячейки в нотации в стиле R1C1 (с фигурными скобками в формулах массива). Если ячейка не содержит формулы, FORMRC возвращает значение ячейки (или строку нулевой длины для пустой ячейки)

Ссылки:

Подробнее об использовании Simtools.xlam можно узнать из книги Вероятностные модели для принятия экономических решений, опубликованной MIT Press (2019). Более подробная информация об этой книге также доступна здесь.

Simtools и Formlist — это программное обеспечение с открытым кодом.

Все материалы являются интеллектуальной собственностью Роджера Майерсона.
Все права защищены. © 2010 г., Роджер Майерсон.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

=СУММ(A2:A10) Складывает значения в ячейках A2:10.

=СУММ(A2:A10, C2:C10) Складывает значения в ячейках A2:10, а также в ячейках C2:C10.

СУММ(число1;[число2]. )

Имя аргумента

Описание

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

Это второе число, которое вы хотите добавить. Таким образом можно указать до 255 номеров.

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

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

Опечатки. Представьте, что вы пытаетесь ввести больше и/или гораздо большие значения, например:

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

Если вы используете такую формулу:

=A1+B1+C1 или =A1+A2+A3

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

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

СУММ с отдельными ссылками на ячейки и диапазонами

Используя такую формулу:

=СУММ(A1,A2,A3,B1,B2,B3)

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

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

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

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

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

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

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

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

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

Нужна дополнительная помощь?

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

Допустим, вам нужно просуммировать значения с более чем одним условием, например сумма продаж товаров в определенном регионе. Это хороший пример использования функции СУММЕСЛИМН в формуле.

Взгляните на этот пример, в котором у нас есть два условия: нам нужна сумма продаж мяса (из столбца C) в Южном регионе (из столбца A).

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

=СУММЕСЛИМН(D2:D11,A2:A11,"Юг",C2:C11,"Мясо")

Результатом является значение 14 719.

Давайте более подробно рассмотрим каждую часть формулы.

=СУММЕСЛИМН – это арифметическая формула. Он вычисляет числа, которые в данном случае находятся в столбце D. Первый шаг — указать расположение чисел:

=СУММЕСЛИМН(D2:D11,

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

Далее вы хотите найти данные, которые удовлетворяют двум условиям, поэтому вы вводите свое первое условие, указав для функции расположение данных (A2:A11), а также то, что это за условие — «Юг». Обратите внимание на запятые между отдельными аргументами:

=СУММЕСЛИМН(D2:D11,A2:A11,"Юг",

Кавычки вокруг слова «Юг» указывают, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазона ячеек (C2:C11), содержащих слово «мясо», плюс само слово (заключенное в кавычки), чтобы Excel мог сопоставить его. Завершите формулу закрывающей скобкой ) и нажмите Enter. Результат снова равен 14 719.

=СУММЕСЛИМН(D2:D11,A2:A11,"Юг",C2:C11,"Мясо")

Когда вы вводите функцию СУММЕСЛИМН в Excel, если вы не помните аргументы, под рукой всегда будет помощь. После того как вы введете =СУММЕСЛИМН(, под формулой появится автозаполнение формулы со списком аргументов в правильном порядке.

Глядя на изображение автозаполнения формул и список аргументов, в нашем примере sum_range равен D2:D11, столбцу чисел, которые вы хотите суммировать; Критерий_диапазон1 — это A2.A11, столбец данных, в котором находится критерий 1 «Юг».

По мере ввода остальные аргументы будут отображаться в автозаполнении формул (здесь не показано); Критерий_диапазон2 — это C2:C11, столбец данных, в котором находится критерий 2 «Мясо».

Если нажать СУММЕСЛИМН в автозаполнении формул, откроется статья с дополнительной помощью.

Попробуйте

Если вы хотите поэкспериментировать с функцией СУММЕСЛИМН, вот примеры данных и формула, использующая эту функцию.

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel Starter 2010 Еще. Меньше

Функция СУММЕСЛИМН, одна из математических и триггерных функций, складывает все свои аргументы, соответствующие нескольким критериям. Например, вы можете использовать СУММЕСЛИМН для суммирования количества розничных продавцов в стране, которые (1) проживают в одном почтовом индексе и (2) чья прибыль превышает определенное значение в долларах.

Это видео является частью учебного курса "Расширенные функции ЕСЛИ".

Синтаксис

СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], . )

Имя аргумента

Описание

Sum_range (обязательно)

Диапазон ячеек для суммирования.

Criteria_range1 (обязательно)

Диапазон, проверенный с использованием Criteria1.

Criteria_range1 и Criteria1 создают пару поиска, с помощью которой диапазон ищется по определенным критериям. Как только элементы в диапазоне найдены, добавляются соответствующие им значения в Sum_range.

Критерий 1 (обязательно)

Критерии, определяющие, какие ячейки в Criteria_range1 будут добавлены. Например, критерии могут быть введены как 32, ">32", B4, "яблоки" или "32".

Диапазон_критериев2, критерии2, … (необязательно)

Дополнительные диапазоны и связанные с ними критерии. Вы можете ввести до 127 пар "диапазон/критерий".

Примеры

Чтобы использовать эти примеры в Excel, перетащите их, чтобы выбрать данные в таблице, щелкните их правой кнопкой мыши и выберите "Копировать". На новом листе щелкните правой кнопкой мыши ячейку A1 и выберите "Соответствие форматированию места назначения" в разделе "Параметры вставки".

Проданное количество

Продавец

Описание

=СУММЕСЛИМН(A2:A9, B2:B9, "=A*", C2:C9, "Том")

Добавляет количество товаров, начинающихся с буквы А и проданных Томом. Он использует подстановочный знак * в Criteria1, "=A*" для поиска совпадающих названий продуктов в Criteria_range1 B2:B9 и ищет имя "Tom" в < i>диапазон_критериев2 C2:C9. Затем он добавляет числа в Sum_range A2:A9, которые удовлетворяют обоим условиям. Результат равен 20.

=СУММЕСЛИМН(A2:A9, B2:B9, "<>Бананы", C2:C9, "Том")

Добавляет количество продуктов, которые не являются бананами и продаются Томом. Он исключает бананы, используя <> в Criteria1, "<>Bananas", и ищет имя "Tom" в Criteria_range2 C2:C9. Затем он добавляет числа в Sum_range A2:A9, которые удовлетворяют обоим условиям. Результат 30.

Распространенные проблемы

Описание

Вместо ожидаемого результата отображается 0 (ноль).

Убедитесь, что Criteria1,2 заключены в кавычки, если вы тестируете текстовые значения, например имя человека.

Результат неверен, если Sum_range имеет значения TRUE или FALSE.

Значения TRUE и FALSE для Sum_range оцениваются по-разному, что может привести к неожиданным результатам при их добавлении.

Ячейки в Sum_range, содержащие значение TRUE, оцениваются как 1. Ячейки, содержащие FALSE, оцениваются как 0 (ноль).

Нужна дополнительная помощь?

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

Читайте также:

Формула в excel vpr как суммировать подходящие значения

Установка SIMTOOLS

Возможности SIMTOOLS 3.4:

Возможности модуля FORMLIST в Simtools.xlam:

Ссылки:

Нужна дополнительная помощь?

Попробуйте

Синтаксис

Примеры

Распространенные проблемы

Рекомендации

Нужна дополнительная помощь?