Формула СУММЕСЛИ суммирует данные в соответствии с критериями. Формула OFFSET позволяет нам изменить ссылку. Комбинация двух формул и добавление поля со списком позволяет легко суммировать данные в соответствии с критериями из таблицы данных.

Пример

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

Шаг 1. Задайте имя для столбца B на листе.

Выберите столбец B, нажмите Ctrl + F3, введите ColB в поле «Имена * на листе» и нажмите кнопку «ОК».

Шаг 2: Формула СУММЕСЛИ

Формула СУММЕСЛИ * суммирует данные в соответствии с критериями.

  • Формула имеет 3 аргумента:

Первый аргумент — Диапазон — ColB * (столбец B в таблице данных).

Второй аргумент — Критерии * — b3 (текст: Доход).

Третий аргумент — Sum_range * — D: D (столбец D, январь 2001 г.). Это диапазон данных, из которого будут суммироваться данные по критериям.

Проблема

Формула СУММЕСЛИ отлично подходит для обобщения данных по критериям. Однако есть ограничение: вы не можете вносить какие-либо изменения в итоговый диапазон при использовании формулы СУММЕСЛИ *. В приведенном выше примере вы суммировали данные из столбца за январь 2001 года. Как же тогда можно легко изменить диапазон суммы для суммирования из колонки марта или апреля (вместо января)?

В формуле VLookup * есть третий аргумент: изменение номера столбца также меняет номер пересекаемого столбца.

Формула Index * намного более гибкая; вы можете изменить как номер строки, так и номер столбца.

Формуле СУММЕСЛИ требуется помощь, и решение состоит в том, чтобы добавить формулу СМЕЩЕНИЕ *.

Шаг 3: Формула СМЕЩЕНИЯ

СМЕЩЕНИЕ

Шаг 4: Объединение формул СМЕЩЕНИЕ и СУММЕСЛИ

СМЕЩЕНИЕ

Поле со списком

Шаг 5: Поле со списком (подробнее о том, как добавить поле со списком) Добавьте поле со списком.

Имя ячейки, связанной с полем со списком, — MonthNumber. На снимке экрана вы увидите третий аргумент формулы СУММЕСЛИ в строке формул. Здесь отображается имя ячейки, связанной с полем со списком.

Комбинация СУММЕСЛИ, СМЕЩЕНИЕ и поля со списком обеспечивает невероятно мощный инструмент для запроса и суммирования данных из отчета. Снимок экрана // Суммируйте данные в соответствии с критериями из таблицы данных с помощью формулы Sumif, формулы смещения и поля со списком в Microsoft Excel //images/screenshots/299-1.JPG [image: //images/screenshots/299-1_s.JPG [ Обобщите данные в соответствии с критериями из таблицы данных, используя формулу Sumif, формулу смещения и поле со списком в Microsoft Excel]] изображение: https: //www.office-skill.site/images/f [image, width = 20, height = 1 ] //images/screenshots/299-2.JPG [image: //images/screenshots/299-2_s.JPG [Суммируйте данные в соответствии с критериями из таблицы данных, используя формулу Sumif, формулу смещения и поле со списком в Microsoft Excel]] изображение: https: //www.office-skill.site/images/f [image, width = 20, height = 1] //images/screenshots/299-3.JPG [image: // images / screenshots / 299- 3_s.JPG [Суммируйте данные в соответствии с критериями из таблицы данных с использованием формулы Sumif, формулы смещения и поля со списком в Microsoft Excel]] изображение: https: //www.office-skill.site/images/f [image, width = 20 , высота = 1]