MS, Libreoffice & Google docs

Формула:

=СУММЕСЛИМН(диапазон_суммирования (ЧТО СУММИРУЕМ); диапазон_условия1 (ГДЕ ИЩЕМ УСЛОВИЕ 1); условие1 (ЧТО ИЩЕМ В ДИАПАЗОНЕ 1) ; [диапазон_условия2; условие2]; …)

Аргументы функции:

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

Аргумент_2 Диапазон_условия1 (обязательный аргумент) диапазон, в котором проверяется Условие1.

Аргумент_3 Условие1 (обязательный аргумент), определяет, какие ячейки суммируются в аргументе

Условия могут вводится в виде: 52, ">45", A2, "текст" или "22".

Диапазон_условия1 и Условие1 составляют пару, определяющую, к какому диапазону применяется условие при поиске. Соответствующие значения найденные в этом диапазоне ячеек суммируются в пределах аргумента Диапазон_суммирования.

Аргумент_n Диапазон_условия2, Условие2, … (необязательный аргумент) дополнительные диапазоны и условия для них.

Можно ввести до 127 пар диапазонов и условий.

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

Функцию можно вызвать, через:

Мастер формул, раздел Математические;

Вкладку Формулы ► Математические;

Введя в строку =СУММЕСЛИМН(.

Еще интересное по теме Excel:

Трюки с листами книги

ВПР (вертикальный просмотр)

Как перевернуть таблицу в Excel

Мгновенное заполнение

"Умные" таблицы в Excel

Как в Excel из кросс-таблицы сделать плоскую

Получить плоскую:

Выполнить такую магию без COPY/PASTE можно при помощи Макроса или надстройки Power Query.

Видео на тему ⬇⬇⬇

Макрос

1. Откройте редактор VBA: ALT+F11;

2. Создайте новый модуль Insert ► Module:

3. Вставьте макрос:

Sub ПреобразованиеТаблиц()
Dim OutputRng As Range
Dim InputRng As Range
Dim out_row As Long, out_col As Long
Dim in_col As Long, in_row As Long
Set InputRng = ActiveCell.CurrentRegion
Set OutputRng = Application.InputBox(prompt:="Выберите ячейку для вывода новой таблицы", Type:=8)
OutputRng.Range("A1:C1") = Array("Столбец1", "Столбец2", "Столбец3")
out_row = 2
out_col = 2
For in_row = 2 To (InputRng.Rows.Count - 1) * (InputRng.Columns.Count - 1) + 1
For in_col = 1 To 3
If in_col = 1 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, 1)
If in_col = 2 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(1, out_col)
If in_col = 3 Then OutputRng.Cells(in_row, in_col) = InputRng.Cells(out_row, out_col)
Next in_col
out_col = out_col + 1
If out_col = InputRng.Columns.Count + 1 Then
out_col = 2
out_row = out_row + 1
End If
Next in_row
End Sub

4. Сохраните книгу с поддержкой макросов;

5. Создайте кнопку для вызова макроса Разработчик ► Вставить:

6. Назначьте на кнопку макрос:

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

Готово.

Power Query

1. Загрузите таблицу в редактор, вкладка Данные ► Получить данные ► Из других источников ►Из таблицы/диапазона;

2. Выделите первый столбец, нажмите пкм на шапке столбца ► Отменить свертывание других столбцов:

В редакторе можно переименовать заголовки столбцов и изменить формат данных при необходимости.

3. Загрузите запрос, окно редактора запросов, Главная ►Закрыть и загрузить ► Закрыть и загрузить в... :

4. Выберите вариант Таблица, указав расположение для выгрузки:

5. Готово.

Полезно? Пишите в комментах, следующую тему.

Формула:

=СУММЕСЛИ(Диапазон ячеек (ГДЕ ИЩЕМ); Условие (ЧТО ИЩЕМ); [диапазон_суммирования] (ЧТО СУММИРУЕМ))

Аргументы функции:

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

Аргумент_2 Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Подстановочные знаки можно включать в вопросительный знак (?), чтобы они соответствовали любому символу, звездочку (*) в соответствии с любой последовательностью знаков. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (~).

Условие может быть выражено в виде 42, ">66 000", B5, "7?", "Iphone *", "* ~?" или "сегодня" ().

ВАЖНО: текстовые условия, условия с логическими и математическими знаками необходимо заключать в двойные кавычки ("текст").

Аргумент_3 Диапазон_суммирования (не обязательный) ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона.

Если аргумент опущен, Excel суммирует ячейки, указанные в Аргументе_1 (те же ячейки, к которым применяется условие).

Диапазон Аргумента_3 должен быть одного размера , что и Аргумента_1.

Функцию ВПР можно вызвать, через:

Мастер формул, раздел Математические;

Вкладку Формулы ► Математические;

Введя в строку =СУММЕСЛИ(.

Еще интересное по теме Excel:

Трюки с листами книги

ВПР (вертикальный просмотр)

Как перевернуть таблицу в Excel

Курсы валют в Google Таблице (GoogleFinance)

Мгновенное заполнение

"Умные" таблицы в Excel

Как отобразить листы в файлах Excel, выгруженных из 1С

Итак, функция ВПР (вертикальный просмотр) предназначена для поиска элемента(-ов) в таблице или диапазоне по строкам.

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

Формула:

Аргументы функции:

Аргумент_1 Искомое_значение, значение, которое вам нужно найти;

Аргумент_2 Таблица, диапазон с искомым значением, в котором находится искомое значение;

Аргумент_3 Номер_столбца, содержащий возвращаемое значение, считается от искомого значения, влево;

Аргумент_4 Интервальный просмотр (не обязательный): 1/ИСТИНА приблизительное совпадение, 0/ЛОЖЬ, точное совпадение возвращаемого значения. По умолчанию всегда 1/ИСТИНА.

Функцию ВПР можно вызвать, через:

Мастер формул, раздел Ссылки и массивы;

Вкладку Формулы ► Ссылки и массивы; 

Введя в строку =ВПР(.

Пример

Необходимо найти цену продукта по наименованию:

Введём формулы:

=ВПР(H4; $B$4:$E$10;4;0) - точный поиск

=ВПР(H4;$B$4:$E$10;4;ИСТИНА) - Приблизительный

ВНИМАНИЕ: искомый столбец Цена четвертый, а не пятый, т.к. отсчёт идет от самого левого столбца с Искомым_значением.

Сортируем исходную таблицу по алфавиту:

Готово. Подробнее про ограничения читайте ниже.

Именованные диапазоны

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

Создать именованный диапазон, можно на вкладке Формулы ► Задать имя:

В открывшемся окне, задайте имя диапазона и укажите сам диапазон:

Посмотреть все созданные диапазоны или отредактировать их можно на вкладке Формулы ► Диспетчер имен:

Используя созданный диапазон напишем формулу =ВПР(G3;Таблица;4;0)

Можно заметить формула стала меньше и теперь диапазон таблицы можно не закреплять при протягивании формулы.

Ограничения

Не может искать влево

ВПР может искать значения только в крайнем левом столбце. В случае неверной ссылки формула выдаст ошибку #Н/Д.

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

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

Вам нужны повторяющиеся данные в списке? Если нет – удалите их, при помощи кнопки Удалить дубликаты на вкладке Данные.

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

ВПР не чувствительный к регистру

Добавили или удалили столбец из таблицы, все сломалось

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

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

Ссылки на ячейки съехали при копировании или протягивании формулы

Используйте абсолютные ссылки на ячейки при записи диапазона, например $A$1:$D$100 или $A:$D. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

Ошибки

1. Включен точный поиск (0/ЛОЖЬ), но искомого значения (Аргумент_1) нет в диапазоне поиска или он написан с опечаткой;

2. Включен приблизительный поиск (1/ИСТИНА), но таблица, в которой происходит поиск не отсортирована по возрастанию наименований;Столбец поиска не является крайним левым;

3. Съехал Диапазон с искомым значением (Аргумент_2), ссылка на который должна быть абсолютной, нажмите внутри формулы на нём F4;

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

Используйте текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(Аргумент_1));Аргумент_2;Аргумент_3;Аргумент_4).

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

Подробнее статье ВПР и числа-как-текст.

Для преобразования числовых форматов данных в текст, используйте функцию ТЕКСТ : =ВПР(ТЕКСТ(Аргумент_1; " # ");Аргумент_2;Аргумент_3;Аргумент_4) или Аргумент_1&"".
Обратный вариант преобразования текста в число: --Аргумент_1, Аргумент_1*1 или Аргумент_1+0.
Универсальная формула для исправления ошибки числа, как текст: =ЕСЛИОШИБКА(ВПР(Аргумент_1*1; Аргумент_2;Аргумент_3;Аргумент_4;ВПР(Аргумент_1&""; Аргумент_2;Аргумент_3;Аргумент_4).

Убрать сообщения об ошибке #Н/Д (для версии Excel 2007+)

В случаях, когда функция ВПР не может найти совпадения, используйте функцию ЕСЛИОШИБКА.

Например, заменяет нулями: =ЕСЛИОШИБКА(ВПР(Аргумент_1;Аргумент_2;Аргумент_3;Аргумент_4);0).

Выводит пустое значение:

=ЕСЛИОШИБКА(ВПР( Аргумент_1;Аргумент_2;Аргумент_3;Аргумент_4 );"").

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

Еще интересное по теме Excel:

Трюки с листами книги

Мгновенное заполнение

Быстро удалить все картинки с листа

Быстрое перемещение строк и столбцов

Сводные таблицы в Excel: как создать?

"Умные" таблицы в Excel

Как отобразить листы в файлах Excel, выгруженных из 1С

Шаги:

Перейдите на вкладку Разметка страницы ► Печатать заголовки:

В открывшемся окне, на вкладке Лист ► Печатать заголовки ► сквозные строки (для печати столбцов, сквозные столбцы):

Добавьте ссылку на диапазон с заголовками (или столбцами):

Готово.