Формулы Excel - подсчет значений в строках с заданным словом

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

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

Завод20

Завод40

Завод80

Итого заводы

Фабрика111

Фабрика222

Фабрика333

Фабрика777

Итого фабрики

Комбинат10001

Комбинат20002

Комбинат30003

Комбинат40004

Комбинат50005

Комбинат70007

Итого комбинаты



ИТОГО ВСЕ: /необходимая нам формула/

Обычно, это делают, выбирая каждую ячейку напротив промежуточного "итого" при вводе формулы СУММ или через "плюс"…

Однако, есть способ и проще и удобнее.

Допустим, названия наших заводов, фабрик, комбинатов находятся в столбце A в интервале A2:A17, а в столбцах, начиная со столбца B и далее, находятся данные по годам.

Чтобы посчитать сумму значений всех ячеек столбца B напротив которых имеется ячейка (из столбца A) содержащая слово "итого" следует воспользоваться формулой:

=СУММЕСЛИ(A2:A17;"*итог*";B2:B17)

Однако, эта формул еще "не совершенна"…

Ведь нам необходимо посчитать итоги не только для первого года (столбец B), но и для других. Если же мы попробуем "растянуть" получившуюся формулу хотя бы на 1 ячейку вправо, мы получим =СУММЕСЛИ(B2:B17;"*итог*";C2:C17), что, конечно же, нас совсем не устроит.

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

=СУММЕСЛИ($A$2:$A$17;"*итог*";B2:B17)

Теперь эту формулу можно спокойно "растягивать" на необходимое количество столбцов!

Сочетания клавиш ("быстрые клавиши") Excel

Комбинация клавиш Excel – изменение абсолютной/относительной адресации.

В примере с формулой приведенной выше, мы упомянули т.н. "абсолютную адресацию". Для того, чтобы изменить адрес ячейки (или диапазона ячеек) в формуле с относительного на абсолютный следует использовать клавишу F4. Тем, кто не знаком с понятием абсолютных и относительных ссылок рекомендуем раздел справки Excel "Ссылки на ячейки и диапазоны ячеек".

Совет по использованию Excel

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

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

='мой лист'!A1

Как видите, название листа взято в одинарные кавычки. То же самое (одинарные кавычки) будут присутствовать, если Вы напишите название листа через дефис.

Однако, если Вы, к примеру, попробуете сделать аналогичную формулу для любого другого листа Вашей новой книги (например, для листа "Лист3"), то она будет иметь немного другой вид:

=Лист3!B2

Отличие, как говорится, очевидно: в этом случае отсутствуют одиночные кавычки.

Собственно совет: в названиях листов старайтесь не пользоваться пробелами и дефисами (возможно, что есть ряд других символов, которые дают "эффект одинарных кавычек"). Если необходимо, чтобы имя файла состояло из 2-х и более слов, то в качестве разделителя лучше использовать нижнее подчеркивание "_".

Пример имени листа в котором слова написаны через нижнее подчеркивание: "мой_план_март".

Почему это важно? Дело в том, что иногда формулы со ссылками на другие листы мы вводим вручную. Как раз, когда это происходит, то намного удобнее напечатать =Лист1!A1, чем ='мой лист'!A1 (не приходиться тратить время на ввод кавычек). Также есть и другие "плюсы" в том, когда имя листа написано без пробелов и дефисов.

Полезные ссылки Excel (полезный файл Excel!)

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

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

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


Скачать файл Нумерология1_2

Адрес excel-ok@yandex.ru ждет твой экселевский файл. Мы поможем тебе разобраться.