Выборочные вычисления суммы, среднего и количества в Excel

Полный текст статьи, подробности и файл-пример www.planetaexcel.ru/techniques...

[@trimaski]

Николай, я готова Вас прямо на руках носить! Мне прислали тестовое задание в эксель, не знала, что с ним делать, посмотрела Ваши видео и поняла, что ничего проще нет! Спасибо огромное!

[@user-sf6ev3ci2y]

Большое человеческое спасибо, Николай, всё очень доступно и понятно. Я думал, что я продвинутый пользователь, но как говорят - век живи, век учись.

[@user-wo7gl3br8q]

Николай, Вы - большая умница! Спасибо за понятное и интересное объяснение!

[@justcraft4600]

За символ звездочки, скрывающую нерелевантную инфу в ячейке- гигантский лайк!

[@kerret2503]

Всё кратко, полно и понятно. Ваше видео очень помогло мне по работе. Большое спасибо за урок!

[@user-pk6dg7we8q]

Николай, Ваши ролики по Excel лучшие. Всё кратко, полно и понятно. Спасибо.

[@tonjaberger6433]

Спасибо большое,Николай. Очень мне помог ваше объяснение ,Очень спокойно и доступно. Талант.

[@masterpaloch]

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

[@builroom6203]

Автор заслуживает лайк! Помог на экзамене! Смотрел прям на экзамене урок и делал, уважение и респект!

[@timur8741]

для меня было очень полезно, спасибо вам уважаемый Николай Павлов!

[@belochka8080]

как всегда, очень понятное и полезное видео.

[@zozo4ka]

Вообще после часа поисков нужного мне урока, в этом видео нашел то, что облегчит работу в разы! Спасибо

[@vladlenasotnikova1091]

Спасибо вам огромное!!! Все очень доступно рассказываете. Ваше видео очень помогло мне по работе :)

[@user-sk7cl8gb3m]

спасибо за прекрасные видео-уроки ! Куплю Вашу книгу, пожалуй.

[@alexandrkrav]

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

[@planetaexcel]

Спасибо! Рад, что смог помочь :)

[@irina8153]

Спасибо большое, Николай все очень доступно и лаконично

[@elenaveselova8426]

В очередной раз - СПАСИБО!!!! Доступно. Интересно. Полезно.

[@yurd563]

Большое спасибо за урок!

[@Albertus99]

Николай крутой и чрезвычайно полезный для общества Человек! Низкий поклон!

[@user-ml4xw6uf8e]

Николай, огромное спасибо. Голову ломал целый день, как получить дату последней заливки колонн одного типа. ДМАКС решает!!!

[@user-qh5fr3yo1w]

Видео очень хорошее и понятное. Большое спасибо.

[@TSMOWRU]

Николай, сколько миллионов раз я посылала Вам мысленные приветы и пожелания всего самого наилучшего!!! С Вашими примерами, Excel мне стал намного ближе!

[@planetaexcel]

Спасибо! Рад был помочь :)

[@Oksana_g]

Николай спасибо большое! Данный ролик помог мне успешно пройти тестирование в банке. Одна из частей задания просто дублирует ваш урок:)

[@planetaexcel]

:)

[@user-oz1ku3in9o]

Очень нужное и полезное видео. Спасибо.

[@sergeypautov8086]

Класс! Мне кажется, функция БДСУММ самый гибкий вариант. Там ведь достаточно поменять одну ячейку условия, причём для любого столбца - и всё пересчитается.

[@Beki4ch]

Николай,спасибо Вам огромное,я посмотрела 1 видеурок Как в Excel скопировать данные с одного листа на другой. Все так доступно и понятно. Это очень помогает в работе новичку,продакт-менеджеру.

[@planetaexcel]

Рад, что смог помочь :)

[@user-nl3sm1ij2s]

Спасибо за подробное объяснение, а по ссылке перешел так вообще решил вопрос))))

[@WARTEM1]

Спасибо огромное Вам! Искал как поставить условие по суммированию значений, фильтруя названия. Нашел ответ в функции СУММЕСЛИ. Теперь автоматически Excel считает данные по критериям. Ничего вручную делать не нужно.

[@evro5267]

очень понятное объяснение ..спасибо😘

[@VITOSGTI]

Спасибо! Пригодилось. Все довольно понятно! :)

[@Oksana_g]

супер полезная вещь! Спасибо!

[@user-wr7fo3ix9u]

Браво, Николай!!

[@user-xg4xk3ku5v]

Крутяк! Большое спасибо!

[@user-gu6ct4le7m]

Дуже вдячний Вам за Ваші уроки

[@planetaexcel]

Нема за що :)

[@VznachitVelichko]

Вы просто красавчик!

[@narekhovanesyan1643]

Спасибо вам за урок

[@Behzodhuja]

Спосибо из Ташкента

[@tan_usha777tigres7]

спасибо вам, если бы я знала это раньше, то не слетела бы с должности экономиста(((((

[@JonathannLivingstonn]

Надеюсь, жизнь сложилась хорошо 🙂

[@kipsag]

Супер, спасибо!

[@fenixcharodey]

Очень классно все рассказал Николай. Жаль только, что нельзя(или я не знаю как) посчитать выборку по условию сразу из трех таблиц. Они расположены не в одну колбасу, а разделены так, чтобы сразу на одной странице были для наглядности. И как забить вычесление сразу по трем столбацам я так и не нашел, ни через запятую,ни через точку-с-запятой,ни через плюс не понимает Эксель меня,что я хочу.

[@anjdele834]

Супер! Спасибо!

[@user-pd4qi2tf4r]

Спасибо огромное!!

[@Lavesan1]

Спасибо большое. Помогли

[@user-rs8yd5uo3w]

Большое спасибо!!!!!

[@IvanKuptsov]

Спасибо за хороший урок! Подскажите как у вас получается ставить одновременно кавычки вокруг слова (когда вводили формулу)?

[@jibjab313]

Спасибо, очень интересно. В формулах СУММЕСЛИ и СУММЕСЛИМН в качестве критерия можно вложенные формулы использовать (например, НЕ)?

[@Albertus99]

Большое спасибо за образовывание малограмотного населения типа меня!

[@battle571]

отличный материал

[@user-ff7iq8kp7v]

спасибо большое!

[@sergeyd7504]

Красавчик!!!

[@user-sq8kl8vr5y]

Можно ли использовать функцию счетесли для ячеек с разных листов?

[@mariden5810]

Спасибо)

[@vl8224]

Спасибо!

[@user-fw9no9zq2c]

Николай, спасибо Вам большое за качественный контент!! ЕСТЬ ВОПРОС, помогите!! Как сделать сумму в умной таблице? чтоб при использовании фильтров таблицы, можно было отобразить 1) сумма 2) количество заказчиков

[@31314542]

Здравствуйте Николай! Ваши уроки очень интересны. Подскажите пожалуйста как вставить в формулу для ячейки "I9" значение ячейки "H9", т.е. чтобы формула зависела от указанной в ячейке "H9" даты. Спасибо заранее!

[@konstantinzhizhilkin2930]

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

[@Kelfuciy]

Спасибо

[@user-dd1zl8ug5y]

Жаль нельзя поставить 2 лайка. 1 за помощь решения проблемы. 2 за однофамильца

[@user-jv5zz5ld5r]

спасибо😘

[@alav7590]

Здравствуйте Николай! Хитрая функция =счётеслимн( не хочет работать с раздельным диапазоном (в отличие от =счетз). Видел в сети как решали вариант с набором ячеек, находящихся по диагонали в диапазоне, где счет ячеек выполнялся через =суммпроизв(. Однако никак не смог прицепить этот вариант к более (казалось бы) простой задаче, когда ячейки в диапазоне находятся в одном столбце (но не подряд). Обычная формула проверки каждой нужной ячейки по нескольким условиям выглядит как первая глава романа Л.Н. Толстого...) Есть ли какой-то оптимальный вариант решения подсчета разрозненных ячеек из одного столбца по нескольким условиям? Вариант сканировать по условиям весь столбец не проходит, т.к. имеются и другие ячейки, влияющие на проверку на условие.

[@user-pd8dw7ii1w]

Николай, огромная благодарность за полезную информацию. Применила в работе кол-во по менеджерам, так же ещё по некоторым условиям. Много пересмотрела Ваших уроков и применила в работе. Ваши видео добавила в свой арсенал знаний полезной информации. Очень доступно и много вариантов применения Ваших уроков. Вопрос: как проставить формулу? Есть штат от 400 сотрудников с разной категорией возраста от 18 до 55. Нужно проставить формулу, что б фильтровало количество сотрудников от 18 до 25; от 25 до 35 и т.д. Если у Вас есть видео о таких формулах, буду очень благодарна в помощи.

[@planetaexcel]

Трудно сказать не видя вашего файла, но должно быть что-то вроде СЧЁТЕСЛИМН(столбец с возрастом; ">=18"; столбец с возрастом; "

[@user-pd8dw7ii1w]

@@planetaexcel Огромная БЛАГОДАРНОСТЬ! Вы дарите такую ценность, как материал знаний. С Вашими уроками на работе просто удовольствие работать с огромными таблицами и отчётности по ним. Примите самые добрые и искренние слова благодарности. Вы вносите неоценимый вклад в развитие нас, те кто желает учиться. А я ещё тот человек, который пока не найдёт ответ-не успокоиться. Благодарю за формулу, где теперь есть статистика на каждый день всех сотрудников по возрастным категориям.

[@user-pd8dw7ii1w]

@@planetaexcel много, много раз буду Вам всегда быть благодарна! В работе уже много раз в разных таблицах применила это видео. Это - как по "щучьему велению" - новые данные в таблицу вношу, и вся статистика автоматом. ОГРОМНОЕ БЛАГОДАРЮ!!

[@valeriymelnichuk7497]

Добрый день, Николай! Позвольте спросить вас том какие функции я должен использовать для следующего задания - у меня есть строка данных (100 ячеек) с цифрами от 1 до 5 (ввперемежку), мне необходимо сделать следующее - узнать какой максимальный период между цифрами (например цифра 1 встречается максимум через девять цифр) и второе сколько таких максимальных периодов было в строке. Если есть возможность решения данной задачи при помощи функций подскажите как эти функциями называются. Не буду скрывать сам я такую задачу решит не могу, недостаток знаний. Заранее вам благодарен.

[@nataliamelnyk6977]

Подскажите, пожалуйста, какой формулой посчитать количество работников за месяц в определенном отделе предприятия, формула СЧЁТЕСЛИМН не считает))

[@NaomiKawai1]

Николай, а что делать, если нужно подсчитать кол-во ячеек по 2 условиям, а точнее вывести кол-во ячеек с 1 условием, которые содержат текст в ячейках "согласовано в СК", но в другом столбце есть другое условие и если второе условие наоборот отсутствует (ячейка пустая или стоит минус). в 1 условии есть "ПМ"=12 раз, "согласовано ск"=7 раз, "в работе"=14 раз и др., 2 условие "завершено" или ячейка пустая. Как подсчитать это? СЧЕТЕСЛИМН работает не корректно он суммирует значения первого и второго условия, а мне надо после первого условия выбрать второе, только из "согласовано", которые ещё "не завершены=пустые" Причем завершенные могут быть стоять в любом варианте первого условия. Это все в перемежку, а строк 1330 и категории меняются. Буду благодарна за помощь

[@user-yp7im4ic5x]

примерно та же проблема. только надо формулу =$В*20% вписать относительно буквенных условий в 3 других ячеек. если решили свою проблему- подскажите как. буду играться применяя к к своей задаче)))

[@user-yg2vn6sh3y]

Николай, все делаю как вы говорите! есть проблема использую функц =суммесли, диапазон находится в форм таблице, а ячейка вне таблицы, прописываю формулу результата в ячейке нет!??? использую функц =сумм(диапазон в таблице, ячейка вне таблице) результат есть!!!

[@Music.86195.]

Здравствуйте, Николай! Скажите, пожалуйста, а можно ли составить формулу в функции СЧЁТЕСЛИМН (или в другой какой-нибудь функции) так, чтобы в ней вычиталось текстовое условие в столбце: в моём случае это последнее условие, чтобы вычитало из выбираемого списка всех в графе «отчислен» К, примеру (но эта формула у меня не работает): =СЧЁТЕСЛИМН(G2:G1000;"ДПОП";H2:H1000;"2";F2:F1000;"фортепиано";J2:J1023;"отчислен") Спасибо, до свидания.

[@Sleepmalice]

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

[@user-kg3lw9ol5c]

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

[@motan87]

Спасибо большое! Скажите пожалуйста, а как сделать суммирование по месяцам? Спасибо

[@planetaexcel]

Проще всего добавить к таблице столбец с функцией =МЕСЯЦ() для извлечения месяца, а потом по нему и считать.

[@user-jo8wq3ji8x]

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

[@user-wo7gl3br8q]

Николай, скажите, пожалуйста, почему когда я использую функции СУММЕСЛИМН и СЧЕТЕСЛИМН с условиями по одному столбцу в результате получается 0, в то время, когда данные там есть?

[@WasiljKalinin]

Добрый день Николай! Есть вопрос. Можно ли при использовании функции СУММЕСЛИМН по двум разным условиям проверять один и тот же диапазон условия? Спасибо!

[@planetaexcel]

Да, конечно! Тогда задаваемые условия будут связаны логическим "И". Если нужно "ИЛИ", то это уже сумма двух СУММЕСЛИ - каждая со своим условием.

[@user-hi7gg4vy5s]

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

[@sergeyo8913]

подскажите возможно ли в просуммировать данные из нескольких диапазонов суммирования, также с несколькими условиями. Допустим есть несколько счетов (1,2,3). Отвечу сам на свой вопрос. В процессе родилось два таких решения: =((СУММЕСЛИМН(E:E;C:C;">28.02.2017";C:C;"28.02.2017";C:C;"28.02.2017";C:C;"28.02.2017";C:C;"

[@aleksandr030298]

Можите сделать пожалуйста видео урок как можно сделать таблицу ( Максимальное количество пунктов, проценты сколько правильно и в результате оценка ) Тоесть 8 заданий макс.пунктов 65 макс. оценка 10 (правильно сделанно 21,5 пункта к примеру 18% и оценка =3) На подобии такого если Вам не сложно за раниее спасибо

[@serzh101]

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

[@erfiuzb8414]

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

[@user-vn3hv4ib6o]

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

[@planetaexcel]

Валентина, у вас там в ячейках проверяемого столбца только дата или дата+время? Скорее всего - второе, поэтому проверка только на "=дата" и не срабатывает и двойное условие, которое вы делаете - как раз правильно.

[@xizgil04]

+Nikolay Pavlov а что делать если нужно потянуть допустим нужно по месяцам каждый вручную?

[@user-lg9zs4yv3x]

Николай, спасибо за грамотные и интересные уроки по Excel! Очень помогли мне развиваться, главное - каждый урок жду с нетерпением!!!

[@VznachitVelichko]

В секунды решил вопрос)))

[@Leks-bj1iy]

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

[@Nelya007]

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

[@user-yt4oe4ic1x]

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

[@yermektemirbayev9991]

Подскажите, пожалуйста, если таких Ашан много и все по-разному. Тоо Фёдоров, Фёдоров тоо, тоо петров, петров тоо и их около 1000 как быть?

[@user-zq9vz2zt2t]

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

[@user-gq4dn3bf4c]

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

[@user-fw9no9zq2c]

Подскажите, как узнать сколько заказов после 1 марта 2014 г. именно "Петрова" ??

[@javlont9330]

Здравствуйте ! Буду очень признателен если сможете дать решение … Функция «суммаеслимн» используется для подсчёта произведённых оплат по договорам по месячно и критерий формулы привязан к ячейке которая в свою очередь подвязана под функцию «ВПР» соответственно данные переносятся с другой таблицы. Массив данных постоянно увеличивается(диапазоны суммирования и поиска критерия привязан к столбцам). Формулы все работают правильно и выдают корректные данные. Проблема: при вводе данных(даже в одну ячейку) в таблицы из которых производится вычисления приходится долго ждать до 20 секунд пока формулы все подсчитают и выдадут обновлённые данные а также сам файл в тоже время зависает. Для информации: Работаю на MacBook M1 с оперативной памятью 16 гб. Вопрос: что можно сделать чтобы ускорить процесс вычисления и решить проблему зависания файла? По другим вашим урокам, были примеры когда некоторые формулы производили растёт гораздо быстрее чем их аналоги/альтернативные формулы. Может порекомендуете использовать другие формулы ? Или есть какой-то придел вычисления самой excel ? Что надо делить на таблицы на файлы и не сводить все данные и формулы с вычислениями в одну таблицу ???

[@planetaexcel]

Причин торможения может быть очень много - нужно смотреть конкретно ваш файл и разбираться. Рекомендую глянуть для начала www.planetaexcel.ru/techniques/12/114/

[@Clecapb.]

Здравствуйте . Подскажите пожалуйста как мне сделать в эксель . Чтоб считало среднюю . Вот например я покупаю доллар , в разные промежутки времени, разное количество и по разной цене. Можно как то сделать , что считало среднюю цену доллара ?

[@uspehi-v-biznese]

Привет... Почему НЕ получается создать формулу СУМЕСЛИМН при условии, если мне нужно рассчитать заработок менеджера в диапазоне 2-х дат введённых в разных ячейках...но при этом я ввожу условия суммирования в конкретном столбце... потом ввожу условия из столбца с именем менеджера - всё работает, но как только я ссылаюсь на следующие условия от какой-то даты, до следующего условия (до какой-то даты) - у меня НЕ СЧИТАЕТ... то есть по-просту - не воспринимает дату и в окне формул - показывает не дату, а набор цифр... хотя в ячейке указан формат даты... 😃И возможности проставления & в формуле - не помогают... 😉

[@user-pv8yx1nk9b]

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

[@ersaiyn6588]

Николай а у вас есть таблицы с формулами для калькуляции блюд

[@planetaexcel]

Это как? Калорийность и все такое?

[@St-stroi]

Здравствуйте. Подскажите сделал небольшую таблицу в которой данные добавляются автоматически. И пока нет данных стоит значения Н/Д .Мне нужно подщитать всю колонку в некоторых ячейках стоят суммы а в некоторых пока нет данных.подскажите какую формулу использовать ??? Пробовал стандартную =СУММА() не работает.

[@planetaexcel]

Замените #НД на ноль с помощью функции ЕСЛИОШИБКА - и будет вам счастье :)

[@St-stroi]

@@planetaexcel то есть функция ЕСЛИОШИБКА скрывает значения #НД . а как сделать что бы убрать значения 0 ну скрыть его ? а то не очень красивы выглядит таблица когда все в нулях )))

[@sergey_zatsepin]

Хотелось бы всё же узнать, почему СУММЕСЛИМН не считает, если в условии используется оператор сравнения и ячейка с числом, например >=B5. И как это исправить.

[@mykhailoholei7588]

также была такая же проблема. Посмотрел справку Microsoft, там советуют операторы сравнения брать в кавычки после чего ставить амперсанд и тогда уже имя ячейки. В вашем случае, должно быть ">="&B5.

[@mariyapodibka7098]

Как подсчитать количество ечеек, где значения више среднего

[@Dragon-qs6yy]

Вот только не понятно, почему для чисел нужно использовать кавычки к примеру ">5000", это же применимо к названиям?

[@planetaexcel]

Аргумент, где задается условие в этих функциях должен быть текстовым - это их (функций) особенность.

[@Dragon-qs6yy]

@@planetaexcel спасибо за ответ.

[@user-lm5zd9ky4n]

А что если нужно суммировать все ячейки столбца, за исключением нескольких, которых много ( от ста)

[@planetaexcel]

Тогда сложите все и вычтите из них ненужные, т.е. СУММ(весь столбец) - СУММЕСЛИ(выбрать ненужные по условиям)

[@vmmoments4984]

А как обозначить каждое значение? допустим мне надо обозначить все суммы меньше 5 000 буквой А

[@user-gs5rg7om9t]

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

[@planetaexcel]

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

[@kppptt]

Один критерий в одном диапазоне это понятно. Мучился когда много критериев в одном диапазоне. Кроме как БДСУММ или сводными таблицами не получилось. Другие решения выглядят громоздкими и с лету у меня не заработали.

[@Radik71101]

спс

[@user-rh9vn3tn4g]

Суммесли выдаёт ноль ( В чём может быть причина?

[@planetaexcel]

Условие не выполняется ни для одной ячейки, скорее всего.