Файл: Лабораторная работа Г. Нижнекамск Как задать простое логическое условие в Excel.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 03.12.2023
Просмотров: 145
Скачиваний: 1
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Рис. 26 - СЧЕТЕСЛИ()
Функции Excel для подсчета данных очень полезны и могут пригодиться практически в любой ситуации. Надеюсь, что данный урок открыл для Вас все тайны функций СЧЕТ и СЧЕТЕСЛИ, а также их ближайших соратников – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН. Возвращайтесь к нам почаще. Всего Вам доброго и успехов в изучении Excel.
Суммирование в Excel, используя функции СУММ и СУММЕСЛИ
В этом уроке мы не будем рассматривать, как посчитать сумму в Excel при помощи оператора сложения, автосуммы и прочих инструментов. Сегодня мы разберем всего две функции: СУММ и СУММЕСЛИ. Спешу Вас обрадовать, их функционала достаточно, чтобы решать практически все возможные вопросы суммирования в Excel.
Функция СУММ – простое суммирование ячеек в Excel
Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:
Рис. 27 - Функция СУММ – простое суммирование ячеек в Excel
Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:
Рис. 28 - Функция СУММ – простое суммирование ячеек в Excel
Поскольку функция СУММ поддерживает работу не только с отдельными ячейками, но и целыми диапазонами, то вышеприведенную формулу можно модифицировать:
Рис. 29 - Функция СУММ – простое суммирование ячеек в Excel
Истинная мощь функции СУММ раскрывается, когда необходимо сложить большое количество ячеек в Excel. В примере ниже требуется просуммировать 12 значений. Функция СУММ позволяет сделать это несколькими щелчками мышью, если же использовать оператор сложения, то провозиться придется долго.
Рис. 30 - Функция СУММ – простое суммирование ячеек в Excel
В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:
Рис. 31 - Функция СУММ – простое суммирование ячеек в Excel
Следующая формула подсчитывает сумму всех ячеек, содержащихся на рабочем листе Лист1. Чтобы данная формула не вызвала циклической ошибки, ее необходимо использовать на другом рабочем листе Excel (отличном от Лист1).
Рис. 32 - Функция СУММ – простое суммирование ячеек в Excel
Рис. 33 - Функция СУММ – простое суммирование ячеек в Excel
Если среди суммируемых значений содержится текст, то функция СУММ их игнорирует, т.е. не включает в расчет:
Рис. 34 - Функция СУММ – простое суммирование ячеек в Excel
Если же текстовые значения попытаться сложить оператором суммирования, то формула вернет ошибку:
Рис. 35 - Функция СУММ – простое суммирование ячеек в Excel
Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:
Рис. 36 - Функция СУММ – простое суммирование ячеек в Excel
Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.
СУММЕСЛИ — условная сумма в Excel
Функция СУММЕСЛИ позволяет подсчитать условную сумму в Excel, т.е. сумму ячеек, которые удовлетворяют определенному критерию. Функция СУММЕСЛИ может содержать только один критерий
Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.
Рис. 37 - СУММЕСЛИ — условная сумма в Excel
В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:
Рис. 38 - СУММЕСЛИ — условная сумма в Excel
Меняем условие, меняется и результат:
Рис. 39 - СУММЕСЛИ — условная сумма в Excel
Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.
Рис. 40 - СУММЕСЛИ — условная сумма в Excel
Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?
На этот случай функция СУММЕСЛИ припасла третий необязательный аргумент, который отвечает за диапазон, который необходимо просуммировать. Т.е. по первому аргументу функция проверяет условие, а третий подлежит суммированию.
В следующем примере мы просуммируем общую стоимость всех проданных фруктов. Для этого воспользуемся следующей формулой:
Рис. 41 - СУММЕСЛИ — условная сумма в Excel
Нажав Enter получим результат:
Рис. 42 - СУММЕСЛИ — условная сумма в Excel
Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.
Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!
Как посчитать накопительную сумму в Excel?
На рисунке ниже Вы можете видеть таблицу Excel, в которой представлен объем продаж по дням за январь месяц 2014 года. В ячейке B20 отображается общая сумма продаж за весь месяц, вычисленная с помощью функции СУММ. Требуется посчитать объем продаж на каждую дату января относительно начала месяца, т.е. накопительную сумму.
Рис. 43 - Как посчитать накопительную сумму в Excel?
При подсчете накопительной суммы в Excel, необходимо, чтобы суммирование всегда начиналось с третьей строки (ячейки B3) и заканчивалось рассматриваемой строкой. Например, ячейка C3 должна содержать следующую формулу: =СУММ(B3), а если говорить точнее, то =СУММ(B3:B3), т.е. суммирование диапазона, состоящего из одной ячейки.
Рис. 44 - Как посчитать накопительную сумму в Excel?
Ячейка C4 такую формулу: =СУММ(B3:B4)
Рис. 45 - Как посчитать накопительную сумму в Excel?
Ячейка C19 такую формулу: =СУММ(B3:B19)
Рис. 46 - Как посчитать накопительную сумму в Excel?
Рис. 47 - Как посчитать накопительную сумму в Excel?
Из представленных выше формул становится очевидно, что необходимая нам формула должна использовать смешанную ссылку и выглядеть следующим образом:
Рис. 48 - Как посчитать накопительную сумму в Excel?
Если скопировать данную формулу во все ячейки таблицы, получим следующий результат:
Рис. 49 - Как посчитать накопительную сумму в Excel?
Поскольку значения в ячейках B20 и C19 совпадают, то можно быть уверенным, что накопительная сумма в Excel подсчитана верно. Как видите, все просто!
Вычисляем средневзвешенные значения при помощи СУММПРОИЗВ
Excel превратил вычисление среднего арифметического нескольких ячеек в очень простую задачу – просто используйте функцию СРЗНАЧ (AVERAGE). Но что делать, если некоторые значения имеют больший вес, чем другие? Например, на многих курсах тесты имеют больший вес, чем задания. Для таких случаев необходимо рассчитывать среднее взвешенное.
В Excel нет функции для расчёта средневзвешенного значения, зато есть функция, которая сделает за Вас большую часть работы: СУММПРОИЗВ (SUMPRODUCT). И даже, если Вы никогда не использовали эту функцию раньше, то к концу этой статьи будете работать с ней как профи. Метод, который мы используем, работает в любой версии Excel, а также в других электронных таблицах, таких как Google Sheets.
Подготавливаем таблицу
Если Вы собираетесь вычислять среднее взвешенное, Вам потребуется минимум два столбца. Первый столбец (в нашем примере – столбец B) содержит оценки для каждого задания или теста. Второй столбец (столбец C) содержит веса. Больший вес означает большее влияние задания или теста на итоговую оценку.
Чтобы понять, что такое вес, Вы можете представить его, как процент от итоговой оценки. На самом деле это не так, поскольку в таком случае веса в сумме должны составлять 100%. Формула, которую мы разберем в этом уроке, будет подсчитывать все правильно и не зависеть от суммы, в которую складываются веса.
Рис. 50 - Подготавливаем таблицу
Вводим формулу
Теперь, когда наша таблица готова, мы добавляем формулу в ячейку B10 (подойдёт любая пустая ячейка). Как и с любой другой формулой в Excel, начинаем со знака равенства (=).
Первая часть нашей формулы – это функция СУММПРОИЗВ (SUMPRODUCT). Аргументы должны быть заключены в скобки, поэтому открываем их:
=СУММПРОИЗВ(
=SUMPRODUCT(
Далее, добавляем аргументы функции. СУММПРОИЗВ (SUMPRODUCT) может иметь несколько аргументов, но обычно используют два. В нашем примере, первым аргументом будет диапазон ячеек B2:B9, который содержит оценки.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Вторым аргументом будет диапазон ячеек C2:C9, в котором содержатся веса. Между этими аргументами должен стоять разделитель точка с запятой (запятая). Когда все будет готово, закрываем скобки: