Перейти к содержимому

Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

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

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

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

Рис. 2. Суммирование ячеек, отвечающих одному условию

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

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

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

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:

=БДСУММ($А$1:$А$101,1,Критерий)

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.

Дополнение от 29 марта 2017 г.

Читая книгу Джона Уокенбаха Excel 2010. Профессиональное программирование на VBA я узнал, что, начиная с версии Excel 2010 в VBA появилось новое свойство DisplayFormat (см., например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте Excel-файл с примером (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запустится макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Код процедуры

Хотя пользовательская функция и дает ошибку, но в процессе ее вызова можно «подсмотреть» ответ. В ячейке начните набирать формулу =su…

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

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


[1] Массив я создал с помощью функции =СЛУЧМЕЖДУ(1;100)

220 комментариев для “Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования”

  1. Добрый день.
    По работе как раз таки понадобилось подсчитать число ячеек, отвечающих критерию условного форматирования. Критерием является минимальное значение в строке(=B7=МИН($B7:$J7)). Проблема в том, что в данном случае нельзя использовать функцию СЧЕТЕСЛИ, т.к. условие должно быть текстовой строкой.
    Посоветуйте, пожалуйста, как можно обойти эту проблему?

  2. Юрий, Вы почему-то решили, что…
    > условие должно быть текстовой строкой
    Это не так. Вот, что говорится в справке Excel по функции СЧЁТЕСЛИ:
    Критерий. Обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку или текста или функции, определяющий, какие ячейки необходимо просуммировать.
    Так что используйте функцию, что-то типа:
    =СЧЁТЕСЛИ($B7:$J7;МИН($B7:$J7))

  3. Мне об этом сообщили на другом сайте:
    http://planetaexcel.ru/techniques/2/99/#1523
    Возможно, я неправильно понял.
    Я как раз предлагал там похожий вариант, но с ошибкой в выражении.
    =СЧЁТЕСЛИ($B7:$E7;МИН($B7:$J7)) — функция дала значение «2», такое впечатление она проверила данные только в первой строчке. Смысл в том, чтобы подсчитать количество минимальных значений в строках B7:I7 и т.д. до B30:I30 по каждому столбцу или по четырем, как в моей формуле. Но как бы я не подставлял ссылки, проверяется только первая строка. Не подскажете, в чем у меня ошибка при указании диапазона?

  4. Попробуйте изменить тип ссылок. Возможно, проблема связана с тем, что формула с функцией СЧЁТЕСЛИ некорректно «протягивается». Без реального примера, я не вполне понимаю, чего вы хотите. Пришлите пример мне в личку. Посмотрю…

  5. Добрый день! А есть функция, чтобы можно было просто число заполненных ячеек посчитать, без критериев?

  6. Добрый день. Есть диапазон D2:D31, в нем есть пары ячеек со значениями 16 и 8 (в сумме сутки), надо подсчитать количество ночных часов (8). Загвоздка в том, что в месяце не всегда попадаются целые пары, например : 8, 16 8, …16 8; или 8, 8, 8, …16 8, 16 8, 8, 8, 8, где 8 после 16 это ночные часы, а просто 8 это выход в день, либо ночные часы с предыдущего месяца.

  7. ЕвгенийПарнишаГродно

    Задача:
    В колонке «I» есть буквенные значения: a, b, c, d…
    В колонке «L» соответствуют им числовые значения: 1, 2, 3, 4 …
    А как сложить все числовые значения ячеек в колонке L , которые соответствуют значению из любой ячейки (на пример «Z25»), которая соответствует буквенному содержанию (на пример «a») в колонке «I» ???)))

  8. Василий

    добрый день! есть задача
    приход денежных средств по дням как суммировать этот приход помесячно? например пришли деньги 10.01.14 — 100т.р 13.01.14- 200т.р 20.01.14 -100 т.р и 03.02.14 500т.р 10.02.14 -700 какие критерии должны задаваться что бы в ячейки январь( другая вкладка) считался весь приход января а февральский приход только в ячейки февраль??

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

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

  11. Доброго времени суток!
    Подскажите пжл, как суммировать количество ячеек с текстом(текст разный), нужно только количество заполненных ячеек. Заранее спасибо.

  12. Анна, воспользуйтесь функцией =СЧЁТЗ(). Эта функция подсчитывает количество непустых ячеек в диапазоне.

  13. помогите пожалуйста. Как посчитать количество (сумму ячеек по нескольким критериям один из которых цвет ячейки. нашёл в интернете надстройку скопировал но там тока по цвету. может добавить в нее что нибудь.

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

  15. Наталья

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

    П9175/P9175, CDN6-93984/2065767

    П9175/Р9175, Н-4-097/0018/2082492

  16. Наталья, не очень понятно. Пришлите файл Excel в личку.

  17. Анатолий

    Добрый день.
    Подскажите, как в строке подсчитать кол-во букв (напр. Я), в одной ячейке — одна буква.

  18. Здравствуйте! Прошу помочь.
    Используется: СУММЕСЛИМН(Исходник!D:D;Исходник!A:A;»ПЛАТНЫЕ»;Исходник!B:B;»САМАРА»;Исходник!C:C;»ЛИПЯГИ»), т.е. считается сумма по D:D при условии, что категория пассажира по А:А = ПЛАТНЫЕ и едут от станции В:В = САМАРА, до станции С:C = ЛИПЯГИ.
    Вопрос: Надо посчитать сумму по D:D при условии, что А:А равен ПЛАТНЫЕ или УЧАЩИЕСЯ, есть ли другой способ кроме сложения СУММЕСЛИМН(…)+СУММЕСЛИМН(…)?

  19. Татьяна

    Здравствуйте! Мне нужна формула, которая считает количество чисел из определенного диапазона, такая есть? Пример выделяю столбец чисел. и мне надо количество чисел от 1 до 5.

  20. Может быть, такая формула подойдет?
    =СЧЁТЕСЛИМН(A1:A6;">=1";A1:A6;"<=5")

  21. Добрый вечер. Извините, может за глупый вопрос, но никак не могу справиться. Как подсчитать сумму в столбце А текстового значения «В»? Заранее спасибо.

  22. Алексей

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

  23. Алексей

    Смотрел, но ячейки выделены условным форматированием — и насколько я понял этот метод не работает…

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

  25. Алексей

    спасибо. так уже сделал но думал можно проще…

  26. Здравствуйте.
    В формуле =СЧЁТЕСЛИМН(Sluch!$N:$N; F$59; Sluch!$F:$F; $B60) на месте первого условия стоит число. Можно ли как то уместить там же еще 1 критерий равный F$59*2 или придется суммировать с еще одной такой же формулой но с новым условием?

  27. Нашел решение в виде {=СУММ(ЕСЛИ(; 1;0))} и в условии ЕСЛИ уже выставляю то, что мне нужно

  28. Евгений

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

  29. Наталья

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

  30. Ярослав

    Здравствуйте,
    Прошу подсказать необходимую функцию формулу по заданному условию:
    Существует таблица (ниже) со стандартными товарами, в 1м столбце с повторениями, в зависимости от месяца. В следующих столбцах — наличие товара в различных точках. Как собрать СУММУ по каждому товару (Х) в каждой точке?:
    Точка 1, точка2, точка3,… точка99
    Товар1
    Товар 2
    Товар1
    Товар 3
    Товар1

    Сводной таблицей и созданием дополнительного столбца чз функцию суммесли уже делал, но интересно именно прописать все в ОДНОЙ формуле.
    Спасибо

  31. Ярослав

    Уточнение
    Выборку по товарам (1-2-3) необходимо сделать по нескольким столбцам- например, точка 1, точка, 10, точка 20 и точка 30.

  32. Николай

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

  33. Добрый день! Подскажите, пожалуйста, а если мне необходимо, чтобы критерий для поиска, к примеру "<=8«, само значение критерия (8) из другой ячейки брал, можно это как-то прописать?

  34. Да. Например, так: =СЧЁТЕСЛИМН(A1:A6;">="&D2; A1:A6;"<=«&D3)

    Чтобы увеличить изображение кликните на нем правой кнопкой мыши, и выберите Открыть картинку в новой вкладке

  35. Подскажите, пожалуйста как посчитать количество в нескольких столбцах по заданному критерию? (Одно значение с одного столбца легко получаю с помощью впр). а как сумму с нескольких….?
    Спасибо заранее 🙂

  36. Елена, придумал решение с помощью функции ДВССЫЛ. Не могу сказать, что оно изящное… но работает.
    =СУММ(ДВССЫЛ("B"&ПОИСКПОЗ(C10;A1:A8;ЛОЖЬ)& ":"&"E"&ПОИСКПОЗ(C10;A1:A8;ЛОЖЬ)))
    Если вы не знакомы с этой функцией, почитайте Примеры использования функции ДВССЫЛ (INDIRECT)

    (Чтобы увеличить изображение, кликните на нем правой кнопкой мыши, и выберите опцию Открыть картинку в новой вкладке)
    См. также Excel-файл

  37. Подскажите пожалуйста, как посчитать сумму кратных 3-м, имея столбец неких чисел

  38. Ирина, допустим числа расположены в столбце А. Используйте формулу
    =СУММПРОИЗВ(A:A*(ОСТАТ(A:A;3)=0))

  39. Добрый вечер, спасибо, очень полезный марериал. «А у вас есть такой же, только с перламутровыми пуговицами?»))
    Подскажите пожалуйста, возможно ли суммирование только тех чисел из колонки B, которым в строке A соответствует текст выровнянный слева С ОТСТУПОМ 2 ЗНАКА (а без отступа чтобы не суммировал)? То есть может ли программа различать по такому критерию?
    Спасибо заранее!

  40. Сергей, если отступ реализован с помощью нескольких пробелов, то подойдет формула
    =СУММЕСЛИ(A1:A2;" *";B1:B2)
    Здесь перед звездочкой стоят два пробела.

    См. также Excel-файл

  41. Добрый день! Подскажите пожалуйста, как преобразовать определённые числа, а именно числа в диапазоне от 0 до 98,4 в о, а от 98,5 и выше в (например) 15. Выручите пожалуйста.

  42. Например, так: =ЕСЛИ(A1<98,5;0;15). Имеется ввиду, что значение которое нужно преобразовать, находится в ячейке А1.

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

  44. Excel 2002, даты идут вразнобой.
    Или хотя бы синтаксис употребления дат в логических формулах (если такое вообще предусмотрено). Заранее большое спасибо

  45. Лион, на мой взгляд, наилучший вариант — использовать сводную таблицу. Альтернатива — функция СУММЕСЛИМН. Здесь 42005 соответствует 1 января 2015 г., а 42035 — 31 января 2015 г.

    См. также Excel-файл.

  46. Большое спасибо. Попробовал оба способа:
    По первому способу:
    Сделал PivotTable, но получилось, что просуммировалось количество чеков в периодах, а не суммы платежей. Подскажите, пожалуйста, как с ней управляться.
    По второму способу:
    не смог найти таблицу соответствия дат и их числовых значений. Если такая есть, подскажите, пожалуйста, где её можно посмотреть или как её можно сделать.

    Ещё раз огромное спасибо

  47. Лион, вам нужно изменить настройку сводной таблицы, чтобы она считала не количество, а сумму. Почитайте, например, Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013.
    Что касается соответствия дат и чисел, такой таблички нет. Но она и не нужна. Сначала внесите в ячейку дату, а потом выберите для ячейки формат «числовой».

  48. + в версии 2002 года этот отчёт не работает (только просмотр)
    (

  49. Треба написати формулу: в якій необхідно підрахувати ячейки-особи, котрі народилися певного місяця та певного року, якщо відомо повну їх дату народження (ДЕНЬ, МІСЯЦЬ, РІК). Наприклад: скільки чоловіків, мешканців сіл народилися в травні 1980 року. Крім того, є ще ряд умов які потрібно додавити.
    Дякую за допомогу

  50. Прусов Николай

    Я инженер ПТО.
    В перечне материалов мне нужно подсчитать общее количество кирпича керамического в разных строках перечня. Как это сделать?
    СУММЕСЛИ(А1:А100;"кирпич керамический")?
    Так? Помогите!

  51. Здравствуйте, я веду табеля и мне необходимо высчитать общие часы с условием,что 8\4=12.
    У меня не получается, выхолит или 2 или выдает 8 апреля. Помогите составить правильно формулу.
    Спасибо.

  52. =(СЧЁТЕСЛИ(R[1]C[-38]:R[1]C[-8];"8/4")*12+ СЧЁТЕСЛИ(R[1]C[-38]:R[1]C[-8];"8/")*8+ СЧЁТЕСЛИ(R[1]C[-38]:R[1]C[-8];"/4")*4+ СУММ(R[1]C[-38]:R[1]C[-8])) это моя формула,чтоб было ясно о чем я говорю.

  53. Николай, немного не так:

    Здесь диапазон A1:A100 — в котором вы суммируете, а диапазон B1:B100 — в котором проверяете на совпадение с кирпичом.

  54. Елена, вы специально используете стиль формул R1C1? Как правило всё же используют стиль А1. Этот параметр можно установить, пройдя по меню Файл -> Параметры -> Формулы. Пришлите в личку Excel-файл, попробую помочь.

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

  56. Елена, если я правильно понял ваш вопрос, могу предложить решение с помощью функции СУММЕСЛИ:

    См. также Excel-файл

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

  58. я дополнительный столбец с единичками делал и через суммесли

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

  60. Алексей

    Здравствуйте. Мучаюсь уже битый час — не могу разобраться с вопросом: как присвоить ячейке определенную метку/категорию (не выделяя цветом)? Т.е. у меня есть определенные ячейки в разных местах, на разных листах; нужно эти ячейки «пометить» — присвоив категорию, например, «транспортные расходы», что бы потом получить общую сумму чисел в этих «помеченных» ячейках. Выручите пожалуйста!

  61. Александр

    Здравствуйте! У меня схожий вопрос: Я инженер ПТО.
    В перечне материалов мне нужно подсчитать общее количество кирпича керамического в разных строках перечня. Как это сделать?
    СУММЕСЛИ(А1:А100;"кирпич керамический")?
    Так? Помогите!» только мне нужно чтобы считались только по словам керамический, силикатный, и т.п., а слово кирпич при этом не затрагивались???

  62. Добрый день.

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

    Либо как подсчитать то же количество ячеек в строке, но при условии, что считаться будут только те ячейки, которые меньше определенного числа из соседней ячейки?

    Спасибо.

  63. Жанна, идея заметки, как раз в том и состоит, что Excel не может использовать цвет (или иной признак) условного форматирования для суммирования. Нужно использовать то же правило, что и в условном форматировании, и «вложить» это правило в функции СУММЕСЛИ или СУММЕСЛИМН (если правил несколько).

  64. Калі ласка, ніжэй — варыянт макраса, які гэтае робіць.
    --------------------------------------------------
    Sub m_Add_Selection_to_Name()
    'Add s elected range to existing range named "transport".
    'When range with the name "transport" is not exist now -- it will be created.
    Const myRangeName = "transport"
    On Error Resume Next
    Dim myRange As Range, myNewRange As Range
    '
    Set myNewRange = Union(Range(myRangeName), Selection)
    If Err Then
    'When range with the name "transport" is not exist now -- it will be created.
    Set myNewRange = Selection
    MsgBox "New named Range will be created!"
    End If
    ActiveWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNewRange
    End Sub
    —————————————————

    Камэнтар:
    1. Макрас далучае вылучаныя каморкі да вобласьці з назовам «transport», калі такая ўжо існуе, ці стварае гэтый назоў для вылучэньня.
    Sic! Назоў «transport» (ці які патрэбны) задаецца ў тэксту макраса, праз канстанту myRangeName = «transport».
    2. Сумму ўсік каморкаў, што належаць да вобласьці, лічыць формула: =СУММ(transport)
    Удачы!

  65. Добрый день!
    Все предлагают случаи сложения каждой N-ой ячейки в столбце.
    А как сложить N-ые ячейки в строке?
    Напр.: D10+G10+J10+… т.д. Т.е. сумму ячеек через 2 (3,4,…).

  66. Сергей, могу предложить формулу массива: =СУММПРОИЗВ(A3:L3;--(ОСТАТ(СТОЛБЕЦ(A3:L3); $A$1)=0)).

    Здесь в ячейке А1 расположен делитель, а суммирование идет по диапазону А3:L3. См. также приложенный Excel-файл. Подробнее о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

  67. Добрый день.
    Возможно есть все же вариант формулы СУММЕСЛИМН, которая ищет значение в диапазоне условий с форматом ячейки «ДАТА».
    То есть — колонка даты, формат дата (диапазон условий).
    Колонка — суммы оплаты — формат числовой (диапазон суммирования)

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

  68. Евгения

    Подскажите, пожалуйста, как посчитать значения по нескольким условиям? Мне необходимо посчитать кол-во продаж указанных на второй странице (с 01.09 по 20.09) при совпадении нескольких условий (BBB и Яблоки)+(BBB и Зеленые яблоки)+(ВВВ и Красные яблоки).
    =СУММЕСЛИМН(Лист2!C:H;Лист2!A:A;Лист1!A3;Лист2!B:B;{"Яблоки";"Зеленые яблоки";"Красные яблоки"})

  69. Евгения, я не совсем понял ваши ссылки на разные листы, поэтому привожу решение, где все данные на одном листе:
    =СУММЕСЛИМН(I2:I25;A2:A25;"ВВВ";B2:B25; "*Яблоки")

    Поскольку Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования (см. справку по функции), я добавил столбец Итого.
    См. также Excel-файл.
    Если я ответил не на тот вопрос, пришлите Excel-файл в личку. Подробно укажите, что требуется. Постараюсь помочь.

  70. Евгения

    Спасибо большое за вашу помощь. Подскажите, как прописать несколько условий?
    Не всегда в названии присутствует одинаковое слово «яблоки» (я для примера написала фрукты, чтоб было понятно).
    Как прописать, чтоб формула считала три фрукта, те которые я укажу. В вашем примере: ВВВ сливы + ВВВ черешни + ВВВ зеленые яблоки.
    Заранее благодарю за вашу помощь!

  71. Евгения, не придумал ничего лучшего, чем просуммировать три фрагмента:
    =СУММЕСЛИМН(I2:I25;A2:A25;»ВВВ»;B2:B25;B5)+ СУММЕСЛИМН(I2:I25;A2:A25;»ВВВ»;B2:B25;B10)+ СУММЕСЛИМН(I2:I25;A2:A25;»ВВВ»;B2:B25;B3)

    См. Excel-файл

  72. Добрый день, подскажите пожалуйста как можно суммировать диапазон цифр в колонке B при условии если в колонке E стоит 1 т.е. надо суммировать цифры попадающие от 1 до 1 по колонке E, и в клонке D посчитать количество записей.
    A B C D E
    01.08.2016 2 100 000,00 6 250 000,00 3 1
    15.08.2016 1 550 000,00
    31.08.2016 2 600 000,00
    02.08.2016 0,00 7 800 000,00 5 1
    06.08.2016 0,00
    08.08.2016 2 600 000,00
    16.08.2016 2 600 000,00
    25.08.2016 2 600 000,00
    30.08.2016 3 500 000,00 3 500 000,00 1 1
    05.08.2016 2 600 000,00 7 800 000,00 3 1
    12.08.2016 2 600 000,00
    24.08.2016 2 600 000,00
    05.08.2016 5 000 000,00 15 000 000,00 3 1
    12.08.2016 5 000 000,00
    20.08.2016 5 000 000,00

  73. Павел, если я правильно понял задачу, то воспользуйтесь формулой =СУММЕСЛИ(E1:E15;1;B1:B15) для суммирования значений в столбце В при условии, что в столбе Е единица.
    Функция =СЧЁТЕСЛИ(E1:E15;1) подсчитает число таких единиц.

  74. Добрый день! Помогите пожалуйста. Нужно определить % повторных договоров у каждого клиента до 09.01.2015 и % повторных клиентов в общем количестве.
    Дата Договор Клиент
    05.01.2015 4841/2015 Катя
    06.01.2015 4842/2015 Даша
    06.01.2015 4843/2015 Даша
    11.01.2015 4844/2015 Даша
    09.01.2015 4847/2015 Маша
    09.01.2015 4845/2015 Юля
    10.01.2015 4848/2015 Виктор
    12.01.2015 4849/2015 Костя

  75. Мария, на мой взгляд, для решения ваших задач лучше всего подходят сводные таблицы. Правда, в вашем примере нет повторных договоров — все уникальные)) И что значит % повторных клиентов? Может быть, это все вхождения, начиная со второго? В этом случае доля повторных клиентов — 33%

    См. также Excel-файл

  76. Baguzin, помогите пожалуйста! Есть число, к нему нужно прибавить (или отнять) случайное число так чтобы сумма была, ну например, от 4 до 10. Может ли случайное число поменяться, если не выполняется данное условие???

  77. Baguzin,спасибо! Не буду себе голову ломать….

  78. Добрый день!
    Подскажите, пожалуйста, как сделать так, чтобы если ячейка А заполнена, то дальнейшие вычисления производятся по ней, если пустая, то по соседней заполненной ячейке? Спасибо!

  79. Например, так: =ЕСЛИ(A1″»;A1;B1). Подразумевается, что проверяется ячейка А1, и если она не пустая, то возвращается ее значение, а если пустая, то значение В1.

  80. Дмитрий

    Доброго времени суток!
    Подскажите пожалуйста как, сделать некоторые действия:
    1) имеем ячейку со значением 2- тогда мы применяем расчет дважды по одной и той же формуле, но с подстановкой значения которое нашлось раньше, если число 3, тогда трижды ну и т.д.;
    2) как сделать авто заполнение нескольких ячеек в зависимости от числа в другой ячейке, диапазон 5-6 чисел максимум;
    3)как можно сделать (хотя бы не автоматическое) округление чисел, например имеем число 20,354 надо округлить до 25, а если 26,05 то округляем до 32.
    Заранее очень благодарен.

  81. Дмитрий, пришлите Excel-файл на s_bag@mail.ru. И подробнее опишите, что вам нужно. Пока не очень понятно…

  82. Добрый день. Может поможете — есть массив допустим A4:D50 все ячейки постепенно заполняются разными цифрами, есть ли формула которая выделит ячейки по заданной сумме как только таковая будет совпадать.
    Например нужно в сумме 100 — и как только в ячейках впишутся цифры сумма которых равна 100 , они бы выделились 🙂 Заранее спасибо.

  83. Антон, если я правильно понял, то предлагаю решить с помощью условного форматирования. Выберите диапазон А4:D40 и задайте правило форматирования на основе формулы:
    =(A4<>"")*(СУММ($A$4:$D$50)=100)=1

    Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
    См. также Excel-файл

  84. Добрый день, подскажите вот у меня таблица, разные года (начиная с 2006 по 2016) много данных на каждый год, каждый месяц. Как через функцию сегодня выставить формулу так чтоб показывали данные только за выбранный год)
    Напимер стоит число 28.11.2016 — то все данные за 2016, если поставить число — 15.08.2010 — то все данные за 2010 год?

  85. Дмитрий

    Здравствуйте,очень нужна помощь.
    У меня есть 7 таблиц (в одном файле эксель) которая включает: название, день недели и итог, вот как мне посчитать «ИТОГ» всех 7-ми таблиц и вместить всё в одну таблицу (в каждой таблице «НАЗВАНИЯ» не по порядку, как например в первой таблице, а все в разброс.
    То есть, из всех этих таблиц (с итога) нужно впихнуть все в одну. (можна сказать в один итог). Просто названия в разброс, вот например, в 1 таблице: апельсины, бананы, киви, а в 2 таблице уже: бананы, мандарины, апельсины.

  86. Дмитрий

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

  87. Подскажите, пожалуйста, как мне суммировать ячейки из диапазона по определенным условиям, при этом исключить ячейки со значением #Н/Д?
    Я использую функцию СУММЕСЛИМН(), но никак не могу исключить ячейки с ошибкой. Если таких ячеек нет, то все ОК ,а вот если есть, то и результат — #Н/Д

  88. Спасибо. Сам до этого искал, но так и не нашел.

  89. Подскажите пожалуйста, у меня есть таблица, в первом столбце наименование деталей а,б,в, для каждой детали во втором столбце указаны операции 1,2,3,4, для каждой операции указан цех и норма времени. Как просуммировать норму для каждой детали внутри одного цеха? Сложность в том, что бывают такие ситуации, когда, допустим, первая операция выполняется в первом цехе, вторая — во втором, в третья — вновь в первом. Понимаю, что нужно как-то использовать СУММЕСЛИМН, но точный синтаксис не соображу.

  90. Александр

    Доброго времени суток. Подскажите в строке рабочего времени необходимо посчитать число рабочих часов исходя из 8 часов на каждый день, сложность в том, что есть ячейки где стоят цыфры больше 8.
    Часы которые больше 8 (9-8=1час) занести в другой столбец. Спасибо.

  91. Как подсветить наименьшее число в таблице в каждой строке?

  92. Андрей,
    1) выделите диапазон, с которым вы хотите работать
    2) пройдите по меню Главная -> Условное форматирование -> Создать правило -> Использовать формулу для определения форматируемых ячеек
    3) введите формулу, как указано на рисунке (с особым вниманием отнеситесь к комбинации абсолютных и относительных ссылок)
    4) задайте желательное форматирование для выделяемых ячеек
    5) нажмите Ok

    См. также Excel-файл

  93. Добрый день.
    Необходимо узнать количество деталей с данным номером детали. Пример того как должно считать https://yadi.sk/i/gocQmzJe3FyMgm Т.е., например, есть детали с номером 433300000027 — надо посчитать сколько их в файле. Номеров деталей сотни, вручную много времени уходит по отдельности суммировать. Да и вероятность ошибки велика, когда глаза уже разбегаются…
    https://yadi.sk/i/PfMLVaw73FyMgz

  94. Иван, воспользуйтесь функцией =СЧЁТЕСЛИ().

  95. Доброго времени суток!
    Работаю с формулой СУММЕСЛИМН, подскажите, пожалуйста, есть ли возможность задать условие для диапазона ячеек с частичным содержанием текста?
    Например нужно подсчитать металл толщиной 4 мм, но разных размеров:
    -4(50*100)
    -4(120х200)

    Спасибо!

  96. Доброго времени суток!
    Работаю с формулой СУММЕСЛИМН, подскажите, пожалуйста, есть ли возможность задать условие для диапазона ячеек с частичным содержанием текста?
    Например нужно подсчитать металл толщиной 4 мм, но разных размеров:
    -4(50*100)
    -4(120х200)

    Спасибо!

  97. Арсений

    Здравствуйте!
    Помогите, пожалуйста, разобраться в вопросе. Заранее оговорюсь, что в моём экселе столбцы не буквенные а номерные — 1,2, 3,….26.. В столбце 10 прописан код классификатора. Таких кодов у меня 7 штук и они находятся в разных строках столбца 10. В столбце 26 у меня есть число (цена позиции). Мне необходимо подсчитать общую цену столбца 26 в соответствии с кодом классификатора. Как пример: стобец 10, в строке 2 стоит код 221, в столбце 26 указана цена 100 р. Такой же код находится в строке 10 и имеет цену в столбце 26 150 р. Рассчитываю, что формула будет брать значение из столбца 10 и суммировать данные из столбца 26… буду очень признателен за помощь в определении этой формулы.

  98. Юлия, попробуйте формулу =СУММПРОИЗВ(НЕ(ЕОШИБКА(НАЙТИ(B7;A2:A5))) *B2:B5), как на рисунке ниже. См. также Excel-файл.

  99. Арсений,
    > в моём экселе столбцы не буквенные а номерные
    Если вы это сделали не специально, рекомендую вернуться к обычноому формату — А1. Для этого пройдите по меню Файл -> Параметры, перейдите на вкладку Формулы, и снимите галочку Стиль ссылок R1C1.

    Что касается вашей проблемы, рекомендую функцию СУММЕСЛИ. Судя по описанию, она справится))

  100. Арсений

    Будьте так любезны, напишите формулу в её натуренатуре. я так понимаю, её начало такое =СУММЕСЛИ(221;J2:J1000;(СУММ(Z2:Z100))

  101. Добрый вечер. Можете помочь, мне нужно создать такую формулу. Например, на первом листе (Фамилия и вид деятельности) деятельность разного вида. На последующих листах каждый вид деятельности отдельно вместе с фамилиями указанными на первом листе, такое типа сортировка. Буду признателен.

  102. Арсений

    спасибо за рекомендацию. я взял 0,3 и таки добил формулу)
    =СУММЕСЛИ(J2:J1000;"221"; Z2:Z1000)

  103. Николай

    Здравствуйте! Помогите, пожалуйста, написать формулу (или хотя бы подсказать направление, в котором нужно искать решение). В ячейках A1:A10 находятся числовые значения. Ячейки B1:G10 могут быть как пустыми, так и содержать числа. Требуется найти сумму всех значений из тех ячеек диапазона A1:A10, для которых строка B:G из диапазона B1:G10 не содержит ни одного числового значения. Иными словами, если, к примеру, хотя бы одна из ячеек B3:G3 содержит числовое значение, то значение ячейки A3 не должно войти в итоговую сумму; а если, например, все ячейки B5:G5 пустые, то значение ячейки A5 входит в итоговую сумму. Конечно, можно было бы добавить формулу в столбец H, где ячейка H1 содержит формулу =Ч(СЧЁТ(B1:G1)>0) и т.д., а затем сумму найти по формуле =СУММПРОИЗВ(A1:A10;H1:H10). Но в этой задаче есть некоторые требования: 1) Искомая сумма должна быть выражена одной формулой; 2) Должна быть возможность увеличить количество строк путем вставки новой строки между 1 и 10 строкой; 3) Также не должно приводить к ошибкам, если добавить новый столбец между B и G. Заранее Вам благодарен!

  104. Николай

    Неким аналогом могла бы быть формула =СУММЕСЛИМН(A1:A10;B1:B10;"";C1:C10;"";D1:D10; "";E1:E10;"";F1:F10;"";G1:G10;"")
    Но она не масштабируема в том смысле, что если вставить новый столбец между B и G, то формулу придется править руками, добавляя новый столбец в аргументы функции СУММЕСЛИМН

  105. Николай, это интересная творческая задача)) Простого решения, скорее всего, нет. Можно написать функцию на VBA. Возможно, справятся формулы массива; см., например, Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel. Возможно будет полезно ввести имена диапазон, определенные пользователем. Одно — для диапазона А1:А10, второе — для В1:G10.

  106. Алексей

    Добрый день! Подскажите, как мне правильно посчитать общее количество по всем типам элементов, входящих в каждый катер? Например вручную для самого первого Катера сумма будет 28. Но вот как создать формулу, которая бы подсчитывала сумму для каждого из Катеров, ведь типы элементов постоянно разнятся? Заранее спасибо!

    Катер
    болт — 4
    гайка — 7
    веревка — 2
    доска — 15

    Катер
    болт — 2
    доска — 3

    Катер
    веревка — 8
    болт — 3
    гайка — 7

  107. Дмитрий

    Добрый день!
    Как реализовать суммирование яйчеек «Количество портов» только во 2ом подъезде, потом в 5ом, и т.д. Вот только чтобы данную функцию можно было растянуть по всему файлу и она вывела бы сумму в определенную яйчейку «Итого». Спасибо.
    Подъезд Количество портов Итого
    2 12 17
    2 5
    5 2 8
    5 6
    1 24 25

  108. Дмитрий, судя по описанию, с вашей задачей справится функция СУММЕСЛИ.

  109. суммируешь в эксель по формуле например 10,12+6,37=16,49. а компьютер выдает 16,50, что делать

  110. Ольга, это ошибка округления. Excel отражает значения с одной (заданной) точностью, а в памяти хранит числа с большим числом знаков после запятой. В арифметических операциях Excel использует числа максимально доступной точности. I6=H6+I5. На верхней части рисунка числа отображаются с двумя знаками после запятой, на нижней — с тремя:

    Ольга, если у вас проблема с налоговй отчетностью, рекомендую Excel для бухгалтера: исправление ошибки округления.

  111. Виктория

    Здравствуйте! Подскажите как из столца с фиксированными числами числа от 20 до 30 найти перенести в другой столбец автоматически? Заранее благодарю!

  112. Арсений

    Здравствуйте!
    Прошу помочь разобраться в следующем.
    Итак, я считаю сумму затрат для раздела с кодом п.4 по следующей формуле =СУММЕСЛИ(D7:D106;"п.4";Z7:Z106), где в диапазоне D:D находится п.4, а в диапазоне Z:Z затраты.
    далее, по формуле =СУММЕСЛИ(J7:J106;"221";Z7:Z106) я нахожу траты по разделу, имеющий код 221. У меня несколько разделов с разными кодами.
    вопрос: как мне объединить п.4 и 221 и вычленить по ним затраты из общей таблицы расходов? Да, можно в ручную плюсиками прописать весь документ, но это очень длительный процесс.
    Прошу помочь в моей проблеме…

  113. Арсений

    то есть, при условии того, что в одном столбце находится п.4, в другом столбце находится 221, то берется сумма из третьего столбца (в моём случае это Z). И таким образом ведется подсчет по всем строкам документа.

  114. Арсений

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

  115. Арсений

    =СУММЕСЛИМН(X7:X106;J7:J106;"221";D7:D106;"п.4") допетрил.

  116. Добрый день!

    Подскажите, пожалуйста, как сделать так, чтобы на ЛИСТЕ 1 (ОБЩЕЕ) выводилась сумма моделей каждого размера с ЛИСТА 2 (СКЛАД) и ЛИСТА 3 (МАГАЗИН)?

    Во всех листам у меня в строчку записаны номера моделей (1-ый столбец) и расцветка (2-ой столбец), а столбик записаны номера размеров. Так вот, мне необходимо, чтобы в 1 листе у каждой модели с определённой расцветкой была сумма (остатков) со склада и с магазина? Как это можно сделать? Заранее спасибо!

  117. Подскажите, как подчитать количество не пустых ячек в диапазоне А1:D20, для критерия Х, если критерий несколько раз встречается только в столбце А1:А20
    Спасибо

  118. Рита, это непростая задача для формулы в одной ячейке. Мне сходу не удалось ее решить. Если вы можете себе позволить создать вспомогательный столбец, то решение относительно простое. Допустим, вам нужно суммировать непустые ячейки в диапазоне B1:D20 только в тех строках, для которых значения в А1:А20 равно единице. Используйте вспомогательный столбец Е. В Е1 разместите формулу =ЕСЛИ(A1=1;СЧЁТЗ(B1:D1);0), и протащите ее до Е20. Далее суммируйте значения в столбце Е:

    См. также Excel-файл.

  119. Здравствуйте.

    Есть список покупок (Наименование Товара и Сумма).
    Как подсчитать стоимость покупок товаров, наименование которых начинается с букв «Бу»?

  120. Супер!
    Огромное спасибо, открыли глаза на очередной принцип управления формулой)
    Я пока выходил из положения, складывая в одной ячейке СУММЕСЛИ для булавок отдельно и для бумаги отдельно.
    Но было некомфортно из-за отсутствия общности)

  121. Здравствуйте еще раз.

    Пусть в ячейку A1 введена формула =СУММ(B1:C1) (к примеру). Текстовое представление этой формулы можно увидеть в режиме «Показать формулы».
    Но это представление только визуально.

    Вопрос:
    можно ли в ячейке D1 (к примеру) сформировать текстовую строку ‘=СУММ(B1:B2), которая делает текстовое представление реальным, а не визуальным?

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

  122. Виктор, я не знаю стандартной функции в Excel, возвращающей формулу, содержащуюся в ячейке. Возможно, лучший вариант периодически переключаться в режим «Показать формулы». Если вам непременно нужно вывести формулу в ячейку, то воспользуйтесь VBA. Есть правда еще один элегантный способ)) Подойдет ли он вам!? Для начала ознакомьтесь с заметкой Функция Получить.Ячейку. Можно устаревшей функции xlm присвоить имя, и использовать это имя в ячейке. Правда, такую формулу нельзя протянуть.

    См. также Excel-файл.

  123. Добрый день,
    Помогите высчитать:
    При сложении 2х цифр, значение должен быть 22, то отнимаем 22 (то есть -22). Есть ли в Экзеле такая функция?

  124. Здравствуйте.Помогите пожалуйста просуммировать числа в текстовых ячеек. Например в одной ячейке AA5555mm а вдругой 44523NLT
    Мне нужно чтобы в другой ячейке суммировать только цифры этих ячеек, при том что бы значение этих ячеек не менялось то есть буквы в этих ячеек остались на месте. Я могу заменить буквы на нет, но мне жто не нужно. Мне нужно что бы буквы остались. Спасибо.

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

  126. Медведев

    Добрый день!

    совсем встал в тупик. помогите если такое возможно.

    есть номера заявок 1,2,3 и т.д.
    в каждой заявке есть доступный объем 5,10,20 и т.д.

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

    как вывести в отдельном столбике напротив каждой заявки «остаток» ?

    Или так не получится?

  127. Наталья

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

  128. Здравствуйте помогите решить проблему. Хочу чтобы он найдя модель сумировал сколько напротив и выводил общее. http://prntscr.com/klrfel

  129. ПРИВЕТ ВСЕМ. Помогите разобраться формула не хочет работать, я хочу если пишу номер модели в пустой ячейки чтобы она притягивала его соседние ячейки http://prntscr.com/kny196

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

  131. Добрый день, возможно ли в ехел посчитать сумму если цифра с символом, например «*»?
    как пример есть ячейки с цифрами 1; -1; -2; 5*; 5*;, можно ли получить сумму этих цифр 10?

  132. Добрый день. Маюсь над задачей из одной таблицы выдернуть данные в другую по условиям. Обычно делаю или ВПР или Суммесли (Суммеслимн), но иногда попадаются такие строки где несколько условий в одной ячейке. Для понимания скриншот
    Из таблицы 2 в таблицу 1
    Данные из Таблицы 2 в Таблицу 1

  133. Иван, на мой взгляд, индекс для того и придуман, чтобы быть целым числом, а ваша строка 4,5,6,7 индексом не является. Предлагаю решать в два этапа: сначала по индексу «подтянуть» данные из Таблицы 2 в Таблицу 1, а затем сделать сводную на основе Таблицы 1
    Сводная на основе Таблицы 2
    См. также Excel-файл

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

  135. Уточню, к моему вопросу,: чтобы эти ячейки ещё как-то выделялись, и можно было бы выбрать вариант ячеек не подряд, чтобы суммировалось, а в разброс.

  136. Здравствуйте. Подскажите.
    Есть нуменклатура на листе 1, на листе2 — учет продаж,сделала выпадающую ячейку по товарам из 1листа, как сделать чтоб из листа 1 в лист 2 по наименованию тянуло за собою цену в другом столбце? Спасибо, надеюсь понятно объяснила.

  137. Здравствуйте!С ВПР очень помогло, сделала что хотела, спасибо большое!

    Есть последняя проблема,
    Как сделать чтоб столбец в ПРАЙСЕ показывал количество проданного товара по определённому наименованию , который формируется в 1таблице при помощи выпадающей ячейки из прайса. Спасибо!

  138. Здравствуйте. У меня есть таблица ведущей деятельности на бирже. Таблица с числами отрицательными и положительными. Мне надо знать на какой минимальном уровне я находился за время ведения таблицы.

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

  140. Михаил, пробовали функцию =МИН()? Или я неверно понял вашу задачу?

  141. У меня в итоговом ряду подщитывается итог каждой строчки т.е сделки и поэтому =мин() показывает самое минимальное значение для каждой сделки. А мне надо знать вообщем после всех вместе сделок какой был минимальный уровнь. Спасибо

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

  143. Валерий

    Здравствуйте, поможите, плз))))), есть таблица,табель, в котором строки — даты, а столбцы — сотрудники, и каждый столбец делится на несколько, куда идут данные о наименовании объекта, количестве часов, стоимости часа, при этом ни какой твердой закономерности нет, объекты по сотрудникам могут меняться ежедневно(а могут и не меняться), также и с количеством и стоимостью нормо-часов, условие: необходима формула, которая будет из этого хаоса в любой момент представлять данные о часах и по-объектно и по-именно, и по-именно-по-объектно.)))

  144. Валерий

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

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

  146. Валерий

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

  147. Нилуфар

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

  148. Добрый день. Помогите решить задачу.

    Каждый день сотруднику начисляются часы, если в день больше 6 часов, то каждый сверх час умножается на 1,5. Теперь надо найти формулу которая подсчитала бы общее кол-во часов.

    Например:
    01.апр 02.апр 03.апр Итог
    10 10 10 36
    10 10 8 33

    10 => 6 + 4*1,5 = 12
    8 => 6 + 2*1,5 = 9

  149. Добрый день. Помогите пожалуйста.
    Как сложить определённые ячейки в строках, но с условием «только положительные».

  150. Например, данные находятся в диапазоне A1:P1. Тогда можно использовать формулу =СУММЕСЛИ(A1:P1;">0";A1:P1)

  151. Валерия

    Добрый день!
    подскажите пожалуйста возможно ли в функции суммеслимн в диапазоне суммирования уже задать фильтрацию по цвету, сделанную через Visual Basic.
    то есть чтобы выглядело так
    =sumifs(sumbycolorvis(D5:D59;B4)…и дальнейшие диапазоны с условиями)
    -sumbycolorvis как промежуточные итоги,
    -D5:D59 это сам диапазон
    -B4 это адрес на цвет ячейки
    Боюсь, что excel видит это значение sumbycolorvis(D5:D59;B4) уже как сумму, а не диапазон и поэтому выдает ошибку

    Благодарю

  152. Валерия, так нельзя. Нужен новый код VBA, соответствующий всей вашей задаче. Но… посмотрите заметку Сумма по цвету ячеек в Excel, а также обширные комментарии к ней. Может быть, что-то почерпнете…

  153. Дата 01.09 02.09 03.09 04.09 итого

    Количество 5 3 2 7 10

    Здравствуйте, помогите пож-ста, как вывести формулу, чтобы итого получить 10.

  154. Александр

    Здравствуйте! Подскажите пожалуйста.
    Ячейка с автосуммой, выборочных ячеек. Как сделать так, чтобы в другой ячейки после подсчета автосуммы написало количество выделенных ячеек автоматом?

  155. Александр

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

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

    Вложение  Tabl1.xlsx

  157. Сергей Багузин

    Сергей, попробуйте функции =СУММЕСЛИ() или =СУММЕСЛИМН().

  158. Здравствуйте,помогите пожалуйста,на работе сказали журнал в эксель сделать за год.
    Мне нужно чтобы из стобца I на строке 4 прибавлялось то что на следующей строке в столбце Н и появлалась сумма опять на столбце I на 5 строке и так далее, т.е. столбец I 5 строка + 6 строка столбца Н= сумма на I столбце строка 6

    Вложение

  159. Михаил Филиппов

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

    Вложение  13.xlsx

  160. Дмитрий

    Здравствуйте. Подскажите как правильно сделать. В экселе ведем журнал, там есть столбец с номером, типа: 111/22-33. как сделать чтобы при растягивании значения на нижние ячейки подсчет велся не по последним цифрам 33 (33-34-35-..), а по первым 111 (111-112-113-…)

  161. Сергей Багузин

    Дмитрий, простого решения не нашел. Могу предложить следующую формулу: =ЗНАЧЕН(ЛЕВСИМВ(A1;НАЙТИ("/";A1)-1))+1&ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("/";A1)+1)

    См. Excel-файл.

  162. Ирина Михайлова

    Здравствуйте! Подскажите пожалуйста, как правильно прописать формулу. Таблица в эксель следующая — это тест для 30 человек, слева в столбце фамилии, справа по шкалам варианты ответа (а,б,в,г,д) последний столбец, итог. В итоге нужно посчитать общее количество баллов по каждому человеку, если а-5 баллов, б-4 балла, в-3 балла, г-2 балла, д-1 балл.

  163. Здравсвуйте! Не могу найти формулу подсчета сумм и при этом чтобы он игнорировал символы, но суммировал не смотря на то что присутствует символ, тот же самый тире

    Вложение  novyj-spisok.xlsx

  164. Добрый день!
    Подскажите, пожалуйста, какой формулой можно решить такой вопрос? В файле.
    Не соображу, как в статье двойные записи не посчитать.

    Вложение  pomoshh.xlsx

  165. Сергей Багузин

    Павел, вы используете формулу, ссылающуюся на ячейки в явном виде =D8+F8+G8+H8+I8+J8+L8+M8+N8+O8+P8+R8+S8+T8+U8. Поэтому она и не работает, если хотя бы в одной ячейке не число, а текст. Вместо этого используйте =СУММ(D8:U8). Такая формула будет игнорировать текстовые ячейки. Если же вы не случайно пропустили некоторые ячейки при суммировании, и вам действительно нужно суммировать только некоторые ячейки из диапазона D8:U8, используйте более сложную конструкцию =АГРЕГАТ(9;6;D8;F8;G8;H8;I8;J8;L8;M8;N8;O8;P8;R8;S8;T8;U8). Первый аргумент функции — 9 — говорит, что это функция суммирования, второй аргумент — 6 — просит игнорировать ошибочные значения, последующие аргументы говорят, какие ячейки суммировать.

  166. Здравствуйте!
    Мне нужно посчитать остаток на складе, но проблема в том, что по условию мне нужно, что бы искало все совпадения по столбцах, сначала сравниваемым столбец B и G, если есть совпадения, то сравниваем столбец C и H, если есть одинаковые то считаем их разницу, а у меня только совпадения строк считает, а не все значения проверят по столбцу, как это исправить. спасибо.
    =query(B6:J200;»select B,C, sum(D)-sum(I), sum(E)-sum(J) where(B=G and C=H) group by B,C label sum(D)-sum(I)», sum(E)-sum(J)» «)

  167. Добрый день. Очень нужно решение проблемы как вытянуть значение по нескольким критериям, так-же нужно учесть что эти критерии не стабильны.
    Есть код товара под фасовку, для расчета стоимости необходимо в расчете добавить на разные вкладки excel стоимость фасовки из таблицы. Стоимость фасовки ежемесячно обновляется и в зависимости от даты поставки необходимо подобрать стоимость фасовки.
    Например:
    код товара 01.01.2021 01.03.2021
    99008 6,354 6,356
    157069 2,268 2,73
    Расчет по приходу 01 03.01.2021 — нужно вытянуть стоимость на 01.01.21, и таких например 5, а приход за последнюю неделю февраля уже нужно учитывать по цене за 01.03.21 и до следующего обновления стоимости. При этом нужно учитывать и код товара т.к. один и тот-же товар фасуется в упаковки с разным весом. Заранее благодарна.

  168. Добрый день. как расчитать сколько ячеек было заполнено до опредеоенного часа,
    скажем с 8:00 до 14:00 и с 14:00 до 17:00?
    Спасибо

  169. Сергей Багузин

    DAN, Excel не хранит дату и время изменения содержимого ячеек. Если вам нужна такая опция, понадобится код на VBA. Посмотрите, например, здесь.

  170. Татьяна

    Добрый день.Мне нужно подсчитать количество ячеек с критерием L (F11),у меня не подсчитывает помогите.прописываю так-=СУММЕСЛИ(D:D;F11;D:D) в ячеке (G2)вот так а значение равно 0, хотя там позиций с этим критерием много

    Вложение  Kniga2.xlsx

  171. Проблема в том, что СУММЕСЛИ суммирует числа, а у вас буквы. Используйте функцию =СЧЁТЕСЛИ(D:D;F11).

  172. Татьяна

    эти данные мне нужно внести в другую книгу

  173. Сергей Багузин

    Алина, например, с помощью формулы =СУММПРОИЗВ(­­(ЗНАЧЕН(ПРАВСИМВ(A5:A11))>5);C5:C11)
    См. Excel-файл
    null

  174. Добрый день!
    Подскажите формулу для подсчета в строке количества ячеек до первой ячейки (считаем слева направо, удовлетворяющей определенному критерию, так как в строке дальше еще встречаются такие ячейки.
    Спасибо

  175. николай

    Добрый день.
    Прошу помощи. Из множества чисел необходимо выбрать количество чисел в интервале 20, выделить цветом.

  176. Добрый день. В файле несколько листов, в каждом листе несколько колонок. Например в Листе1 в колонке А есть значение 1 и в колонке В значение 2, В Листе2 в колонке С есть значение 1 и в колонке В значение 2 и так далее, то есть колонки с одинаковыми значениями не совпадают! Как посчитать сумму всех значений «2» по критерию значения «1» во всех листах ? Спасибо

Добавить комментарий

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