Как посчитать сумму в экселе

Использование функции СРЗНАЧ

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

1. Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
2. В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
3.  На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
4. Получаем результат в выбранной ячейке.
5. Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
6. Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
7. Все готово. Теперь результат выглядит намного привлекательнее.

Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)

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

И так… начнем.

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

Для подсчета товаров в нужной ячейке написали следующую формулу (см. выше):

=СЧЁТЕСЛИМН(B2:B5;«>90»), где:

B2:B5 — диапазон, по которому будут проверять, по заданному нами условию;

«>90» — само условие, заключается в кавычки.

Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.

Формула приобретает вид:

=СЧЁТЕСЛИМН(B2:B6;»>90″;C2:C6;«<20»)

Здесь все осталось таким же, кроме еще одного условия (C2:C6;»<20″). Кстати, таких условий может быть очень много!

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

Как прибавить/вычесть процент к числу в Excel

При расчетах может понадобиться прибавить к какому-либо числу процент. Например, представим, что мы планируем расходы на отпуск. Для того чтобы рассчитать необходимую сумму денег на неделю отпуска, мы закладываем, что в отпуске будем тратить 30% больше чем в обычную рабочую неделю. Для такого расчета нам потребуется формула:

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

Если задача стоит вычесть 20% от числа “100”, то формула выглядит так:

Вернемся к нашей задаче. Запланируем, что на неделю отпуска мы будем тратить на 30% больше чем в регулярную неделю, а неделю после отпуска будем экономить и тратить на 30% меньше чем в неделю до отпуска. Тогда расчет нашего бюджета будет выглядеть так:

Подсчет между датами — Формулы

= РАЗНДАТ (начало; конец; «г»)&»годы»; & РАЗНДАТ(начало; конец; «гм») & «месяцы»; & РАЗНДАТ(начало; конец; «мд») & «дни»

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

В показанном примере формула в D5:

= РАЗНДАТ(B5; C5; «г») & «годы»; & РАЗНДАТ(B5; C5; «гм») & «месяцы»; & РАЗНДАТ(B5; C5; «мд») & «дни»

Где даты начала указаны в столбце B, а даты окончания — в столбце C.

Функция РАЗНДАТ предназначена для вычисления разницы между датами в годах, месяцах и днях. Доступны несколько вариантов (например, время в месяцах, время в месяцах без учета дней и лет и т. д.), И они устанавливаются аргументом «единица» в функции.

В показанном примере мы вычисляем отдельно годы, месяцы и дни, затем «склеиваем» результаты вместе с конкатенацией. Чтобы получить целые годы, мы используем:

РАЗНДАТ(B5; C5; «г») и «годы»

Чтобы получить целые месяцы, мы используем:

РАЗНДАТ(B5; C5; «гм») & «месяцы»

И мы вычисляем дни с:

РАЗНДАТ(B5; C5; «мд») & «дни»

Остальная часть формулы — это просто необходимый текст, объединенный с оператором конкатенации (&).

Чтобы предотвратить вывод дней, месяцев или лет с нулевыми значениями, вы можете обернуть каждую функцию РАЗНДАТ внутри ЕСЛИ, как показано здесь (строки, добавленные для удобочитаемости):

=ЕСЛИ(РАЗНДАТ(B5; C5; «г»); РАЗНДАТ (B5; C5; «г») & «годы»; «») ЕСЛИ(РАЗНДАТ(B5; C5; «гм»); РАЗНДАТ(B5; C5; «гм») & «месяцы»; «»)

ЕСЛИ(РАЗНДАТ(B5; C5; «мд»); РАЗНДАТ(B5; C5; «мд») & «дни»; «»)

Для предотвращения появления дополнительных запятых в конечном результате запятые заменяются пробелами. Использование исходной формулы РАЗНДАТ как «логического теста» внутри ЕСЛИ работает, потому что ЕСЛИ обрабатывает любой ненулевой результат как истинный.

Подсчитайте дни недели между датами

= СУММПРОИЗВ(- (ДЕНЬНЕД(СТРОКА(ДВССЫЛ (дата1 & «:» & дата2))) = дни))

Чтобы подсчитать количество понедельников, пятниц, воскресений и т. п. между двумя датами, вы можете использовать формулу массива, которая использует несколько функций: СУММПРОИЗВ, ДЕНЬНЕД, СТРОКА и ДВССЫЛ.

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

= СУММПРОИЗВ(- (ДЕНЬНЕД(СТРОКА(ДВССЫЛ(B5 & «:» & C5))) = D5))

В основном эта формула использует функцию ДЕНЬНЕД, чтобы проверить количество дат, чтобы увидеть, попадают ли они в заданный день недели и функцию СУММПРОИЗВ, чтобы подсчитать общее количество.

Когда дана дата, ДЕНЬНЕД просто возвращает число от 1 до 7, соответствующее конкретному дню недели. С настройками по умолчанию: 1 = воскресенье и 7 = суббота. Итак, 2 = понедельник, 6 = пятница и так далее.

Трюк этой формулы заключается в понимании того, что даты в Excel — это только серийные номера, которые начинаются 1 января 1900 года. Например, 1 января 2016 года — это серийный номер 42370, а 8 января — 42377. Даты в Excel выглядят только как даты, когда применяется формат числа даты.

Итак, возникает вопрос — как вы можете построить массив дат, который вы можете использовать в функции ДЕНЬНЕД, чтобы узнать соответствующие дни недели?

Ответ: использовать СТРОКА с ДВССЫЛ следующим образом:

СТРОКА(ДВССЫЛ(дата1 & «:» & дата2))

ДВССЫЛ позволяет связанным датам «42370: 42377» интерпретироваться как номера строк. Затем функция СТРОКА возвращает массив вроде этого:

{42370; 42371; 42372; 42373; 42374; 42375; 42376; 42377}

Функция ДЕНЬНЕД оценивает эти числа как даты и возвращает этот массив:

{6; 7; 1; 2; 3; 4; 5; 6}

Который тестируется на заданный день недели (6 в данном случае, начиная с D6). Как только результаты теста преобразуются в 1 и 0 с двойным дефисами, этот массив обрабатывается СУММПРОИЗВ:

{1; 0; 0; 0; 0; 0; 0; 1}

Который возвращает 2.

Подсчитывать праздники между двумя датами

= СУММПРОИЗВ((праздники> = начало) * (праздники =F5)*(B5:B13 = F5)

Это возвращает массив значений ИСТИНА/ЛОЖЬ, например:

{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА }

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

(B5: B13 = F5) * (диап

Виды формул

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

Простые

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

СУММ

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

=СУММ(значение1;значение2)

=СУММ(начало_диапазона:конец_диапазона)

Перемножает все числа в выделенном диапазоне.

= ПРОИЗВЕД(начало_диапазона:конец_диапазона)

Помогает произвести округление дробного числа в большую (ОКРУГЛВВЕРХ) или меньшую сторону (ОКРУГЛВНИЗ).

ВПР

Это – поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.

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

Таблица – диапазон, в котором будет осуществляться поиск.

Номер столбца – порядковый номер столбца, где будет осуществляться поиск.

Альтернативные функции – ИНДЕКС/ПОИСКПОЗ.

СЦЕПИТЬ/СЦЕП

Объединение содержимого нескольких ячеек.

=СЦЕПИТЬ(значение1;значение2) – цельный текст

=СЦЕПИТЬ(значение1;» «;значение2) – между словами пробел или знак препинания

Вычисление квадратного корня любого числа.

=КОРЕНЬ(ссылка_на_ячейку)

=КОРЕНЬ(число)

Альтернатива Caps Lock для преобразования текста.

=ПРОПИСН(ссылка_на_ячейку_с_текстом)

=ПРОПИСН(«текст»)

Преобразует текст в нижний регистр.

=СТРОЧН(ссылка_на_ячейку_с_текстом)

= СТРОЧН(«текст»)

СЖПРОБЕЛЫ

Убирает лишние пробелы. Это будет полезно, когда данные переносятся в таблицу из другого источника.

=СЖПРОБЕЛЫ(адрес_ячейки)

Сложные

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

ПСТР

Позволяет «достать» требуемое количество знаков из текста. Обычно используется при редактировании тайтлов в семантике.

=ПСТР(ссылка_на_ячейку_с_текстом;начальная_числовая_позиция;число_знаков_которое_вытащить)

ЕСЛИ

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

=ЕСЛИ(какие_данные_проверяются;если_значение_отвечает_заданному_условию;если_значение_не_отвечает_заданному_условию)

СУММЕСЛИ

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

=СУММЕСЛИ(C2:C5;B2:B5;«90»)

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

СУММЕСЛИМН

Суммирование чисел на основе нескольких заданных условий.

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

Программа посчитала общую сумму зарплат женщин-кассиров.

По такому же принципу работают функции СЧЁТЕСЛИ, СРЗНАЧЕСЛИ и т.п.

Комбинированные

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

Есть задача: найти сумму трех чисел и умножить ее на коэффициент 1,3, если она меньше 80, и на коэффициент 1,6 – если больше 80.

Решение следующее: =ЕСЛИ(СУММ(А2:С2)<80;СУММ(А2:С2)*1,3;СУММ(А2:С2)*1,6). Если сумма диапазона будет больше числа 80, программа посчитает условие СУММ(А21:С2)*1,3, если больше – СУММ(А21:С2)*1,6.

Комбинированные формулы могут включать 10 и более функций.

Встроенные

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

Чтобы воспользоваться какой-либо функцией, нужно кликнуть по желаемой ячейке, нажать на значок указанной выше опции или посредством комбинации клавиш Shift+F3 вызвать диалоговое окно мастера. В списке найти функцию, клацнуть по ней, в открывшемся окне заполнить поля диапазона (чисел), критерия и выбрать условие.

Программа Microsoft Excel: подсчет суммы

Автосумма

​ После этого, в​ сумма. Кликаем по​Во время работы в​ добавилась функция СУММ().​ Автоматически распознанные диапазоны​ а затем щелкните​ в строке меню​

​ и не хотите​Кв2​ далеко не во​ все колонки таблицы,​и это может,​ которая содержит функцию.​СУММ​ колонки, для вывода​

​ каждой торговой точке.​ как произвести суммирование​

​ указанной ячейке выводится​ кнопке «Вставить функцию»,​ программе Microsoft Excel​ Теперь в ячейках​

​ не всегда являются​Параметры​Excel​ использовать новый вычисляемый​. В результате Excel​ всех случаях. Наиболее​ а только определенные.​ причем решение данной​Затем можно проделать аналогичные​. Предназначение данного оператора​

​ результата суммирования дохода​ Сделать это можно​

​ колонок в Экселе​ общая сумма введенных​ расположенной слева от​ часто требуется подбить​ A4; B4 и​ подходящими для пользователя.​>​выберите​ столбец, нажмите​ создал следующую формулу:​ гибкий вариант –​ Ещё больше задача​ задачи даже легче,​ операции с функцией​ состоит как раз​ за 7 дней​ посредством все той​

​ при помощи такого​ данных.​ строки формул.​

​ сумму в столбцах​ D4 отображается результат​ Их можно при​Формулы и списки​Параметры​Отменить​

Функция «СУММ»

​=СУММ(Таблица1:])​ это применение математических​ усложняется, если они​ чем применение предыдущего​СУММ​ в суммировании чисел.​

​ по первой торговой​ же автосуммы. Производим​ инструмента, как автосумма.​Главный недостаток этого способа​Открывается окно Мастера функций.​ и строках таблиц,​

​ суммирования каждого столбца.​ необходимости быстро и​>​>​два раза. Вы​. Такие формулы называются​

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

​ В списке функций​ а также просто​ Это самый быстрый​ легко поправить.​Таблицы и фильтры​Формулы и списки​ также можете дважды​ формулами со​ наименее автоматизированный и​ с другом. Давайте​Как всегда, выделяем ячейку​ столбцов таблицы, подсчитывая​ категории математических функций​

​ кнопку​ зажатой левой кнопкой​ в которой представлена​ что адрес каждой​ ищем функцию «СУММ».​ определить сумму диапазона​ и удобный метод.​Допустим, нам нужно просуммировать​>​>​ нажать клавиши​структурированными ссылками​ в некоторых случаях,​ рассмотрим, как производится​ на листе, куда​ в них суммы​ и имеет следующий​Enter​ мыши все ячейки,​

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

Использование формулы

​Автоматически заполнять формулы​Поиск ошибок​CTRL+Z​, и их можно​ при большом объеме​ данный вид сложения​ будет производиться вывод​ выручки за 7​ простой синтаксис:​.​ в которых расположена​ магазинов за семь​ отдельно, и нельзя​ жмем на кнопку​ несколько инструментов для​ подставляет формат ячеек​ как показано на​.​.​.​ использовать только в​ данных, его воплощение​

​ при помощи оператора​ итогового результата. Вызываем​ дней для разных​=СУММ(число1;число2;…)​Затем можно проделать подобную​ величина дохода по​ дней. Данные по​

​ выделить сразу целый​ «OK».​ решения данного вопроса.​ которых она суммирует.​ рисунке:​Прекращение автоматического создания вычисляемых​Параметр автоматического заполнения формул​

Просмотр суммы в приложении Microsoft Excel

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

​ Давайте разберемся, как​У меня громадный столбик​Перейдите в ячейку D1​ столбцов​ для создания вычисляемых​ включать формулы, отличающиеся​

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

​ таблицы. Предположим, что​

lumpics.ru>

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

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

1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».

1. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».

1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

Метод 6: функция СЧИТАТЬПУСТОТЫ

В некоторых случаях перед нами может стоять задача – посчитать в массиве данных только пустые ячейки. Тогда крайне полезной окажется функция СЧИТАТЬПУСТОТЫ, которая проигнорирует все ячейки, за исключением пустых.

По синтаксису функция крайне проста:

=СЧИТАТЬПУСТОТЫ(диапазон)

Порядок действий практически ничем не отличается от вышеперечисленных:

1. Выбираем ячейку, куда хотим вывести итоговый результат по подсчету количества пустых ячеек.
2. Заходим в Мастер функций, среди статистических операторов выбираем “СЧИТАТЬПУСТОТЫ” и нажимаем ОК.
3. В окне «Аргументы функции» указываем нужный диапазон ячеек и кликаем по кнопку OK.
4. В заранее выбранной нами ячейке отобразится результат. Будут учтены исключительно пустые ячейки и проигнорированы все остальные.

Использование вложенных функций

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

Затем придерживайтесь следующей инструкции:

1. Кликните на первую ячейку. Вызовите окно «Вставка функции». Выберите функцию «Если». Для вставки нажмите на «OK».

1. Затем нужно будет составить какое-нибудь логическое выражение. Его необходимо записать в первое поле. Например, можно сложить значения трех ячеек в одной строке и проверить, будет ли сумма больше 10. В случае «истины» указываем текст «Больше 10». Для ложного результата – «Меньше 10». Затем для возврата в рабочее пространство нажимаем на «OK».

1. В итоге мы видим следующее – редактор выдал, что сумма ячеек в третьей строке меньше 10. И это правильно. Значит, наш код работает.

=ЕСЛИ(СУММ(B3:D3)>10;»Больше 10″;»Меньше 10″)

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

1. В итоге редактор пересчитывает наше выражение для каждой строки.

Функция СЧЕТЕСЛИ в Excel и примеры ее использования

​ текст «столы» и​ на диапазон в​ совпадать с заголовками​ сразу двум критериям,​ считает ячейки, в​ в Excel​ текстовыми данными! Если​

Синтаксис и особенности функции

​ можно комбинировать в​

• ​ примеры. Допустим, у​Если вы раньше не​ буквы не различаются.​
• ​ с числами больше​ формулу:​Для расчета используем формулу:​

​ для указания. Для​ все табличные элементы​ «Счёт». Игнорируя текстовый​ «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»).​ другой книге, то​ исходной таблицы. Размещение​

​ которые образуют Условие​ которых написано «да».​.​ в ячейке нет​ условии различные функции.​ нас есть список​ сталкивались с такой​Диапазон_суммирования​ 3 и менее​

​В результате расчета получим:​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​

• ​ наглядности стоит рассмотреть​ будут прописаны в​ формат, она учитывает​ Для указания нескольких​ необходимо, чтобы эта​
• ​ условий в разных​ ИЛИ. Например, в​ Получилось три слова​
• ​Например, нужно посчитать​ ни одной буквы,​
• ​Введенный в предыдущем разделе​ овощей и фруктов,​ замечательной возможностью Excel​- это те​ 8 необходимо выполнить​Функция имеет следующую синтаксическую​Описание аргументов:​ следующий пример.​ виде аргументов. Она​ только числовые значения.​
• ​ условий используется несколько​ книга была открыта.​ строках соответствует Условию​ таблице с перечнем​ «да».​

Функция СЧЕТЕСЛИ в Excel: примеры

​ покажет всегда 0​ набор данных поможет​ хранящихся на складе​ как формулы массива,​

​ ячейки, значения которых​ следующие действия:​

​ запись:​C3:C17 – диапазон ячеек,​Пример 3. Есть ведомость​ будет выглядеть так:​Часто, для анализа табличных​ выражений СЧЕТЕСЛИ. Они​

​Аргумент «Критерий» нужно заключать​ ИЛИ (см. статью​ Фруктов и их​Если нужно посчитать​

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

​ то советую почитать​

​ мы хотим сложить,​записать первую функцию СЧЁТЕСЛИ​=СЧЕТЕСЛИ(диапазон; критерий)​

​ содержащих названия фирм​ с фамилиями студентов​

​=СЧЁТ(12; мост; кг; крыло;​ данных не нужно​ объединены между собой​ в кавычки (кроме​ Функция БСЧЁТ()).​

​ количеством на складе,​ процентное отношение ответов​

​ посчитать результат голосования​ в Excel и​ «СЧЕТЕСЛИ» в Excel.​

​ закупочной цены, продажной​ предварительно про них​ т.е. нашем случае -​ с критерием «>3»;​Описание аргументов:​

​ проданной техники;​ и оценками за​

​ 33; 45678; «22»;​ производить никаких математических​ оператором «+».​ ссылок).​Также можно использовать формулу =БСЧЁТА(Таблица;A1;D13:E15)​ отберем строки, в​ «да» по отношению​ или ответов в​ анализ данных, хранящихся​ Примеры, рассматриваемые в​

1. ​ надбавки и датой​ много хорошего здесь.​ стоимости заказов.​записать вторую функцию с​диапазон – обязательный аргумент,​»Samsung» – критерий поиска​ экзамен. В таблице​ сила; ампер; 16.02.1999;​
2. ​ операций с числами.​Условия – ссылки на​Функция не учитывает регистр​ с теми же​ которых в столбце​ ко всем ответам,​ анкете, опросе, провести​ в них, практически​
3. ​ текущем разделе, будут​ закупки. Начинается работа​ Ну, а в​Если условий больше одного​ критерием «>=8»;​ принимающий ссылку на​
4. ​ (точное совпадение);​ 2 столбца и​ 14.07.1975; стена; потолок;​ Достаточно уточнить количество​ ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2).​ текстовых значений.​ условиями, но нужно​ Фрукты значится Персики​ то формула будет​ другой анализ данных,​
5. ​ невозможен без использования​ довольно простыми, однако​ с таблицами в​ нашем случае задача​ (например, нужно найти​определить разницу между возвращаемыми​ одну либо несколько​A17 – ячейка, хранящая​ 10 ячеек. Нужно​ 21)​ показателей. Именно для​ Текст «столы» функция​При формулировании условия подсчета​ заменить столбец для​

​ ИЛИ строки с​ сложнее. Смотрите формулу​ т.д.​ подстановочных знаков.​ они основополагающие, и​ Excel — переносим​ решается одной формулой:​ сумму всех заказов​ значениями =СЧЁТЕСЛИ(A1:10;»>3″)-СЧЁТЕСЛИ(A1:A10;»>=8″). То​

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

​ ячеек, в которых​ номер последней продажи,​

1. ​ определить, какое количество​Введя формулу, мы получим​ такого случая пригодится​
2. ​ ищет в ячейке​ можно использовать подстановочные​ подсчета строк, он​ остатком на складе​ в строке 22​Эта функция нужна​Комбинирование с другими функциями​ рассмотреть их необходимо,​ на рабочий лист​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​

​ Григорьева для «Копейки»),​ есть, вычесть из​

​ требуется определить число​ соответствующий общему числу​ студентов получили отличную​ значение 5.​ эта простая функция​ А1. Текст «стулья»​

exceltable.com>

Часто задаваемые вопросы и проблемы.

Я надеюсь, что эти примеры помогли вам почувствовать функцию Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникла проблема, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.

1. Возможен ли подсчет в несмежном диапазоне клеток?

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

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

Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;»>0″)

Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))

Или же

=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)

1. Амперсанд и кавычки в формулах СЧЕТЕСЛИ

Вопрос: когда мне нужно использовать амперсанд?

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

Итак, вы можете придерживаться этих правил:

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

= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)

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

= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)

Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:

= СЧЕТЕСЛИ(A2:A10;»>»&D2) или = СЧЕТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())

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

Например, = СЧЕТЕСЛИ(C2: C8;»<=5″) и = СЧЕТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.

1. Как сосчитать ячейки по цвету?

Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA.

1. Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.

1. Формула не работает

Проблема: моя формула не работает! Что я сделал не так?

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

Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….

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

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

Ещё примеры расчета суммы:

Как изменить порядок расчета в Excel

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

Например, расчет =2*4+7 сообщает Excel умножить 2 на 4, а затем добавить 7 к значению. Результат этого вычисления равен 15. Заключив операцию сложения в скобках =2*(4+7), вы даете команду Excel сначала просуммировать 4 и 7, а затем умножить сумму на 2. И результатом этого расчета является значение 22.

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

Расчеты в Excel – Изменение порядка расчетов в Excel

Вот так происходят расчеты в Excel.

Как посчитать процент от суммы

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

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

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую…).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

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

Пример решения задач с процентами

PS 

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать.

Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel), и даже не догадывается как быстро это можно сделать…

 по теме приветствуются!

Удачи!

Первая публикация: 21.01.2017

Корректировка: 5.01.2020

Wifi Hack Password («Андроид»)

Заключение

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

Надеюсь что кому-то пригодятся разобранные примеры и помогут ускорить его работу. Удачных экспериментов!

PS

А какие формулы используете вы, можно ли как-то упростить формулы приведенные в статье? Например, на слабых компьютерах, при изменении каких-то значений в больших таблицах, где производятся автоматически расчеты — компьютер зависает на пару секунд, пересчитывая и показывая новые результаты…