Таблицы. Помощь с Excel и Google (помощь с Эксель и Гугл)

Сегодня покажу неочевидный способ использования функции ИНДЕКС

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

Это можно сделать с помощью функций СУММ и ИНДЕКС.

❗ При этом, в функции ИНДЕКС определяется только номер столбца по названию месяца, а в качестве аргумента "номер_строки" следует указать 0.

=СУММ(ИНДЕКС(C3:N7;0;ПОИСКПОЗ(P2;C2:N2;0)))

✅ Excel внутри себя определит заданный столбец и просуммирует все значения из него в указанном диапазоне.

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

#полезности
#моихрукдело

О канале | Донат каналу

⚡ И вот теперь здесь мы можем заменить жёстко заданные значения 3 и 5 на функции ПОИСКПОЗ

=ИНДЕКС(C28:G32;ПОИСКПОЗ(E36;B28:B32;0);ПОИСКПОЗ(E34;C27:G27;0))

❗ При изменении искомых значений будут найдены новые позиции строки и столбца и вернётся соответствующее значение из диапазона поиска.

#полезности
#моихрукдело

О канале | Донат каналу

Функция ПОИСКПОЗ

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

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

Простыми словами:

❗ Всё крутится относительно массива с данными.

Если рассматриваем массив C28:G32, в котором ищем, то поиск позиции значения С по строкам следует искать в диапазоне B28:B32

=ПОИСКПОЗ(D36;B28:B32;0)

Аналогичная ситуация и со столбцами - для диапазона со значениями C28:G32 диапазон поиска позиции столбца со значением 5 будет C27:G27

=ПОИСКПОЗ(D34;C27:G27;0)

❗❗❗ Еще раз обращаю внимание на важный нюанс - просматриваемый массив должен быть одной размерности по строкам или столбцам с массивом значений.

Третий аргумент чаще всего 0 (ЛОЖЬ). Про него забывать не стоит, иначе работать будет неправильно.

#полезности
#моихрукдело

О канале | Донат каналу

Функция ИНДЕКС

❗ Она возвращает значение из диапазона.

При вводе функции, подсказка Excel может запутать, т.к. существует два варианта её использования - с массивом и с областями. В 99% реальных задач - это работа с массивом, поэтому синтаксис следующий:

=ИНДЕКС(массив; номер_строки; [номер_столбца])

😉 На примере тестового задания я сравнил бы работу функции с "морским боем", игрой, в которую мы, когда-то, все играли на листочках:

Массив - это поле боя. В нём нужно указать номер строки и номер столбца. На их пересечении будет значение, которое и вернет формула.

=ИНДЕКС(C28:G32;3;5)

❗ Для диападона C28:G32 заданная буква С располагается в строке 3, а заданный номер столбца 5 - в столбце G листа Excel. Но рассматривается выбранный диапазон, а не лист, поэтому искомые координаты, куда нужно "бить" - это 3 и 5. Тогда "корабль" будет убит)

Если массив одномерный, состоящий из одного столбца, то аргумент "[номер_столбца]" можно указать как 1 или не указывать вовсе, т.к. он, в данном случае, будет необязательным.

💡 Эти позиции строки 3 и столбца 5 можно и нужно автоматизировать, чтобы при изменении искомых значений функция определяла новые координаты. Но про ПОИСКПОЗ в следующий раз.

#полезности
#моихрукдело

О канале | Донат каналу

Сегодня несложный тест.

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

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

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

#домашнеезадание

О канале | Донат каналу

🫣 Представьте: утро, вы включаете компьютер, и там снова они - те самые действия, которые делаете каждый день. Потом ещё обработка данных, потом снова та же самая сложность… И так по кругу.

Знакомая картина? Такая рутина не просто утомляет, она забирает самое ценное, что у нас есть - время!

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

✍️ Напишите в личные сообщения или в комментарии - обсудим, где можно упростить работу.

О канале | Донат каналу

Всё о ВПР в одном месте:

✔️ Размышления здесь
✔️ Поиск по точному совпадению здесь
✔️ Поиск по приблизительному совпадению здесь
✔️ Обход ограничения функции здесь
✔️ Пример использования функции здесь
✔️ Ещё один пример здесь
✔️ Об удобствах и подсказках здесь
✔️ О возможных ошибках здесь
✔️ Обработка ошибок здесь

О канале | Донат каналу

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

❗ Всё оказалось объяснимым - в границах указанного диапазона был скрыт один столбец, о котором Игорь не знал и не заметил. При этом, он визуально считал столбцы и указывал номер нужного. Но Excel считает по-другому - он учитывает и те, которые скрыты.

⚡ Чтобы не испытывать подобных неудобств в момент выделения диапазона при написании формулы, Excel подсказывает нам в специальной плашке, сколько строк и столбцов выделено (реконструировал события на скрине).

✔️ Особенно актуально на больших таблицах!

В данном случае это:

5R x 4C

Фактически выделено 5 строк и 4 столбца.

Но, т.к. столбец D скрыт, визуально кажется, что столбцов три.

✅ Теперь не только Игорь знает, как быстро узнать номер столбца выделенного диапазона, не считая их глазами 😉

💡 Пользуйтесь подсказками Excel

#полезности
#моихрукдело

О канале | Донат каналу

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

⚡ Для таких случаев существуют другие приёмы - ИНДЕКС+ПОИСКПОЗ, новая функция ПРОСМОТРХ и т.д. Но если нужно именно ВПР, то решение есть! Это сочетание функции ВПР с функцией ВЫБОР.

{=ВПР(G3;ВЫБОР({1;2};D:D;B:B);2;0)}

❗ Это уже формула массива, поэтому вводить её нужно клавишами CTRL SHIFT ENTER

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

На втором скрине я вынес функцию ВЫБОР и показал, как она работает - формирует правильную для нас таблицу, меняя расположение столбцов с исходными данными B и D на D и B.

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

💫 Магия Excel в чистом виде!

#полезности
#моихрукдело

О канале | Донат каналу