9 способов как используется функция БДСУММ в Excel

Function BDSUMM 9 способов как используется функция БДСУММ в Excel Добрый день!

Продолжу свое стремление описать разнообразие функций в Excel и следующей на рассмотрении у нас функция БДСУММ. Это еще один из представителей функций суммирования, но со своими специфическими условиями. Функция БДСУММ в Excel ищет и суммирует числа в вашей таблице по определенным вами критериям, это ее основное свойство.

Положа руку на сердце могу сказать, что много расчётов и вычислений можно сделать и без нее используя разнообразные функции массивов, функцию СУММЕСЛИМН или СУММПРОИЗВ, но если же вам нужно сделать сложную выборку используя «подстановочные» знаки, то тогда вам точно нужно использовать героя нашей статьи.

Для начала рассмотрим синтаксис, который использует функция БДСУММ в Excel:

=БДСУММ(диапазон вашей базы данных; поле поиска; условие поиска), где

Function BDSUMM 2 9 способов как используется функция БДСУММ в Excel

  • Диапазон вашей базы данных – является обязательным критерием и указывается диапазон, из которого и будут по критериям изыматься нужные вам значения, обязательное условие, что бы первая строка содержала в себе заголовок вашей таблицы;
  • Поле поиска – это обязательный критерий, указывается для определения, по которому полю производить поиск и суммирование чисел. Указывать можно как название поля, то есть текстовое значение, типа «Продукт», «Страна», с обязательным взятием аргумента в кавычки, так и числовое значение, типа, 1,2,3… для определения номера поля или просто указать ссылку на нужное поле, решать вам;
  • Условие поиска – это обязательный аргумент, который содержит в себе диапазон с указанными в нём критериями для суммирования значений. Аналогичная структура таблицы используется при создании расширенного фильтра.

При работе с функцией БДСУММ стоить отметить несколько условий, на которые стоит обращать внимание при работе:

  • При выполнении работы над целым столбиком заполненным данными, обязательно стоит вставить пустую строку под заголовками столбиков в указанном диапазоне критериев;
  • Диапазон критериев не должен иметь пересечения со списком;
  • Хотя данные для формирования диапазона условий вы можете помещать в любое место на листе, но тем не менее, помещать его после списка не следует, так как данные которые мы добавляем в список, будут вставляться в первую строку после списка и если в строке есть данные, то добавить новые не получится;
  • Можно использовать для условий любой диапазон, который содержит, как минимум один заголовок и хотя бы одну из ячеек, которая расположена под заголовком и содержит в себе условие.

Итак, теоретическую часть, я считаю выполненной, приступим теперь к практическому применению функции БДСУММ в своей работе, для этого рассмотрим несколько примеров для выполнения, я примеры сделал по принципу расширенного фильтра, но вместо сбора значений по критерию будет суммирование:

  • С одним числовым критерием;
  • С одним текстовым критерием;
  • Суммирование по двум критериям по разным столбцам;
  • Суммирование по одному из двух условий в одном столбике;
  • Суммирование по одному из двух условий в двух разных столбиках;
  • Суммирование по двум текстовым критериям по двум столбикам;
  • Использование результата формулы для получения критерия отбора и суммирования;
  • Суммирование по трём критериям;
  • Суммирование по текстовому критерию с учётом регистра.

Функция БДСУММ с одним числовым критерием

Итак, для начала рассмотрим простой пример с одним числовым критерием, для этого выберем столбик «Урожай» и укажем что нам надо деревья с урожайность «>=10». Что бы получить результат нам нужна формула такого вида (советую использовать абсолютные ссылки):

=БДСУММ($B$6:$G$12;E6;E2:E3),

где, $B$6:$G$12 диапазон в котором мы будем суммировать, E6 – столбик в котором мы будем суммировать и E2:E3 диапазон в которые мы ввели критерии для суммирования. В итоге формула нашла 3 позиции на общую сумму 34.

Для получения аналогично результата вы можете воспользоваться также такими формулами:

=СУММЕСЛИ(E7:E12;»>=10″)

Function BDSUMM 3 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(E7:E12;E3)

Функция БДСУММ с одним текстовым критерием

Теперь рассмотрим, как ведет себя функция БДСУММ с текстовыми критериями, в общем всё остается так и в предыдущем примере за исключением того как указывается текстовый критерий, а он указывается только в таком виде: =»=с.Серово» и тогда результат у вас получится, иначе формула не сможет распознать ваш критерий. Теперь подставляем этот критерий в формулу и получаем:

=БДСУММ($B$6:$G$12;E6;C2:C3), как видим произошло только изменения диапазона критерия.

Для получения схожего результата вам пригодится функция СУМЕСЛИ:

Function BDSUMM 4 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(C7:C12;»с.Серово»;E7:E12)

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

Пример усложняется применением двух критериев, но ничего принципиально нового мы применять не будем, укажем текстовый критерий «с.Серово» и числовой критерий «>=10», оставив поле суммирование «Урожай», мы получим изменение формулы только по последнему аргументу, как результат. Теперь наша формула будет выглядеть так:

=БДСУММ($B$6:$G$12;E6;C2:E3), снова видите изменения только адреса диапазона критерия.

Альтернативный вариант можно получить с помощью функции СУММЕСЛИМН и так:

=СУММЕСЛИМН(E7:E12;C7:C12;C3;E7:E12;E3)

Function BDSUMM 5 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИМН(E7:E12;C7:C12;»с.Серово»;E7:E12;»>=10″)

Суммирование по одному из двух условий в одном столбике

Рассмотрим еще один вариант как применяется функция БДСУММ, но сейчас будем использовать не единичный критерий, а двойной, но для одного поля. Обязательно два критерия должны быть в разных строках. Суть формулы сводится к тому что, она проходит один и тот же диапазон два раза считая каждый из критериев отдельно. Для этого примера формула будет выглядеть так:

=БДСУММ($B$6:$G$12;E6;C2:C4), здесь снова изменяем диапазон критерия, но не в ширину, а в высоту.

Также заменителем, вы можете использовать сумму функции СУММЕСЛИ:

Function BDSUMM 6 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(C7:C12;C3;E7:E12)+СУММЕСЛИ(C7:C12;C4;E7:E12).

Суммирование по одному из двух условий в двух разных столбиках

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

Function BDSUMM 7 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;C2:D4), принцип формирования формулы сохраняется, окромя диапазона критерия, который включает в себя три строки: заголовок и два критерия.

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

В данном примере работы БДСУММ в Excel рассмотрим практически полный аналог ранее рассматриваемого примера, когда были два критерия в двух столбика, но там были числовой и текстовый критерий, а здесь рассмотрим суммирование по двум текстовым критериям и по двум столбикам. Используем критерии «=»=с.Иваново»» и «=»=Вишня»», которые мы и укажем в диапазоне критериев. Значит наша формула будет иметь следующий вид:

Function BDSUMM 8 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;B2:C3).

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

В данном примере функции БДСУММ я использую формулу для определения критерия отбора и суммирования, в данном случае найдем какие же деревья нам приносят доход и для определения условного аргумента возьмем среднее значение по продажам плодов с деревьев и всё что выше среднего показателя нас интересует. Для определения среднего значение которое станет нашим критерием «ИСТИНА» мы создаем формулу статистического направления:

Function BDSUMM 9 9 способов как используется функция БДСУММ в Excel

=G7>СРЗНАЧ($G$7:$G$12), не забываем об абсолютных ссылок для закрепления диапазона, что бы при переборе формулой значений они не сползли вниз, а вот значение G7 должно скользить по всему диапазону для определения «ЛОЖЬ» это или «ИСТИНА». Очень важно еще то чтобы названия заголовков не дублировались, имели отличие, поэтому поле критериев я назову «Среднее». И тогда формула начнёт работать, она переберет весь диапазон $G$7:$G$12 на наличие среднего значения и при получении положительного результата «ИСТИНА» она будет суммировать. С этой работой справится формула следующего вида:

=БДСУММ($B$6:$G$12;G6;$G$2:$G$3)

А если вам очень интересно альтернативное решение вопроса, то тогда испробуйте вариант с функцией СУММЕСЛИ в таком виде:

Function BDSUMM 10 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ($G$7:$G$12;»>»&СРЗНАЧ($G$7:$G$12))

Функция БДСУММ по трём критериям

В этом примере посчитаем среднее по продажам с выращенных плодов в двоих сёлах: «с.Иваново» и «с.Уютное». Основную идею отбора по критериям я уже описывал, поэтому повторятся не буду, просто скажу, что это будет соединение ранее рассмотренных критериев. Для получения результата нам нужна функция БДСУММ в таком виде:

Function BDSUMM 11 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;G6;$C$2:$G$4)

Суммирование по текстовому критерию с учётом регистра

Как я упоминал ранее, функция БДСУММ может производить поиск не только с подстановочными символами, но и с учётом регистра букв, вот сейчас именно такой вариант. Для начала определим условие по отбору критерия, если встречается название «с.ИВАНОВО», заглавными, то мы производим суммирование, для определения этого критерия нам нужна формула:

Function BDSUMM 12 9 способов как используется функция БДСУММ в Excel

=СОВПАД(«с.ИВАНОВО»;C7) А вот теперь мы можем прописать функцию БДСУММ, которая проверит диапазон на наличие указанного критерия и при получении значения «ИСТИНА» произведет суммирование. В примере я указал специально один раз по условию, и как видим, формула успешно отобрала все населенные пункты и нашла нужный и получили результат «9». Для этого была использована формула:

Function BDSUMM 13 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;$C$2:$C$3) Ну что же, я думаю, что функция БДСУММ в Excel, мною была описана деталь и во многих подробностях, поэтому вопросов будет мало, а пользы много. Если у вас возникли вопросы пишите комментарии, жду ваши лайки и отзывы. О других функциях вы можете ознакомиться в «Справочнике функций» на моем сайте.

Успехов вам в ваших делах!

«За деньги можно, конечно, купить очаровательного пса, но никакие деньги не смогут заставить его радостно вилять хвостом.
»
Д. Биллингс

Как посчитать сумму определенных ячеек в Excel

Если вам нужно посчитать сумму определенных ячеек в Excel, то это также можно сделать при помощи функции «Авто сумма». Для этого нужно выделить мышкой ячейку таблицы, в которую вы хотите поместить сумму, нажать на кнопку «Авто сумма» и выбрать нужные ячейки удерживая зажатой клавишу CTRL на клавиатуре. После того, как нужные ячейки выбраны, нажмите на клавишу Enter на клавиатуре, и в выбранной вами ячейке таблицы будет помещена сумма.

авто сумма для определенных ячеек

Кроме этого, вы можете вписать формулу для подсчета суммы определенных ячеек вручную. Для этого установите курсор там, где должна находится сумма, а потом введите формулу в формате: =СУММ(D3; D5; D7). Где вместо D3, D5 и D7 – адреса нужных вам ячеек. Обратите внимание, адреса ячеек вводятся через запятую, после последней ячейки запятая не нужна. После ввода формулы нажмите клавишу Eneter и в выбранной вами ячейке появится сумма.

вводим формулу для подсчета суммы

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

редактирование формулы

Вычисление в MS EXCEL среднего по условию (один ТЕКСТовый критерий)

​формула массива:​Al t​ мы узнаем общую​ мы вызовем по-другому.​ второй части формулы.​

  • ​ равенства (=).​Использован подстановочный знак ?​ точно совпадающее с​ только те значения,​ вызова Мастера функций.​​ естественно, что с​​При работе с числовыми​ или меньше конкретно​ функции свернется, а​ выпадающее списка выбираем​
  • ​ если использовать вторую​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СРЗНАЧ(C3-(СТРОКА(1:30)-1)*B3+СУММЕСЛИ(D3:D6;»​: =СРЗНАЧ(A1:A550)​ выручку после реализации​ С помощью мастера​​ Например, если мы​​(SUMPRODUCT) вычисляет произведение​Первая часть нашей формулы​Excel превратил вычисление среднего​ критерием.​​ которые больше указанного.​​Перейдите на вкладку «Формулы».​ их помощью есть​ выражениями иногда появляется​ установленного значения.​

​ вы сможете выделить​

Критерий точно соответствует значению

​ пункт «Среднее».​ таблицу, можно воспользоваться​Serge_007​И никаких али-бабов,​ всего количества товара.​ функций (кнопка fx​ увеличиваем одно или​ оценки каждого задания​ – это функция​ арифметического нескольких ячеек​В качестве диапазона усреднения​

​Этот способ как рассчитать​

​Выделите ячейку, в которой​ возможность обойти ограничения,​ потребность вычисления их​Для этих целей, используется​ ту группу ячеек​После этого, с помощью​ функцией СРЗНАЧ​:​ считает отменно​​ А функция СУММ​​ или комбинация клавиш​ несколько значений весов,​ на его вес,​

Читайте также:  Примеры функции РАНГ для ранжирования списков по условию в Excel

​СУММПРОИЗВ​ в очень простую​ можно указать лишь​ среднее арифметическое в​ хотите видеть результат​​ предполагающие предыдущий способ.​

​ среднего значения. В​ функция «СРЗНАЧЕСЛИ». Как​​ на листе, которую​​ функции «СРЗНАЧ», производится​.​​MCH​

В критерии применяются подстановочные знаки (*, ?)

​Brodiyga​ — сумирует количесвто​ SHIFT+F3).​ вторая часть формулы​ а затем суммирует​(SUMPRODUCT). Аргументы должны​ задачу – просто​ первую ячейку диапазона​ Excel, отлично подойдет​ вычислений.​

​ Сейчас будет рассказано​ математике это значение​ и функцию «СРЗНАЧ»,​ берете для расчета.​ расчет. В ячейку​Я подставил эту​, тоже «не на​: Exelю по барабану​ товара. Поделив общую​Третий способ вызова функции​ просто выполнит деление​ все полученные произведения.​ быть заключены в​ используйте функцию​ — функция СРЗНАЧЕСЛИ()​ в тех случаях,​На панели инструментов нажмите​ о произведении вычислений​

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

​ сколько значений складывать​ выручку от реализации​ СРЗНАЧ из панели:​

​ на большее значение,​ Другими словами, функция​

​ скобки, поэтому открываем​

​ вычислит все правильно:​

​ когда вы работаете​

​ на кнопку «Другие​

​ путем использования Мастера​

​ через Мастер функций,​

​ на кнопку слева​ или справа от​

​Хотя признаюсь, я​

​ и др. исчисления​

​ товара на общее​ «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».​

​ вновь приводя к​ находит сумму произведений​ их:​

​(AVERAGE). Но что​​=СРЗНАЧЕСЛИ($A$6:$A$16;»яблоки»;B6)​​ с огромным диапазоном​​ функции», расположенной в​​ функций. Итак, вот​

​ табличном редакторе от​

​ из панели формул,​

​ от поля ввода​

​ выделенной строки, выводится​

Способ 2. Ручной ввод формулы

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

  1. Курсор поставить на ячейку с будущим итогом.
  2. Ввести =. С равенства начинается любая формула в Excel.
  3. Левой кнопкой мыши щелкать по нужным для суммирования ячейкам, ставя между ними + и Enter.

Как в Эксель посчитать сумму столбца

Подпишитесь на канал и читайте интересные записи чаще! Есть вопросы? Задайте их в чате.

комментариев 39

Вадим, прошу прощения за оффтоп, но ведете бюджет в Excel ?

Ваша оценка: Thumb up Thumb down+1

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

Ваша оценка: Thumb up Thumb down

Каждый месяц считаю таблички в 30-40 тысяч строк и 30-100 столбцов.
Без сводной таблицы никак.

Ваша оценка: Thumb up Thumb down

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

Ваша оценка: Thumb up Thumb down

Vadim Sterkin,

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

Ваша оценка: Thumb up Thumb down+2

Виктор, я не веду домашний бюджет, сорри 🙂

Ваша оценка: Thumb up Thumb down

Вадим, по моему мнению, вести домашний бюджет в Excel не очень удобно. Я пользуюсь программой Money IQ на iPhone. Все быстро, удобно и всегда под рукой. Также есть куча похожих программ для iPhone, для Android и для Windows Phone.

Ваша оценка: Thumb up Thumb down

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

Ваша оценка: Thumb up Thumb down

Часто приходится выполнять подобные действия, но я использую «Форматировать как таблицу», включаю «Строка итогов» и вуаля!
Мне кажется так быстрее, чем у вас. Более того при моем способе интересно закрепляется верхняя строка при длинном списке.

Ваша оценка: Thumb up Thumb down+3

Сергей, точно! Это отличный вариант, о котором я забыл, хотя читал статью Николая Павлова (MVP Excel) Умные таблицы.

Я бы не сказал, что это быстрее, чем сводная таблица, но попроще, наверное. Что же касается формулы SUBTOTAL, то именно она используется в умных таблицах 🙂

Добавил способ в статью.

Ваша оценка: Thumb up Thumb down+2

Viktor Golub,

Прошу прощения что отвечаю на вопрос, который был задан не мне.
Просто хочу поделится программой, которую долго выбирал среди кучи подобных и веду в ней бюджет. http://www.youneedabudget.com Стоит на оф.сайте 60$, но на распродаже стим купил за 200 руб (можно запускать без стима). Есть пробный период. Описание почитайте http://gagadget.com/software/2013-02-06-zapiski-makovoda-prilozhenie-ynab-4-dlya-upravleniya-lichnyimi-finansami/
Ключевой момент для меня был наличие синхронизации между ПК и смартфоном. Может и вам подойдет 🙂

Ваша оценка: Thumb up Thumb down

Так скоро мы дойдем до азбуки.
Без обид, Вадим, про Windows у Вас получается куда лучше….

Ваша оценка: Thumb up Thumb down-3

Сергей, не вопрос, но отмечу пару моментов.

1. Не все задачи эффективно решаются средствами Windows. Да, в Windows есть калькулятор, но в Excel считать удобнее 🙂

2. Если данная задача для вас слишком проста, это не значит, что я пишу о ПО хуже, чем о Windows. Пишу-то я одинаково.

Годится! Согласны? Thumb up Thumb down+8

Веду учёт всех расходов в Excel 2007. По столбцам виды расходов, счёт в Сбербанке, доходы (зарплата, пенсия и прочие доходы). Веду учёт уже более 3-х лет. По строкам даты. Ввожу только приход и расход по категориям. Все остальные итоги выводятся автоматически. Подсчёт ведётся и ежедневный и помесячный. Спасибо за новую тему.

Ваша оценка: Thumb up Thumb down

Василий, а специализированным ПО не пробовали пользоваться? Или Excel устраивает для вашей задачи?

Ваша оценка: Thumb up Thumb down

Расчетов в Excel делаю не много, в основном сбор, отображение выборочной информации и ссылки на детальные описания (похоже на маленькие БД без макросов). Что-то типа такого:

Теперь по теме статьи. Вы, безусловно, вправе решать, о чем писать в своем блоге — это даже не обсуждается. Но на мой взгляд количество задач, которые может выполнить Excel, и их специфичность делает работу по описанию решения какой-то конкретной задачи не очень востребованной. Для этого есть форумы посвященные Excel’ю (типа вопрос — ответ). А Ваши статьи по оптимизации и решению проблем с Windows читаю регулярно и нахожу в них очень много нужной информации. Спасибо.

Ваша оценка: Thumb up Thumb down

Вадим, спасибо за ответ — у вас немецкий Excel, как я понял 🙂

Теперь по теме статьи. Вы, безусловно, вправе решать, о чем писать в своем блоге — это даже не обсуждается. Но на мой взгляд количество задач, которые может выполнить Excel, и их специфичность делает работу по описанию решения какой-то конкретной задачи не очень востребованной. Для этого есть форумы посвященные Excel’ю (типа вопрос — ответ). А Ваши статьи по оптимизации и решению проблем с Windows читаю регулярно и нахожу в них очень много нужной информации. Спасибо

Аха, это уже намного лучше сформулировано, чем у Сергея выше, поэтому отвечу подробнее.

О выборе тем
Подавляющее большинство записей блога делится на две категории:

1. «Решения задач» с которыми сталкиваюсь я лично или другие люди (вопросы по почте, форумы). И тематика тут не ограничивается ОС Windows. В частности, я люблю Excel и очень активно использую его на работе. А задачи в нем решаю по мере их поступления.

2. «Как это работает», т.е. технологии, концецпии и т.п. В основом — Windows, но также и ПО (и не забывайте про серию об SSD).

Есть еще «Специально для некоторых постоянных читателей» (вело), и да, ими тоже выражали мне недовольство те, кто не катает 🙂

О тайном знании
Понятно, что лично вас (и многих других) интересует тайное знание (ТЗ) о Windows, которое я доставляю время от времени. Но должен ли я его доставлять в каждой записи? Нет, конечно.

ТЗ, как правило, содержится в материалах о новых продуктах и технологиях, например, в Windows 8 — я пишу о ней (в т.ч. и потому, что пользуюсь ОС сам — см. пункт 1 выше).

Окей, по памяти цитирую письмо, полученное от читателя блога пару недель назад:

«Почему вы пишете только о Windows 8? Большинство пользуется Windows 7 и хотелось бы читать о ней.»

Ответом, кстати, была ссылка на статьи о 7, немалая часть которых была написана в то время, когда большинство еще пользовалось XP 🙂

ТЗ не нужно большинству, им нужно что-то простое и распространенное. Хороший пример — запись о настройке Hyper-V. За исключением пары нюансов, там КО из разряда Next-Next-Next. Однако эта запись является одной из наиболее востребованных посетителями из поисковиков.

О специализированных ресурсах и конкретных задачах
Ресурсы «Вопрос-Ответ» есть по каждой теме, но
1. Нужно их знать.
2. Там должен быть ответ на ваш вопрос.

Да, его можно задать, но зачем, если ответ уже есть в моей рубрике Вопрос — Ответ? 🙂

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

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

Посетители из поисковиков не самоцель, но я рассчитываю на то, что они вольются в постоянную аудиторию и научатся чему-то полезному — в этом и цель.

Хороший пример — запись об ошибке 05 после установки обновлений. Решение описано на тысячах сайтов, наверное, и я не хотел писать в блоге КО. Однако потом подумал, что с учетом относительно высокой позиции блога в поисковиках, такая запись будет полезна, если я изложу в ней причины ошибки и поделюсь тем, о чем не пишет никто (удаление с помощью DISM помогло как минимум одному человеку :).

*

Результат публикации?

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

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

Годится! Согласны? Thumb up Thumb down+8

Сергей: Так скоро мы дойдем до азбуки.
Без обид, Вадим, про Windows у Вас получается куда лучше….
»

Не соглашусь, возможно для кого-то приложения из пакета Office — открытая книга, но для очень многих (и, к сожалению, меня) их функционал остается не освоенным полностью.
Спасибо, Вадим, за Ваш труд! Уже около года остаюсь постоянным читателем блога, чему очень рад! В дискуссиях практически не участвую, так как мои познания и опыт еще очень скромны. Думаю я такой далеко не один. С энтузиазмом читаю свежие и более ранние записи на любые темы и в каждой масса полезного и интересного. Здорово просто, что Вы есть и вкладываете силы и время в этот ресурс. Очень помогает в работе и повседневных делах. Извините за излишнее отступление от темы 🙂
Excel мне помогал при расчетах планируемых и фактических затрат на свадьбу и небольшое строительство. на работе ведем журнал ремонтов. Удобно и наглядно.

Годится! Согласны? Thumb up Thumb down+4

Читайте также:  Как найти одинаковые строки в Excel и выделить их цветом

Евгений, спасибо, что читаете мой блог (в т.ч. предыдущие записи — там много интересного:) и поддерживаете идею этой записи. И я очень рад, что она подвигла вас на отправку первого (судя по имени/e-mail) комментария!

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

Ваша оценка: Thumb up Thumb down

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

Ваша оценка: Thumb up Thumb down+1

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

Ваша оценка: Thumb up Thumb down

Вадим, не знаю в курсе ты или нет. Просто в сводной таблице можно полученные категории сгруппировать вручную магазины в группу «магазины», кафе в «кафе». Перед этим придется потаскать поля, чтобы поля одной группы были рядом, а потом выделяется эта группа, alt+shift+[стрелка вправо]. И так далее. Преимущество в том, что можно видеть сразу суммы по всем группам и не ковыряться каждый раз в фильтрах.

сорри, если получилось заумным тоном ботаника, мол, я умный… Не хотел никого обидеть 🙂

Ваша оценка: Thumb up Thumb down

Павел, я не совсем понял идею, если честно. Возьмите файл^^, запишите видео, киньте ссылку 🙂

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

Ваша оценка: Thumb up Thumb down

Vadim Sterkin,

Записал видео, вот ссылка, как просили: http://youtu.be/N2jEe55xfgA
Сорри, что без голоса, у меня все спят 🙂

Там в экселе встроенных видов группировки много: даты можно группировать помесячно (пишет первые три буквы месяца), произвольное количество дней (например, понедельно), поквартально, по годам и т.п. Цифры можно группировать интервалами (например, 1-100; 101-200; 201-300… и т.п.).

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

Ваша оценка: Thumb up Thumb down+1

Павел, теперь я понял, что вы там перетаскивали 🙂 Отличное видео! Я добавил его в статью 🙂

Ваша оценка: Thumb up Thumb down

Отличное видео и способ группировки, но честно, чтобы использовать возможности сводной, ей изначально немного не доставало данных. Нужно ввести доп. столбец, тогда ни фильтрация, ни группировка не понадобятся — сводная сама все сделает. Вот ссылка на файл http://yadi.sk/d/PEdPVpv5ALzkX

Ваша оценка: Thumb up Thumb down

Аркадий, да, я об этом сразу и написал. Более того, первый черновик статьи содержал шаг «Добавление категорий» 🙂 Убрал я его, находясь под влиянием того, что в моем (реальном) случае подсчет нужно было сделать всего один раз, причем на планшете! Поэтому операция «сводная таблица -> фильтр в ней» вела к цели быстрее всего 😉

Но если таблица обновляется / дополняется, категории сильно упрощают жизнь. Я добавил столбец «Категории» в файл-пример — желающие могут сравнить, насколько проще решается задача, если позаботиться о них заранее.

Ваша оценка: Thumb up Thumb down

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

Ваша оценка: Thumb up Thumb down

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

Здесь две причны:

1. Введение столбца «Категории»^^ позволяет продемонстрировать более эффективный способ решения, что редко бывает, когда я пишу о Windows 🙂

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

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

Ваша оценка: Thumb up Thumb down

Периодически анализирую детализацию из личного кабинета Билайна
Они, наконец, поля с балансом стали форматировать как Числовые и операция по созданию сводной таблицы занимает 5 секунд.
Удобно, что если я вижу аномальную цифру по услуге, можно двойным кликом вывести дополнительные данные: номер исходящего звонка или дату.

Ваша оценка: Thumb up Thumb down

Артем, кстати, Николай Павлов (MVP Excel) в июле опубликовал статью/видео Анализ детализации мобильной связи (Билайн). И он там преобразует в число сначала 🙂 Видимо, до билайна только недавно дошло, что нужно упростить клиентам жизнь.

Ваша оценка: Thumb up Thumb down

Василий: Веду учёт всех расходов в Excel 2007. По столбцам виды расходов, счёт в Сбербанке, доходы (зарплата, пенсия и прочие доходы). Веду учёт уже более 3-х лет. По строкам даты. Ввожу только приход и расход по категориям. Все остальные итоги выводятся автоматически. Подсчёт ведётся и ежедневный и помесячный. Спасибо за новую тему.
»

Василий, заинтересовал ваш способ учета расходов в Excel! Не могли бы Вы скинуть в качестве примера вашу таблицу? Я и сам веду учет в Excel, но не так сложно как у Вас, хотел бы сделать учет доходов/расходов более умным.

Ваша оценка: Thumb up Thumb down

Бюджет и айфон как-то у меня не сочетаются.

Ваша оценка: Thumb up Thumb down+1

Спасибо за статью, прям как по заказу в самую тему, веду учет в экселе экспортируя данные из coinkeeper на ios, и как раз выборка нужна была…как то пытался, но руки не доходили , а тут еще и несколькими способами)
Вчера как раз прикрутил график , а сегодня будет и по категориям)

Ваша оценка: Thumb up Thumb down

Евгений, спасибо что отписал и тут помимо почты — рад, что статья пригодилась и вовремя.

Ваша оценка: Thumb up Thumb down

Если действительно надо посчитать сумму один раз для конкретного параметра повторяющегося несколько раз, то я пользуюсь формулой «суммесли()».

Ваша оценка: Thumb up Thumb down

Я бы третий столбец озаглавил бы «Магазин», где ставил бы единички против магазинов, чётвёртый — аналогично «Кафе». Для подсчёта магазинов использовал формулу с СУММПРОИЗВ(), считающую векторное произведение столбцов с суммами и единичками (пустые ячейки эквивалентны 0). Для кафе — аналогично.
Столбец с единичками для кафе можно описать как =1-С3, т.к. столбцы взаимоисключающие.

Такое вот фильтрующее свойство дельта-функции. 🙂

Ваша оценка: Thumb up Thumb down

Александр, а если у вас 10 тысяч строк, тоже 1/0 ставить будете? 🙂

Ваша оценка: Thumb up Thumb down+1

С такими таблицами (на 10 тысяч строк) не сталкивался. Приходилось работать только с таблицами, где 1/0 хватало за глаза.

Ваша оценка: Thumb up Thumb down

С помощью автосуммы

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

  1. Выделяете числовые значения столбца.
  2. На верхней панели инструментов нажимаете функцию автозаполнения. Кнопка располагается на вкладке Главная в группе Редактирование. Либо используйте комбинацию клавиш ALT + =.

Выделяете числовые значения столбца

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

После нажатия на значок Сумма

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

Если необходимо посчитать сумму ячеек

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

Автосумма

Смотрите также: “Сортировка и фильтрация данных в Excel”

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

  1. Переходим в вкладку “Главная”, левой кнопкой мыши (далее – ЛКМ) нажимаем на последнюю пустую ячейку столбца или строки, по которой нужно посчитать итоговую сумму и нажимаем кнопку “Автосумма”.

Считаем сумму ячеек в Microsoft Excel

Считаем сумму ячеек в Microsoft Excel

Считаем сумму ячеек в Microsoft Excel

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

Считаем сумму ячеек в Microsoft Excel

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

Считаем сумму ячеек в Microsoft Excel

Данный способ достаточно хорош и универсален, но у него есть один существенный недостаток – он может помочь только при работе с данными, последовательно расположенными в одной строке или столбце, а вот большой объем данных подсчитать таким образом невозможно, равно как и не получится пользоваться “Автосуммой” для отдаленных друг от друга ячеек. Допустим, мы выделяем некую область ячеек и нажимаем на “Автосумма”.

Считаем сумму ячеек в Microsoft Excel

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

Считаем сумму ячеек в Microsoft Excel

как посчитать сумму в excel в столбце как в экселе посчитать сумму Автосумма в Excel Автосумма в Excel

Exceltip

Если вы выберите ряд данных какой-нибудь диаграммы и взгляните на строку формул, вы увидите, что ряд данных генерируется с помощью функции РЯД. РЯД – это специальный вид функции, который используется только в контексте создания диаграммы и определяет значения рядов данных. Вы не сможете использовать ее на рабочем листе Excel и не сможете включить стандартные функции в ее аргументы.

Про аргументы функции РЯД

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

АРГУМЕНТ ОБЯЗАТЕЛЬНЫЙ/ НЕ ОБЯЗАТЕЛЬНЫЙ ОПРЕДЕЛЕНИЕ
Имя Не обязательный Имя ряда данных, которое отображается в легенде
Подписи_категорий Не обязательный Подписи, которые появляются на оси категорий (если не указано, Excel использует последовательные целые числа в качестве меток)
Значения Обязательный Значения, используемые для построения диаграммы
Порядок Обязательный Порядок ряда данных

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

функция ряд excel

В строке формул Excel вы можете увидеть примерно такую формулу:

Аргументами функции РЯД являются данные, которые можно найти в диалоговом окне Выбор источника данных:

Имя – аргумент Diag!$B$1 можно найти, если щелкнуть по кнопке Изменить, во вкладке Элементы легенды (ряды) диалогового окна Выбор источника данных. Так как ячейка B1 имеет подпись Значение, ряд данных будет называться соответственно.

Изменение ряда

Подпись_категорий – аргумент Diag!$A$2:$A$100 находится в поле Подписи горизонтальной оси (категории).

Значения – аргумент значений ряда данных Diag!$B$2:$B$100 находится там же, где мы указали имя ряда.

Порядок – так как наша диаграмма имеет всего один ряд данных, то и порядок будет равен 1. Порядок рядов данных отражается в списке поля Элементы легенды (ряды)

Применение именованных диапазонов в функции РЯД

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

Читайте также:  Расчет скользящей средней в Excel и прогнозирование

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

Область сходимости, интервал сходимость и радиус сходимости степенного ряда

Множество значений переменной x , для которых ряд сходится, называется областью сходимости степенного ряда.. Для действительных значений переменной x область сходимости состоит либо из одной точки, либо является некоторым интервалом (интервалом сходимости), либо совпадает со всей осью Ox.

При подстановке в степенной ряд значения x=0 получится числовой ряд

Следовательно, при x=0 сходится любой степенной ряд и, значит, область его сходимости не может быть пустым множеством. Есть степенные ряды, которые сходятся только при x=0 и расходятся при остальных значениях х. Структура области сходимости всех степенных рядов одинакова. Её можно установить с помощью следующей теоремы, которая уже была проиллюстрирована в начале этого урока.

Теорема 1 (теорема Абеля). Если степенной ряд сходится при некотором значении x = x 0 , отличном от нуля, то он сходится, и притом абсолютно, при всех значениях |x| < |x 0 | . Обратите внимание: и отправное значение «икс нулевое» и любое значение «икса», которое сравнивается с отправным, взяты по модулю — без учёта знака.

Следствие. Если степенной ряд расходится при некотором значении x = x 1 , то он расходится и при всех значениях |x| > |x 1 | .

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

В частных случаях интервал сходимости степенного ряда может вырождаться в точку (тогда ряд сходится только при x=0 и считается, что R=0 ) или представлять собой всю числовую прямую (тогда ряд сходится во всех точках числовой прямой и считается, что ).

Таким образом, определение области сходимости степенного ряда заключается в определении его радиуса сходимости R и исследовании сходимости ряда на границах интервала сходимости (при ).

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

Пример 1. Найти область сходимости степенного ряда

Используя формулу (28), найдём радиус сходимости данного ряда:

Исследуем сходимость ряда на концах интервала сходимости . Данный ряд сходится при x=1 и расходится при x=−1 . Следовательно, областью сходимости служит полуинтервал .

Пример 2. Найти область сходимости степенного ряда

Решение. Коэффициенты ряда положительны, причём

Найдём предел этого отношения, то есть радиус сходимости степенного ряда:

Исследуем сходимость ряда на концах интервала . Подстановка значений x=−1/5 и x=1/5 в данный ряд даёт:

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

Пример 3. Найти область сходимости степенного ряда

По формуле (28) находим радиус сходимости ряда:

Исследуем сходимость ряда при значениях . Подставив их в данный ряд, соответственно получим

Оба ряда расходятся, так как не выполняется необходимое условие сходимости (их общие члены не стремятся к нулю при ). Итак, на обоих концах интервала сходимости данный ряд расходится, а область его сходимости – интервал .

Пример 4. Найти область сходимости степенного ряда

Решение. Здесь , а

Следовательно, радиус сходимости ряда

то есть ряд сходится при любом конечном значении x . Область его сходимости – бесконечный интервал .

Пример 5. Найти область сходимости степенного ряда

Решение. Находимо отношение , где , а :

Согласно формуле (28) радиус сходимости данного ряда

то есть ряд сходится только при x=0 и расходится при остальных значениях х .

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

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

Пример 6. Найти интервал сходимости степенного ряда

Решение. Данный ряд не содержит членов с нечётными степенями х. Поэтому преобразуем ряд, полагая . Тогда получим ряд

для нахождения радиуса сходимости которого можно применить формулу (28). Так как , а , то радиус сходимости этого ряда

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

Функциональные ряды

Функциональный ряд – сумма вида sum<n=1><infty><f_n(x)>,</p>
<p>При <img src=называется точкой сходимости функционального ряда. Если в каждой точке x</p>
<p>D» /> числовые ряды сходятся, то функциональный ряд называется <em>сходящимся</em> в области <img src=. Совокупность всех точек сходимости образует область сходимости функционального ряда.

S_k(x)=sum<n=1><k><f_n(x)>» /> – частичные суммы ряда. Функциональный ряд сходится к функции <em>f</em>(<em>x</em>), если <img src=

Геометрический смысл равномерной сходимости:

если окружить график функции y = f(x) «ε-полоской», определяемой соотношением f(x)−ε > y > f(x)+ε, то графики всех частичных сумм Sk(x), начиная с достаточно большого k, ∀x ∈ [a, b] целиком лежат в этой «ε-полоске», окружающей график предельной функции y = f(x).

sum<n=1><infty><f_n(x)>» /> — называется <em>мажорируемым</em> в области , если существует такой сходящийся числовой ряд <img src=
При x_0=0</p>
<p><strong>Область сходимости степенного ряда:</strong> <br />Радиус сходимости, интервал сходимости <em>R</em>, <em>x</em> ∈ (-<em>R, R</em>): <br /><img src=
В итоге получаем:


Идентичного результата также можно добиться и через использование формул:

Как посчитать сумму определенных ячеек в Excel

Если вам нужно посчитать сумму определенных ячеек в Excel, то это также можно сделать при помощи функции «Авто сумма». Для этого нужно выделить мышкой ячейку таблицы, в которую вы хотите поместить сумму, нажать на кнопку «Авто сумма» и выбрать нужные ячейки удерживая зажатой клавишу CTRL на клавиатуре. После того, как нужные ячейки выбраны, нажмите на клавишу Enter на клавиатуре, и в выбранной вами ячейке таблицы будет помещена сумма.

Кроме этого, вы можете вписать формулу для подсчета суммы определенных ячеек вручную. Для этого установите курсор там, где должна находится сумма, а потом введите формулу в формате: =СУММ(D3; D5; D7). Где вместо D3, D5 и D7 – адреса нужных вам ячеек. Обратите внимание, адреса ячеек вводятся через запятую, после последней ячейки запятая не нужна. После ввода формулы нажмите клавишу Eneter и в выбранной вами ячейке появится сумма.

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

Второй способ упрощения формулы для частичной суммы.

Этот способ основан на свойстве, записанном в начале страницы. По сути, он схож с предыдущим, – разница лишь в применении уже готовой теоремы, доказанной нами ранее. Вернёмся к записи общего члена ряда:

Обозначим $b_n=\frac<-1><2n+1>$, тогда $b_=\frac<-1><2(n+1)+1>=\frac<-1><2n+3>$. Таким образом, $u_=b_-b_$. При этом $\lim_b_n=0$. Согласно упомянутому свойству, ряд $\sum\limits_^<\infty>u_n$ сходится. При этом его сумма равна $S=0-b_1=\frac<1><3>$. Если есть необходимость, можно записать и частичную сумму ряда:

Примеры решений

Так как ряд представляет собой бесконечною убывающую геометрическую прогрессию, то воспользуемся формулой: $ S = frac <1-q>$

Первый член прогрессии при $ n = 1 $ равен: $ b_1 = frac<1> <9>$ Основанием является: $ q = frac<1> <3>$

Подставляя всё это в формулу для вычисления суммы получаем:

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

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

Приравниваем числитель последней дроби к числителю первой дроби:

$ 2An + 3A + 2Bn + B = 1 $

Теперь определяем находим неизвестные коэффициенты:

После разложения общий член ряда записывается следующим образом:

Далее составим частичную сумму ряда: $ S_n = a_1 + a_2 + a_3 + a_4 + . + a_n $

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

Обратите внимание, чтобы составить $ a_ $ необходимо подставить в $ a_n $ вместо буковки $ n $ выражение $ n-1 $. После выполнить раскрытие скобок.

Выносим дробь одну вторую $ frac<1> <2>$ за скобки:

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

Теперь осталось вычислить предел частичной суммы $ S_n $. Если он существует и конечен, то он является суммой ряда, а сам ряд сходится:

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

Ссылка на основную публикацию