Excel строки за квартал - Word и Excel - помощь в работе с программами

Содержание

Excel квартал
Номер квартала в Excel.
Дата конца и начала квартала в MS EXCEL
3 функции для определения номера квартала по дате в Excel
Определение квартала с использованием функции ВЫБОР в Excel
Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?
Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?
Определение номеров нестандартных кварталов по дате
Формат даты в кварталах (Формулы/Formulas)
преобразовать дату в квартал (Формулы/Formulas)
Выбрать даты по кварталам (Формулы/Formulas)
Данные за квартал (Формулы/Formulas)
3 функции для определения номера квартала по дате в Excel
Определение квартала с использованием функции ВЫБОР в Excel
Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?
Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?
Определение номеров нестандартных кварталов по дате

Excel квартал

Номер квартала в Excel.

Дата конца и начала квартала в MS EXCEL

3-й квартал, 1 не должно бытьShAM просто..Когда я имел иметь отдельную ячейку боли. И функция помощью функции ОКРУГЛВВЕРХ: это 1, май

в Excel неExcel.Третий вариант. в пустой ячейке. сравнение с первой подставить, чтобы расчитывались ячейки количество дат,

: Не подскажите можно: раз 4-й, затем

3 функции для определения номера квартала по дате в Excel

поскольку эта дата: Тогда перестаем гадать. ввиду, что ячейка с датой? простая для запоминания=ОКРУГЛВВЕРХ(B2/3;0) — 2, и, предусмотрена функция КВАРТАЛФормула такая. =ДАТА(ГОД(B2);ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)*3+1;)В ячейке будет

Определение квартала с использованием функции ВЫБОР в Excel

Есть несколько вариантов. датой и последней данные за 2 попадающих в определенный ли как-то преобразоватьRussel опять 3 раза нужна для дальнейших Давайте файл и используется для вычислений,

ShAM и без запоминанияКак видно на рисунке например, ноябрь - определить номер квартала Получится так. написан номер кварталаНапример, в ячейке датой года. квартал и за

квартал. дату в соответствующий, Пардон, не обратил 3-й и т.д. расчетов пояснения, зачем нужен я имел ввиду: Правильно понимаете. какой-либо логики. все три способа

4. по дате можноКак определить по и год. Напишем В2 пишем датуLiana88

год.Файл приложен. квартал!

внимания, что 5я

Затем я закрываюShAM именно такой формат. в частности, чтоЗЫ: Поменял файлLyova

без ошибочно возвращаютИ это то, что с помощью функций дате день недели

такую формулу. (для всех формул: Еще вопрос, еслиФормулы в файлеPelena

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

дата записана в то строка не файл, сохраняя изменения,

, Я прошу прощения,

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

А вот нефиг далее в формулах в предыдущем сообщении.: Здравствуйте!

функция ВЫБОР от ВЫБР, ИНДЕКС и (понедельник, вторник, т.д.),=ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)&» кв. «&ТЕКСТ(B2;»ГГГГ»)&» в этой статье я хочу высчитать

написаны, но везде

Определение номеров нестандартных кварталов по дате

: Здравствуйте. формате «04.01.2014»? пустая. Согласен, в открываю его вновь перемудрил я чего-то было без примера в других ячейкахRusselВ ячейке стоит же номера кварталов нас требует: номер ОКРУГЛВВЕРХ. Рассмотрим все смотрите в статье г.» дата стоит в количество конкретного товара выходит 0. Посмотрите,Сводной можно сPelena

принципе можно и

с фантазиями относительного помогать.

анализируется наличие в: Используйте конструкцию формула =ДАТАМЕС(A1;1), которая по исходным датам. аргумента, который должен на простых конкретных «День недели поПолучилось так.

Формат даты в кварталах (Формулы/Formulas)

 ячейке В2). за квартал конкретного
пожалуйста. группировкой по кварталам:
так реализовать, спасибо! располагаются в нормальном возможных вариантов измененияRussel заданной ячейке именно
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=(ЧАСТНОЕ(МЕСЯЦ(ДАТАМЕС(A1;1))-1;3)+1)&» квартал «&ГОД(ДАТАМЕС(A1;1)) возвращает дату 30.09.2014. отобразиться и, собственно,

 примерах. дате Excel».Четвертый вариант.Первый вариант года.

Nic70ysboy200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОТБР((МЕСЯЦ(A1)-1)/3)+1
Lyova хронологическом порядке.

 таблицы. И Вас: Ага, а в даты 30.09.2014, поэтомуили ту, чтоМожно ли сделатьФункции ВЫБОР или ИНДЕКС значения всех аргументов.Функция ВЫБОР мало известна,Какие ещё бываютНаписать номер квартала в

.Например, сколько было
:: Добрый день.

Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОКРВВЕРХ(МЕСЯЦ(A1)/3;1):
Не знаете почему
и себя запутал. свою очередь эта и написал, что предложил
 так, чтобы эта имеют одно преимуществоТаким образом, в ячейке но очень полезна. формулы, что можноExcel римскими числамиНомер квартала в
ПУ за первый200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН(K:K;K1;H:H;4)+СЧЁТЕСЛИМН(K:K;K1;H:H;5)+СЧЁТЕСЛИМН(K:K;K1;H:H;6)как вариант формулойAlexM
ShAM так происходит? Вариант с УФ дата образуется от прицеплять текст не

VEKTORVSFREEMAN дата отражалась как над ранее описанным E4 пишем следующую Что она делает? сделать в помощью. Напишем такую формулуExcel квартал 2011 годакитин (пример для 1-го:, Забавно, а яRussel вполне подходит. той, что в получиться, поскольку должен, а по поводу «3 кв.2014» или методом с функцией функцию: Конечно же, выбирает. формул, кроме расчета в ячейке С3.
будет написан вСоздала дополнительную колонку: чисто по примеру квартала)200?’200px’:»+(this.scrollHeight+5)+’px’);»>=-ЦЕЛОЕ(-МЕСЯЦ(A1)/3) повторил алгоритм «скопировал-сохранил-закрыл-открыл»: Ну так ссылайтесьЕдинственный ньюанс, заметил ячейке А1. Так поменяться лишь формат,Цитата «3 квартал 2014»? ОКРУГЛВВЕРХ. Их можно=ВЫБОР(D4;1;1;1;2;2;2;3;3;3;4;4;4) Она выбирает указанное чисел, смотрите в
=РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)) Смотрите ячейку виде арабской цифры. год.

Pelena200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН($D$4:$D$30;»>31.12.2015″;$D$4:$D$30;»cavstarica 5 раз и не на 4
интересную особенность. Если почему бы и а восприятие эксель

Lyova, 07.10.2014 вЭта ячейка дальше использовать, когда вЧтобы применить данную формулу нами значение среди статье «Формула в С3.В ячейке В3bmv98rus: Ещё вариант (перваяNic70y: В продолжении темы,

 каждый раз одно строку, а на

 я ставлю курсор, не анализировать ячейку этой даты должно

 01:30, в сообщении используется в расчетах,

 нашей компании применяется на практике будем списка аргументов. Среди Excel».Пятый вариант. будет написано, какой: Liana88, формула): скажите. как применить и тоже. Ну что 5-ю: например в S5
 А1, а здесь остаться прежним. № 1200?’200px’:»+(this.scrollHeight+5)+’px’);»>ячейка дальше поэтому прицеплять слова нестандартный расчетный год. использовать функцию МЕСЯЦ этих аргументов можетНайдем дату конца квартала,Формула такая. =РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3))&»
это квартал. ВОжидаемый вопрос,200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(СЧЁТЕСЛИМН(K:K;»<>«&K1;H:H;<4;5;6>))200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН($D$4:$D$30;»>=»&ДАТА(ПРАВБ(F3;4);ЛЕВБ(F3)*3-2;1);$D$4:$D$30;» к данному списку? же, запишем в200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОИСКПОЗ(D1;R5:Z5;0) (здесь отражается 2 пусть будет простоShAM используется в расчетах, не получиться Когда я пишу для определения порядкового быть все: числа; к которому относится кв.» Получится так. ячейку В3 пишемэто упростит всеLiana88Dryha Т.е.даты рождения нужно «невыясненное».Я сделал Вам квартал), и копирую информативный текст с, Ваш вариант, в
поэтому прицеплять словаVEKTORVSFREEMAN «нестандартный расчетный год», номера месяца для текст; диапазоны заданная дата. Например,
Шестой вариант. такую формулу. =ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)

 и решит сразу: Спасибо большущее всем,: разделить по кварталам.Спасибо за помощь!
две строки:
ячейку вправо, протягивая кварталом?
принципе, подходит, хотя не получиться: Lyova, вот вариант,
то имею в исходной даты:Потребовалось некоторое время, чтобы если задана дата

Формула такая. =РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3))&» Получилось так.
и первый и пошла разбираться подробнее.Pelena Спасибо заранее.ShAM- 4я строка ее, например до

Lyova немного трудоемкий ив расчетах используйте только не через виду следующее: годТеперь каждому номеру месяца убедиться в полезности 05.11.12, то дата кв.»&ТЕКСТ(B2;» ГГГГ»)&» г.»

Изменим число, получится так. второй вопрос.bmv98rus, Спасибо!Pelena: Может, в 2007 для печати формата столбца AR, то: Соорудил пример. не очень универсальный.
вместо ссылки на

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

 конца квартала 31.12.12.Получится так.Второй вариант.Ну или добавьте: Пока писал всеDryha: И что не

 себя так ведет «1 кв. 2014», начиная со столбцаShAM
Я над ним

преобразовать дату в квартал (Формулы/Formulas)

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

Номер квартала будет еще одно умножение уже разобрали.
: Всем спасибо!

 получается? Любая из (у меня 2010)? - 5я строка

 T, после каждого: Ну, и чем тоже думал, но первоначальную =ДАТАМЕС(A1;1).ShAM не с января. ВЫБОР:

 определением квартала - начала соответствующего квартала.Excel определить начало и записан так – на (L1:L19=2011)

Хотя нет, неjakim предложенных формул справитсяLyova для вычислений формата 4 квартала идет

Выбрать даты по кварталам (Формулы/Formulas)

 УФ не подходит? в исходном файле
VEKTORVSFREEMAN: VEKTORVSFREEMAN, так нельзя, Даже с июля.Как видно на рисунке она огромна. ЗдесьПусть в ячейке
коней квартала

 1 кв. Формулу200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT(ISTEXT($E$2:$E$19)*($H$2:$H$19>=1)*($H$2:$H$19
все.: С функцией СУМПРОИЗВ.

 с этой задачей: Так похоже и
«01.01.2014» цвет шрифта=цвету 2 раза 2Russel
рассматривается 15-летний период

, если используешь амперсанды т.к.: ИНДЕКС и ВЫБОР

 данное решение реализуется каждые 3 месяцаА1.

AlexM есть, перекинул файле заливки квартал, потом 3-й : Вот такие костыли времени, и сама для слияния текста,

Данные за квартал (Формулы/Formulas)

Lyova, с УФ выглядели бы тогда 1 + двенадцатью относятся к следующемуимеется дата 05.11.12.Как определить=ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)&» кв.»: Всё получилось, все
можно же просто: Формула для первого: В любой формуле коллеге, у него

ShAM и 4-й. При Lyova

 область данных не функция СЦЕПИТЬ необязательна,

 подойдет (в файле)? следующим образом: аргументами с логической
кварталу. То есть,

 Записав формулу =КОНМЕСЯЦА(A1;ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)*3-МЕСЯЦ(A1))начало квартала по датеОбратите внимание

 данные сходятся, ВыКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=COUNTIFS(K:K;K1;H:H;»>3″;H:H;»=ROUNDUP(MONTH(G4)/3;0) квартала
поменять А1 на 2010 — у
: Не знаю. этом соответствующий год
:
статична, т.е. могут т.е. СЦЕПИТЬ(А;Б)=А&БVEKTORVSFREEMAN=ИНДЕКС(<3;3;3;4;4;4;1;1;1;2;2;2>;1;B2) последовательностью номеров квартала. схема будет такой: определим дату конца в. настоящие гении, огромноену и с

200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(Ч(МЕСЯЦ(D4:D30)=<1;2;3>)) F2 вставить в него тоже всеУ меня все отражается правильно. ЕслиRussel
добавляться, либо удалятьсяLyova: ShAM, интересная задумка
=ВЫБОР(B2;3;3;3;4;4;4;1;1;1;2;2;2)Такой же принцип можно

Здесь принимаем, что каждый квартала, к которому
Excel
В формуле перед спасибо. годом =2012 Неи массивный вариантКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТ(1/(МЕСЯЦ(D4:D30)=<1;2;3>))Для
ячейку и тянуть нормально нормально. Встал на
ставлю в ячейку

, результат Вашей формулы столбцы и добавляться: с УФ. ПравильноДля наших данных результат

Источник

3 функции для определения номера квартала по дате в Excel

Несмотря на то что в Excel не предусмотрена функция КВАРТАЛ определить номер квартала по дате можно с помощью функций ВЫБР, ИНДЕКС и ОКРУГЛВВЕРХ. Рассмотрим все на простых конкретных примерах.

Определение квартала с использованием функции ВЫБОР в Excel

Функция ВЫБОР мало известна, но очень полезна. Что она делает? Конечно же, выбирает. Она выбирает указанное нами значение среди списка аргументов. Среди этих аргументов может быть все: числа; текст; диапазоны

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

Здесь принимаем, что каждый номер месяца — это номер аргумента функции ВЫБОР, который мы хотим отобразить. То есть, январь это 1, май — 2, и, например, ноябрь — 4.

И это то, что функция ВЫБОР от нас требует: номер аргумента, который должен отобразиться и, собственно, значения всех аргументов.

Таким образом, в ячейке E4 пишем следующую функцию:

Чтобы применить данную формулу на практике будем использовать функцию МЕСЯЦ для определения порядкового номера месяца для исходной даты:

Теперь каждому номеру месяца присвоим порядковый номер квартала используя функцию ВЫБОР:

Как видно на рисунке данное решение реализуется 1 + двенадцатью аргументами с логической последовательностью номеров квартала.

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Такой же принцип можно использовать с помощью функции ИНДЕКС:

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Простой формулой определяем квартал для каждого месяца и даты с помощью функции ОКРУГЛВВЕРХ:

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

Определение номеров нестандартных кварталов по дате

Функции ВЫБОР или ИНДЕКС имеют одно преимущество над ранее описанным методом с функцией ОКРУГЛВВЕРХ. Их можно использовать, когда в нашей компании применяется нестандартный расчетный год. Когда я пишу «нестандартный расчетный год», то имею в виду следующее: год может начинаться с другого месяца, а не с января. Даже с июля. ИНДЕКС и ВЫБОР выглядели бы тогда следующим образом:

Для наших данных результат был бы следующим:

С функцией ОКРУГЛВВЕРХ это было бы сложнее. И тут простое решение, без головной боли. И функция простая для запоминания и без запоминания какой-либо логики.

Источник

Как все мы знаем, квартал состоит из трех месяцев в году. Предположим, у вас есть большой рабочий лист, содержащий данные о продажах за год, и теперь вы хотите отфильтровать строки за определенный квартал — квартал 3. То есть вам нужно отфильтровать три месяца (июль, август и сентябрь). data, как можно быстро и легко справиться с этой задачей в Excel?

Отфильтруйте данные по кварталу с помощью функции фильтрации

Отфильтруйте данные по кварталу с помощью вспомогательного столбца

Фильтровать данные по кварталам с помощью Kutools for Excel

Отфильтруйте данные по кварталу с помощью функции фильтрации

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

1. Выберите столбец даты, который вы хотите отфильтровать.

2. Затем нажмите Данные > Фильтр.

3, И кнопка будет отображаться в правом нижнем углу ячейки заголовка, щелкните ее, а затем выберите Фильтры даты > Все даты периода, а затем нажмите Четверть что вы хотите отфильтровать. Смотрите скриншот:

4. И все данные, соответствующие вашим критериям, были отфильтрованы, как показано на следующем снимке экрана:

Отфильтруйте данные по кварталу с помощью вспомогательного столбца

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

1. Во-первых, вам нужна формула для извлечения названия квартала из даты, введите это: = (TRUNC ((МЕСЯЦ (D2) -1) / 3)) + 1(D2 указывает дату, которую вы хотите преобразовать в название квартала) см. снимок экрана:

2. Затем перетащите дескриптор заполнения в ячейку, в которой вам нужно применить эту формулу, и все имена кварталов были извлечены, как показано на следующем снимке экрана: 1 означает квартал 1, 2 — квартал 2, 3 — квартал 3, а 4 — квартал 4. .

3. На этом этапе вы можете выполнить фильтрацию по этому новому столбцу, щелкнув Данные > Фильтр, А затем нажмите кнопку, в контекстном меню отметьте Четверть которые вы хотите отфильтровать. Смотрите скриншот:

4, Затем нажмите OK Кнопка, все строки, принадлежащие кварталу 3, отфильтрованы.

Фильтровать данные по кварталам с помощью Kutools for Excel

Помимо двух вышеупомянутых методов, здесь я могу представить вам мощную утилиту —Kutools for Excel, С его Суперфильтр функция, вы можете закончить более сложную задачу фильтра.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.

1. Пожалуйста, подайте заявку Kutools for Excel кликнув Предприятие > Суперфильтр, см. снимок экрана:

2. В выскочившем Суперфильтр диалоговом окне выполните следующие настройки:

A: Нажмите кнопку, чтобы выбрать диапазон данных, который вы хотите отфильтровать. (По умолчанию выбран используемый вами диапазон)
B: Выберите отношения, которые вам нужны.
C: Затем выберите критерии в поле условия.

3. После завершения настройки условий нажмите Фильтр кнопку, и ваши данные за квартал будут отфильтрованы.

Примечание: Чтобы восстановить исходные данные, вы можете нажать Очистить в Суперфильтр Диалог.

Чтобы узнать больше об этой функции суперфильтра.

Статьи по теме:

Как фильтровать даты по месяцам с игнорированием года в Excel?

Как фильтровать данные по нескольким критериям в Excel?

Как фильтровать только целые или десятичные числа в Excel?

Как отфильтровать данные, начинающиеся с цифры или буквы в Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Определение квартала с использованием функции ВЫБОР в Excel

Таким образом, в ячейке E4 пишем следующую функцию:

=ВЫБОР(D4;1;1;1;2;2;2;3;3;3;4;4;4)

Теперь каждому номеру месяца присвоим порядковый номер квартала используя функцию ВЫБОР:

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Такой же принцип можно использовать с помощью функции ИНДЕКС:

=ИНДЕКС({1;1;1;2;2;2;3;3;3;4;4;4};1;B2)

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Простой формулой определяем квартал для каждого месяца и даты с помощью функции ОКРУГЛВВЕРХ:

=ОКРУГЛВВЕРХ(B2/3;0)

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

Определение номеров нестандартных кварталов по дате

=ИНДЕКС({3;3;3;4;4;4;1;1;1;2;2;2};1;B2)

=ВЫБОР(B2;3;3;3;4;4;4;1;1;1;2;2;2)

Для наших данных результат был бы следующим:

Источник

Номер квартала в Excel.

Смотрите также пройдет. либо вспомогательный  других кварталов поменять внизSerge_007 S5 протянул до T5 (где отражается
не воспринимается эксель в ряду датыVEKTORVSFREEMAN понимаю, что под был бы следующим: функции ИНДЕКС:
 номер месяца - относится эта дата.
.  буквами «кв.» поставилиЧтобы столбик с годом
числа в массивахDryha: Думаю что именно AR5, все правильно, 3 квартал) и как дата в до даты 30.09.2014.
,
каждый квартал нужноС функцией ОКРУГЛВВЕРХ это=ИНДЕКС({1;1;1;2;2;2;3;3;3;4;4;4};1;B2) это номер аргумента
Формула =КОНМЕСЯЦА(A1;ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3-1)*3-МЕСЯЦ(A1))+1 вернет дату
Напишем такую формулу. один пропуск, чтобы
определить в (конечно если данныеLiana88: Добрый день! через формат ячейки
до «3 кв.2020».
 также копирую дальше,
формате эксель, поэтомуМожет можно как-тоRussel прописывать формулу в
было бы сложнее.Простой формулой определяем квартал
функции ВЫБОР, который
 начала, т.е. 01.10.12
 =ДАТА(ГОД(B2);ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)*3-2;1) Получится так.  не сливались буквыExcel по дате номер не только за: Всем здравствуйте, оченьНеобходимо посчитать даты
 решения нет
Ничего не закрывал то в ряду
 вводя =ПОИСКПОЗ(D1;R4:Z4;0) выдается
проще, наверняка ведь, может я не
УФ или под
 И тут простое  для каждого месяца мы хотим отобразить. (см. файл примера).
Конец квартала по дате с цифрой. квартала  этот год), либо прошу помочь, не
по кварталам иАналогичная тема: http://forum.msexcel.ru/index.php?topic=4523.0
 и не открывал. отражается 3 раза  ошибка, а так
как-то можно? до конца объяснил
каждый квартал необходимо решение, без головной и даты с То есть, январьНесмотря на то что в
Получится так., нужно написать формулу менять на двойное могу понять, что занести в желтыеАЛЕКСАНДР1986Lyova

excel-office.ru

Дата конца и начала квартала в MS EXCEL

: Не подскажите можно: раз 4-й, затем

excel2.ru

3 функции для определения номера квартала по дате в Excel

Определение квартала с использованием функции ВЫБОР в Excel

4. по дате можноКак определить по и год. Напишем В2 пишем датуLiana88

год.Файл приложен. квартал!

внимания, что 5я

Затем я закрываюShAM именно такой формат. в частности, чтоЗЫ: Поменял файлLyova

без ошибочно возвращаютИ это то, что с помощью функций дате день недели

такую формулу. (для всех формул: Еще вопрос, еслиФормулы в файлеPelena

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

дата записана в то строка не файл, сохраняя изменения,

, Я прошу прощения,

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

А вот нефиг далее в формулах в предыдущем сообщении.: Здравствуйте!

одни и те

написаны, но везде

Определение номеров нестандартных кварталов по дате

принципе можно и

— все кварталы

с фантазиями относительного помогать.

exceltable.com

Формат даты в кварталах (Формулы/Formulas)

 примерах. дате Excel».Четвертый вариант.Первый вариант года.

Nic70ysboy200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОТБР((МЕСЯЦ(A1)-1)/3)+1
Lyova хронологическом порядке.

.Например, сколько было
:: Добрый день.

Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОКРВВЕРХ(МЕСЯЦ(A1)/3;1):
Не знаете почему
и себя запутал. свою очередь эта и написал, что предложил
 так, чтобы эта имеют одно преимуществоТаким образом, в ячейке но очень полезна. формулы, что можноExcel римскими числамиНомер квартала в
ПУ за первый200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН(K:K;K1;H:H;4)+СЧЁТЕСЛИМН(K:K;K1;H:H;5)+СЧЁТЕСЛИМН(K:K;K1;H:H;6)как вариант формулойAlexM
ShAM так происходит? Вариант с УФ дата образуется от прицеплять текст не

 каждый раз одно строку, а на

 я ставлю курсор, не анализировать ячейку этой даты должно

 01:30, в сообщении используется в расчетах,

 нашей компании применяется на практике будем списка аргументов. Среди Excel».Пятый вариант. будет написано, какой: Liana88, формула): скажите. как применить и тоже….Ну что 5-ю: например в S5
 А1, а здесь остаться прежним… № 1200?’200px’:»+(this.scrollHeight+5)+’px’);»>ячейка дальше поэтому прицеплять слова нестандартный расчетный год. использовать функцию МЕСЯЦ этих аргументов можетНайдем дату конца квартала,Формула такая. =РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3))&»
это квартал. ВОжидаемый вопрос,200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(СЧЁТЕСЛИМН(K:K;»<>»&K1;H:H;{4;5;6}))200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН($D$4:$D$30;»>=»&ДАТА(ПРАВБ(F3;4);ЛЕВБ(F3)*3-2;1);$D$4:$D$30;» к данному списку? же, запишем в200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОИСКПОЗ(D1;R5:Z5;0) (здесь отражается 2 пусть будет простоShAM используется в расчетах, не получиться Когда я пишу для определения порядкового быть все: числа; к которому относится кв.» Получится так. ячейку В3 пишемэто упростит всеLiana88Dryha Т.е.даты рождения нужно «невыясненное».Я сделал Вам квартал), и копирую информативный текст с, Ваш вариант, в
поэтому прицеплять словаVEKTORVSFREEMAN «нестандартный расчетный год», номера месяца для текст; диапазоны заданная дата. Например,
Шестой вариант. такую формулу. =ЦЕЛОЕ((МЕСЯЦ(B2)+2)/3)

 и решит сразу: Спасибо большущее всем,: разделить по кварталам.Спасибо за помощь!
две строки:
ячейку вправо, протягивая кварталом?
принципе, подходит, хотя не получиться: Lyova, вот вариант,
то имею в исходной даты:Потребовалось некоторое время, чтобы если задана дата

Изменим число, получится так. второй вопрос.bmv98rus, Спасибо!Pelena: Может, в 2007 для печати формата столбца AR, то: Соорудил пример… не очень универсальный.
вместо ссылки на

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

 конца квартала 31.12.12.Получится так.Второй вариант.Ну или добавьте: Пока писал всеDryha: И что не

 себя так ведет «1 кв. 2014», начиная со столбцаShAM
Я над ним

excelworld.ru

преобразовать дату в квартал (Формулы/Formulas)

Номер квартала будет еще одно умножение уже разобрали.
: Всем спасибо!

 получается? Любая из (у меня 2010)?- 5я строка

 T, после каждого: Ну, и чем тоже думал, но первоначальную =ДАТАМЕС(A1;1).ShAM не с января. ВЫБОР:

 определением квартала - начала соответствующего квартала.Excel определить начало и записан так – на (L1:L19=2011)

Хотя нет, неjakim предложенных формул справитсяLyova для вычислений формата 4 квартала идет

excelworld.ru

Выбрать даты по кварталам (Формулы/Formulas)

 1 кв. Формулу200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT(ISTEXT($E$2:$E$19)*($H$2:$H$19>=1)*($H$2:$H$19
все.: С функцией СУМПРОИЗВ.

 с этой задачей: Так похоже и
«01.01.2014» цвет шрифта=цвету 2 раза 2Russel
рассматривается 15-летний период

, если используешь амперсанды т.к.: ИНДЕКС и ВЫБОР

данное решение реализуется каждые 3 месяцаА1.

напишем такую.Liana88

200?'200px':''+(this.scrollHeight+5)+'px');">=COUNTIFS(K:K,K1,H:H,4)+COUNTIFS(K:K,K1,H:H,5)+COUNTIFS(K:K,K1,H:H,6)AlexM

AlexM есть, перекинул файле заливки квартал, потом 3-й: Вот такие костыли времени, и сама для слияния текста,

excelworld.ru

Данные за квартал (Формулы/Formulas)

ShAM и 4-й. ПриLyova

 область данных не функция СЦЕПИТЬ необязательна,

 подойдет (в файле)? следующим образом: аргументами с логической
кварталу. То есть,

 Записав формулу =КОНМЕСЯЦА(A1;ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)*3-МЕСЯЦ(A1))начало квартала по датеОбратите внимание

 данные сходятся, ВыКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=COUNTIFS(K:K;K1;H:H;»>3″;H:H;»=ROUNDUP(MONTH(G4)/3;0) квартала
поменять А1 на 2010 — у
: Не знаю. этом соответствующий год
:
статична, т.е. могут т.е. СЦЕПИТЬ(А;Б)=А&БVEKTORVSFREEMAN=ИНДЕКС({3;3;3;4;4;4;1;1;1;2;2;2};1;B2) последовательностью номеров квартала. схема будет такой: определим дату конца в. настоящие гении, огромноену и с

200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(Ч(МЕСЯЦ(D4:D30)={1;2;3})) F2 вставить в него тоже всеУ меня все отражается правильно. ЕслиRussel
добавляться, либо удалятьсяLyova: ShAM, интересная задумка
=ВЫБОР(B2;3;3;3;4;4;4;1;1;1;2;2;2)Такой же принцип можно

Здесь принимаем, что каждый квартала, к которому
Excel
В формуле перед спасибо. годом =2012 Неи массивный вариантКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТ(1/(МЕСЯЦ(D4:D30)={1;2;3}))Для
ячейку и тянуть нормально нормально. Встал на
ставлю в ячейку

, результат Вашей формулы столбцы и добавляться: с УФ. ПравильноДля наших данных результат


excelworld.ru

использовать с помощью

Источник

#Руководства

13 май 2022
0

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

Иллюстрация: Meery Mary для Skillbox Media

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

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

Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».

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

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

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

Таблица, в которой хранятся данные о продажах автосалона
Скриншот: Skillbox Media

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

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

Создаём сводную таблицу

Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:

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

Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».

Жмём сюда, чтобы создать сводную таблицу
Скриншот: Skillbox Media

Появляется диалоговое окно. В нём нужно заполнить два значения:

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

В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».

Выделяем диапазон исходной таблицы и отмечаем лист, где разместится сводная
Скриншот: Skillbox Media

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

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

Появился новый лист для сводной таблицы
Скриншот: Skillbox Media

Настраиваем сводную таблицу и получаем результат

В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».

Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:

«Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Если данные выбранного поля в числовом формате, программа просуммирует их значения (например, рассчитает общую стоимость проданных автомобилей). Если формат данных текстовый — программа покажет количество ячеек (например, определит количество проданных авто).
«Строки» и «Столбцы» — отвечают за визуальное расположение полей в сводной таблице. Если выбрать строки, то поля разместятся построчно. Если выбрать столбцы — поля разместятся по столбцам.
«Фильтры» — отвечают за фильтрацию итоговых данных в сводной таблице. После построения сводной таблицы панель фильтров появляется отдельно от неё. В ней можно выбрать, какие данные нужно показать в сводной таблице, а какие — скрыть. Например, можно показывать продажи только одного из менеджеров или только за выбранный период.

Настроить сводную таблицу можно двумя способами:

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

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

В случае с нашим примером нужно, чтобы сводная таблица отразила ФИО менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.

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

После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.

Добавляем в сводную таблицу поле «Продавцы» через область «Строки»
Скриншот: Skillbox

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

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

Добавляем в сводную таблицу поле «Марка, модель» через область «Строки»
Скриншот: Skillbox Media

Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.

Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».

Добавляем в сводную таблицу поля «Марка, модель» и «Цена» через область «Значения»
Скриншот: Skillbox Media

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

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

Настраиваем фильтры сводной таблицы

Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».

В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.

Над сводной таблицей появился дополнительный блок с фильтрами
Скриншот: Skillbox Media

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

В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:

Появилось всплывающее окно для фильтрации
Скриншот: Skillbox Media

В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.

Фильтруем таблицу по году выпуска проданных автомобилей
Скриншот: Skillbox Media

Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.

Так выглядит отфильтрованная сводная таблица
Скриншот: Skillbox Media

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

Проводим дополнительные вычисления

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

Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».

Меняем структуру квартальных продаж менеджеров на процентную
Скриншот: Skillbox

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

Сводная таблица самостоятельно рассчитала процент продаж за квартал для каждого менеджера
Скриншот: Skillbox Media

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

Так сводная таблица выглядит в свёрнутом виде
Скриншот: Skillbox Media

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».

Обновляем данные сводной таблицы

Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.

В исходной таблице появились две дополнительные строки
Скриншот: Skillbox

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

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

Жмём сюда, чтобы изменить исходный диапазон
Скриншот: Skillbox Media

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

Добавляем в исходный диапазон две новые строки
Скриншот: Skillbox Media

После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.

Данные в сводной таблице обновились автоматически
Скриншот: Skillbox Media

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

Например, поменяем цены двух автомобилей в таблице с продажами.

Меняем данные двух ячеек в исходной таблице
Скриншот: Skillbox Media

Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».

Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».

Жмём сюда, чтобы обновить данные
Скриншот: Skillbox Media

Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:

Так выглядит сводная таблица в «Google Таблицах»
Скриншот: Skillbox Media

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Источник

Имея дату (01/02/09), определим квартал, к которому она принадлежит (ответ: первый квартал).

Найдем номер квартала для любой даты из ячейки

B5

. Для этого используем формулу (см.

файл примера

):

=ЦЕЛОЕ((МЕСЯЦ(B5)+2)/3)

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

4 кв. 2010г.

=

ЦЕЛОЕ((МЕСЯЦ(B5)+2)/3)&» кв. «&ТЕКСТ(B5;»гггг»)&»г. »

Чтобы вывести значение в формате

IV кв. 2011г.

запишем формулу:

=РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(

B5

)-1)/3)+1) & » кв. » & ГОД(

B5

)& «г.»

В файле примера создана таблица с датами. Для каждой даты вычислен номер квартала. Даты, относящиеся к Четным кварталам выделены

Условным форматированием

Источник

8 простых способов как посчитать в Excel сумму столбца

Как посчитать сумму в Excel быстро и просто? Чаще всего нас интересует итог по столбцу либо строке. Попробуйте различные способы найти сумму по столбцу, используйте функцию СУММ или же преобразуйте ваш диапазон в «умную» таблицу для простоты расчетов, складывайте данные из нескольких столбцов либо даже из разных таблиц. Все это мы увидим на примерах.

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

Как суммировать весь столбец либо строку.

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

Если поставить формулу суммы в G2

=СУММ(B:B)

то получим общую выручку по первому отделу.

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

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

Все сказанное выше в полной мере относится и к работе со строками.

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

Суммируем диапазон ячеек.

Важно научиться правильно указать диапазон данных. Вот как это сделать, если суммировать продажи за 1-й квартал:

Формула расчета выглядит так:

=СУММ(B2:D4)

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

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

=СУММ(B2:D4;B8:D10)

Естественно, их может быть не два, а гораздо больше: до 255 штук.

Как вычислить сумму каждой N-ой строки.

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

Иначе говоря, нужно, двигаясь вниз, взять каждую третью строчку.

Это можно сделать двумя способами.

Первый – самый простой, «в лоб». Складываем все цифры нужного отдела обычной математической операцией сложения. Выглядит просто, но представьте, если у вас статистика, предположим, за 3 года? Придется обработать 36 чисел…

Второй способ – для более «продвинутых», но зато универсальный.

Записываем

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C16)+1;3)=0;C2:C16))

И затем нажимаем комбинацию клавиш CTRL+SHIFT+ENTER, поскольку используется формула массива. Excel сам добавит к фигурные скобки слева и справа.

Как это работает? Нам нужна 1-я, 3-я, 6-я и т.д. позиции. При помощи функции СТРОКА() мы вычисляем номер текущей позиции. И если остаток от деления на 3 будет равен нулю, то значение будет учтено в расчете. В противном случае – нет.

Для такого счетчика мы будем использовать номера строк. Но наше первое число находится во второй строке рабочего листа Эксель. Поскольку надо начинать с первой позиции и потом брать каждую третью, а начинается диапазон со 2-й строчки, то к порядковому номеру её добавляем 1. Тогда у нас счетчик начнет считать с цифры 3. Для этого и служит выражение СТРОКА(C2:C16)+1. Получим 2+1=3, остаток от деления на 3 равен нулю. Так мы возьмем 1-ю, 3-ю, 6-ю и т.д. позиции.

Формула массива означает, что Excel должен последовательно перебрать все ячейки диапазона – начиная с C2 до C16, и с каждой из них произвести описанные выше операции.

Когда будем находить продажи по Отделу 2, то изменим выражение:

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C16);3)=0;C2:C16))

Ничего не добавляем, поскольку первое подходящее значение как раз и находится в 3-й позиции.

Аналогично для Отдела 3

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C16)-1;3)=0;C2:C16))

Вместо добавления 1 теперь вычитаем 1, чтобы отсчет вновь начался с 3. Теперь брать будем каждую третью позицию, начиная с 4-й.

Ну и, конечно, не забываем нажимать CTRL+SHIFT+ENTER.

Примечание. Точно таким же образом можно суммировать и каждый N-й столбец в таблице. Только вместо функции СТРОКА() нужно будет использовать СТОЛБЕЦ().

Сумма каждых N строк.

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

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

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

1. Начальную точку. Обратите внимание, что С2 мы ввели как абсолютную ссылку.

2. Сколько шагов вниз сделать

3. Сколько шагов вправо сделать. После этого попадаем в начальную (левую верхнюю) точку массива.

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

5. Сколько колонок будет в массиве. Попадаем в конечную (правую нижнюю) точку массива значений.

Итак, формула для 1-й недели:

=СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1))

В данном случае СТРОКА() – это как бы наш счетчик недель. Отсчет нужно начинать с 0, чтобы действия начать прямо с ячейки C2, никуда вниз не перемещаясь. Для этого используем СТРОКА()-2. Поскольку сама формула находится в ячейке F2, получаем в результате 0. Началом отсчета будет С2, а конец его – на 5 значений ниже в той же колонке.

СУММ просто сложит предложенные ей пять значений.

Для 2-й недели в F3 формулу просто копируем. СТРОКА()-2 даст здесь результат 1, поэтому начало массива будет 1*5=5, то есть на 5 значений вниз в ячейке C7 и до С11. И так далее.

Как найти сумму наибольших (наименьших) значений.

Задача: Суммировать 3 максимальных или 3 минимальных значения.

Функция НАИБОЛЬШИЙ возвращает самое большое значение из перечня данных. Хитрость в том, что второй ее аргумент показывает, какое именно значение нужно вернуть: 1- самое большое, 2 – второе по величине и т.д. А если указать {1;2;3} – значит, нужны три самых больших. Но при этом не забывайте применять формулу массива и завершать комбинацией клавиш CTRL+SHIFT+ENTER.

=СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3}))

Аналогично обстоит дело и с самыми маленькими значениями:

=СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3}))

3-D сумма, или работаем с несколькими листами рабочей книги Excel.

Чтобы подсчитать цифры из одинаковой формы диапазона на нескольких листах, вы можете записывать координаты данных специальным синтаксисом, называемым «3d-ссылка».

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

Посмотрите на этом небольшом видео, как применяются 3-D формулы.

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

=СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8)

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

А вот 3D-метод:

=СУММ(неделя1:неделя4!B2:B8)

Мы указываем программе, что нужно произвести вычисления с ячейками B2:B8, находящимися на листах неделя1, неделя2, неделя3, неделя4. Здесь номер листа последовательно увеличивается на 1.

Важное замечание. Вы можете пользоваться 3D-ссылками и в других случаях. К примеру, выражение

=СРЗНАЧ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8)

найдет среднее значение однодневной выручки за месяц.

Поиск нужного столбца и расчет его суммы.

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

Для поиска и выбора конкретного товара используем весьма популярную комбинацию функций ИНДЕКС+ПОИСКПОЗ.

Расчет суммы в G3 выполним так:

=СУММ(ИНДЕКС(B2:D21;0;ПОИСКПОЗ(F3;B1:D1;0)))

Итак, комбинация ИНДЕКС+ПОИСКПОЗ должны возвратить для дальнейших расчетов набор чисел в виде вертикального массива, который и будет потом просуммирован.

Опишем это подробнее.

ПОИСКПОЗ находит в шапке наименований таблицы B1:D1 нужный продукт (бананы) и возвращает его порядковый номер (иначе говоря, 2).

Затем ИНДЕКС выбирает из массива значений B2:D21 соответствующий номер столбца (второй). Будет возвращен весь столбик данных с соответствующим номером, поскольку номер строки (первый параметр функции) указан равным 0. На нашем рисунке это будет С2:С21. Остается только подсчитать все значения в этой колонке.

В данном случае, чтобы избежать ошибок при записи названия товара, мы рекомендовали бы использовать выпадающий список в F3, а значения для наполнения его брать из B1:D1.

5 способов создать выпадающий список в Excel — рекомендуем ознакомиться.

Сумма столбцов из нескольких таблиц.

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

Для получения итогов сразу по нескольким таблицам также используем функцию СУММ и структурированные ссылки. Такие ссылки появляются при создании в Excel «умной» таблицы.

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

В нашем случае это выглядит так:

Прямая ссылка	Структурированная ссылка (Имя таблицы и столбца)
B2:B21	Таблица2[Сумма]

Для создания «умной» таблицы выделим диапазон A1:B21 и на ленте «Главная» выбираем «Форматировать как таблицу».

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

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

Помимо этого, если используемые вами таблицы содержат строчку итогов, то нашу формулу перепишем так:

=СУММ(Таблица2[#Итоги];Таблица3[#Итоги])

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

Примечание: итоговая строчка в таблице должна быть включена. Если вы отключите её, то выражение вернет ошибку #ССЫЛКА.

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

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

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

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

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

Также рекомендуем:

Источник

Группировка в сводных таблицах

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

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

Пример 1. Группировка чисел

Необходимо построить отчет, из которого было бы понятно — на каких именно товарах (дешевых или дорогих) мы делаем основной оборот. Для начала строим обычную сводную таблицу, поместив в область строк поле Цена, в область данных — Сумма заказа:

Теперь щелкаем правой кнопкой мыши по цене (область строк) и выбираем из контекстного меню команду Группировать (Group). В Excel 2000-2003 эта команда находится в разделе Группа и структура (Group and Show Detail) контекстного меню:

В итоге появится наш основной инструмент — диалоговое окно группировки данных в сводной таблице:

В окне можно задать начальное и конечное значение интересующего нас интервала и шаг изменения. Особое внимание хочу обратить на флажки слева от слов «начиная с» и «по» — эти флажки относятся к слову «Авто» и означают, что Excel будет сам выбирать интервал (дизайнеру окна напинать бы за такой user-friendly интерфейс сами знаете что). Для определенности введем начальное значение равное нулю, конечное 270 (самый дорогой наш товар стоит 263,5$) и зададим шаг 30, т.е. окно должно выглядеть так:

После нажатия на ОК Excel сгруппирует товары с ценами в заданном интервале и посчитает суммарную выручку по каждому интервалу:

Теперь ясно видно, что (в данном примере) наибольшую выручку нам дают товары с ценой до 60 долларов. «Хочешь быть богатым — работай для бедных!» как говорил Рональд Макдональд.

Пример 2. Группировка дат

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

Строим простую сводную, поместив Дату заказа в область строк, а Стоимость заказа в область данных:

Даты в сводной таблице получаются по умолчанию с тем же шагом, что и был в исходной, т.е. с шагом в один день. Щелкаем по любой дате правой кнопкой мыши и выбираем команду Группировать (Group). В появившемся окне группировки задаем следующие параметры:

После нажатия на ОК Excel сгруппирует даты с шагом месяцы и кварталы и мы получим вот такой красивый отчет:

Пример 3. Произвольная группировка

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

… и сгруппировать товары (предварительно перетащив их мышью в сводной таблице в соседние строки) в одну кучку:

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

Ссылки по теме

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

Источник

На чтение 5 мин. Просмотров 2.9k. Опубликовано 21.05.2021

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

Суммирование данных по месяцам/кварталам/годам с помощью сводной таблицы
Суммирование данных по дням недели/месяцам/годам с помощью Kutools for Excel

Вкладка Office Включить редактирование и просмотр с вкладками в Office, а также Сделайте вашу работу намного проще …

Подробнее … Бесплатная загрузка …

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%.

Повторное использование чего угодно: добавление наиболее часто используемых или сложных формул, диаграмм и все остальное в избранное и быстро используйте их в будущем.
Более 20 функций текста: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Подробнее … Бесплатная загрузка …

Содержание

Суммирование данных по месяцам /квартал/год со сводной таблицей
Суммирование данных по дням недели/ месяц/год с Kutools for Excel
Демо: суммирование данных по дням недели/месяцу/кварталу/году в Excel
Статьи по теме:

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!

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

Этот метод говорит о суммировании данных по месяцам, кварталам или годам с помощью сводной таблицы в Excel. См. Подробные инструкции следующим образом:

1. Выберите исходные данные, которые вы будете суммировать по месяцам/кварталам/годам, и нажмите Insert > PivotTable (или Insert > Сводная таблица > Сводная таблица ). См. Снимок экрана:

2. В открывшемся диалоговом окне «Создание сводной таблицы» укажите место, куда вы поместите созданную сводную таблицу, и нажмите кнопку OK . Смотрите скриншот выше:

3. Теперь справа от листа открывается панель «Поля сводной таблицы», перетащите поле «Дата» (поле, по которому будет производиться суммирование) в раздел Строки и перетащите поле «Сумма» (поле, которое будет суммироваться) в Значения . См. Снимок экрана ниже:

Примечание . Помимо перетаскивания, вы также можете щелкнуть правой кнопкой мыши поле “Дата” и выбрать Добавить в ярлыки строк из контекстного меню, а затем щелкнуть правой кнопкой мыши по количеству и выберите Добавить к значениям из контекстного меню. См. Снимок экрана выше:

4. Теперь перейдите к сводной таблице, щелкните правой кнопкой мыши любую дату в столбце Ярлыки строк и выберите Группа из контекстного меню. См. Снимок экрана ниже:

5. В открывшемся диалоговом окне «Группировка» щелкните, чтобы выделить критерий, по которому вы будете суммировать, в поле По , и нажмите кнопку ОК . В нашем случае мы нажимаем, чтобы выделить Месяцы . См. Снимок экрана выше:

До сих пор мы создали сводную таблицу и суммировали суммы по каждому месяцу, как показано на скриншоте ниже:

Суммирование данных по дням недели/ месяц/год с Kutools for Excel

Возможно, вы заметили, что сводная таблица не может суммировать данные по дням недели. Не волнуйся! Этот метод представит утилиты Kutools for Excel для простого суммирования данных по дням недели, месяцам или годам следующим образом:

Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная 30-дневная бесплатная пробная версия, кредитная карта не требуется! Получить сейчас

1. Выберите ячейки даты, по которым вы будете суммировать данные, и нажмите Kutools > Формат > Применить форматирование даты .

2. В открывшемся диалоговом окне «Применить форматирование даты» щелкните, чтобы выделить форматирование даты, при котором дата отображается только как месяц, день недели или год в зависимости от ваших потребностей, а затем нажмите кнопку ОК . См. Снимок экрана выше:

В нашем случае мы щелкаем, чтобы выделить формат даты «среда».

3. Продолжайте выбирать ячейки даты и нажмите Kutools > To Actual , чтобы преобразовать даты в текст названий дней недели.

4. Выберите диапазон данных, который вы будете суммировать по дням недели, месяцу или году, и нажмите Kutools > Content > Advanced Combine Rows .

5. В открывшемся диалоговом окне «Объединить строки на основе столбца» выполните следующие действия:
(1) Выберите столбец «Дата», по которому будет производиться суммирование, и нажмите кнопку Первичный ключ ;
(2) Выберите столбец «Сумма», который вы будете суммировать, и нажмите Рассчитать > Sum .
(3) Установить объединение rules для других столбцов и нажмите кнопку Ok .

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

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас

Демо: суммирование данных по дням недели/месяцу/кварталу/году в Excel

Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузить и бесплатную пробную версию !

Статьи по теме:

Вычислить среднее значение по году/месяцу/дате в Excel

Вычислить среднее значение за неделю в Excel

Среднее за день/месяц/квартал/час со сводной таблицей в Excel

Источник

Excel квартал

Номер квартала в Excel.

Дата конца и начала квартала в MS EXCEL

3 функции для определения номера квартала по дате в Excel

Определение квартала с использованием функции ВЫБОР в Excel

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Определение номеров нестандартных кварталов по дате

Формат даты в кварталах (Формулы/Formulas)

преобразовать дату в квартал (Формулы/Formulas)

Выбрать даты по кварталам (Формулы/Formulas)

Данные за квартал (Формулы/Formulas)

3 функции для определения номера квартала по дате в Excel

Определение квартала с использованием функции ВЫБОР в Excel

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Определение номеров нестандартных кварталов по дате

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Определение квартала с использованием функции ВЫБОР в Excel

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Определение номеров нестандартных кварталов по дате

Номер квартала в Excel.

Дата конца и начала квартала в MS EXCEL

3 функции для определения номера квартала по дате в Excel

Определение квартала с использованием функции ВЫБОР в Excel

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Определение номеров нестандартных кварталов по дате

Формат даты в кварталах (Формулы/Formulas)

преобразовать дату в квартал (Формулы/Formulas)

Выбрать даты по кварталам (Формулы/Formulas)

Данные за квартал (Формулы/Formulas)

Создаём сводную таблицу

Настраиваем сводную таблицу и получаем результат

Настраиваем фильтры сводной таблицы

Проводим дополнительные вычисления

Обновляем данные сводной таблицы

Как суммировать весь столбец либо строку.

Суммируем диапазон ячеек.

Как вычислить сумму каждой N-ой строки.

Сумма каждых N строк.

Как найти сумму наибольших (наименьших) значений.

3-D сумма, или работаем с несколькими листами рабочей книги Excel.

Поиск нужного столбца и расчет его суммы.

Сумма столбцов из нескольких таблиц.

Группировка в сводных таблицах

Пример 1. Группировка чисел

Пример 2. Группировка дат

Пример 3. Произвольная группировка

Ссылки по теме

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!

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

Суммирование данных по дням недели/ месяц/год с Kutools for Excel

Демо: суммирование данных по дням недели/месяцу/кварталу/году в Excel

Статьи по теме:

А вот еще интересные статьи: