Архив рубрики: Excel

Поиск значений суммы с помощью СУММЕСЛИ и именованными диапазонами DEVSAP

=Функция СУММПРОИЗВ(СУММЕСЛИ(диапазон критерия,критерий,диапазон скмирования))

Для суммирования значений, полученных с помощью операции подстановки можно использовать функцию СУММПРОИЗВ с функцией СУММЕСЛИ.

В показанном примере, формула в J6-это:

=Функция СУММПРОИЗВ(СУММЕСЛИ(Код,C5:I5,Значение))

=СУММПРОИЗВ(СУММЕСЛИ(Код;C6:I6;Значение))

Где коды-это именованный диапазон «Код» L5:L6 и «Значение» именованный диапазон M5:M6.

Контексте

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

Как эта формула работает

Ядром этой формулы СУММЕСЛИ, которая используется для поиска правильных значений для Н и Д. используя СУММЕСЛИ для поиска значений-это более сложный метод, который хорошо работает, если значения являются числовыми, и никаких дубликатов в «таблицы подстановки».

Хитрость в данном случае в том, что критерии СУММЕСЛИ не одно значение, а массив значений в диапазон C5:I5 с:

=Функция СУММПРОИЗВ(СУММЕСЛИ(Код,C5:I5,Значение))

Потому что мы даем СУММЕСЛИ более чем одному критерию, СУММЕСЛИ будет возвращать более одного результата. В показанном примере результат СУММЕСЛИ есть следующий массив:

{1,0,0,0.5,0,0.5,0}

Обратите внимание, что мы правильно получите 1 Для каждого «Д» И 0,5 за каждую «Н»., и пустые значения в неделю генерировать ноль.

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

Файл Excel Поиск значений суммы с помощью СУММЕСЛИ и именованными диапазонами devsap

СЧЕТЕСЛИ поиск в диапазоне конкретных значений и совпадений + ЕСЛИ + «*»&

=СЧЕТЕСЛИ(диапазон; значение)>0

Связанные формулы

Диапазон содержит определенный текст
Спектр содержит одну из многих значений
Спектр содержит один из множества подстрок
Ячейка содержит определенный текст
Ячейка содержит одну из многих вещей

Если Вам необходимо определить, существует ли значение в диапазон ячеек, вы можете использовать простую формулу на основе функции СЧЕТЕСЛИ.

В показанном примере, формула в Д5:

=СЧЕТЕСЛИ(гсч,B5)>0

Функция СЧЕТЕСЛИ подсчитывает Количество ячеек, которые удовлетворяют заданным условиям возвращает число найденных вхождений. Если нет клетки, отвечающие критериям, СЧЕТЕСЛИ возвращает ноль. Любое положительное число означает значение было найдено. Это означает, что вы можете использовать оператор «больше» ( > ):

=СЧЕТЕСЛИ(диапазон; значения)>0

Вы также можете обернуть формулы внутри оператора ЕСЛИ, чтобы получить конкретный результат. Например, чтобы получить «да» или «нет», используйте:

=Если(СЧЕТЕСЛИ(диапазон,значение)>0,»Да»,»Нет»)

Это работает, поскольку если считает любое число > 0 как истина, когда она появляется как логический аргумент теста.

Частичное совпадение

Если вы хотите проверить диапазон, чтобы увидеть, если он содержит определенный текст (подстрока или частичный текст), вы можете добавить подстановочные знаки в Формуле. Например, если у вас есть значение поискать в ячейке B5, а вы хотите, чтобы проверить диапазон $F$5:$F$11, вы можете настроить СЧЕТЕСЛИ искать это значение в любом месте в клетке, объединяя звездочки:

=ЕСЛИ(СЧЁТЕСЛИ($F$5:$F$11;»*»&B5&»*»);»ок»;»ПРОВЕРИТЬ»)

Звездочка (*) является подстановочным знаком для одного или нескольких персонажей. Объединяя звездочки перед и после значения в B5 формула будет подсчитывать подстроки в B5 везде, где он появляется в каждой ячейке диапазона.

Файл СЧЕТЕСЛИ поиск в диапазоне конкретных значений и совпадений + ЕСЛИ + «*»& xlsx

Построить гиперссылку с ВПР

=Гиперссылка(ВПР(название,Таблица,столбец 0),название)

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

В показанном примере, формула в F5:

=Гиперссылка(ВПР(E5,диапазон для поиска,2,0),E5)

Как эта формула работает

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

Работая изнутри, ВПР ищет и возвращает значение по ссылке из столбца 2 именованного диапазона «таблица ссылок» (В5:В8). Искомое значение исходит из столбца E, и ВПР настроен на Точное «0» совпадение.

Результат поступает в гиперссылку как ссылка, и текст в столбце e используется для наименование ссылки.

Гиперссылка возвращает рабочую ссылку.

Файл Построить гиперссылку с ВПР xlsx

Общее число столбцов и строк в диапазоне Excel

=ЧИСЛСТОЛБ(диапазон) Колонка

=ЧСТРОК(диапазон) Строка

Связанные формулы

Всего строк в диапазоне
Общее количество ячеек в диапазоне

Если вам нужно подсчитать количество столбцов в диапазоне, используйте функцию ЧИСЛСТОЛБ. В общем виде формулу ЧИСЛСТОЛБ представляет собой диапазон ячеек.

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

=ЧИСЛСТОЛБ(D5:E12)

Вот как эта формула работает:

ЧИСЛСТОЛБ подсчитывает количество столбцов и возвращает результат в виде числа.

Файл Общее число столбцов и строк в диапазоне xls

Транспонировать таблицу без нулей Excel

=ТРАНСП(ЕСЛИ(диапазон=»»;»»;диапазон))

Для динамического транспонирования таблицы, которая содержит пробелы, вы можете использовать формулу массива, которая использует ТРАНСП и функцию ЕСЛИ.

В примере показано, формула массива в F4 является:

=ТРАНСП(ЕСЛИ(C4:D11=»»;»»;C4:D11))

Обратите внимание: это формула массива, который должен быть введен с помощью Control + Shift + ввод через весь диапазон F4:M5.

Как эта формула работает

ТРАНСП функция автоматически переносит значения в горизонтальной ориентации на вертикальную ориентацию и наоборот.

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

Да, еще раз, перед тем как воспользоваться «Control + Shift + ввод» перейдите в ячейку и помощью Shift выделите диапазон от первой ячейки вниз или в право, после этого нажимайте Control + Shift + ввод.

Файл Транспонировать таблицу без нулей xls