После многих лет работы со сложными и запутанными электронными таблицами я открыл для себя четыре функции Excel, которые экономят мне часы работы каждую неделю, автоматизируя рутинные задачи, которые большинство людей выполняют вручную. Эти функции незаменимы для всех, кто регулярно работает с данными, будь то профессиональный аналитик данных или просто рядовой пользователь, стремящийся упростить свою работу.
Быстрые ссылки
4. XLOOKUP: расширенный поиск в электронных таблицах
XLOOKUP Это расширенная функция поиска в программах для работы с электронными таблицами, таких как Microsoft Excel и Google Sheets, которая выходит за рамки возможностей традиционных функций поиска, таких как ВПР و ГПР. Товер XLOOKUP Повышенная гибкость, более эффективная обработка данных и сокращение числа распространенных ошибок, связанных с устаревшими функциями. XLOOKUP Незаменимый инструмент для финансовых аналитиков, специалистов по анализу данных и всех, кто работает с большими объемами данных и нуждается в быстром и точном извлечении конкретной информации. XLOOKUPВы можете выполнить поиск значения в определённом диапазоне и вернуть соответствующее значение из другого диапазона, независимо от расположения столбцов или строк. Также поддерживается XLOOKUP Поиск осуществляется справа налево и снизу вверх, что делает его более универсальным по сравнению с другими функциями.
Прощай VLOOKUP: XLOOKUP — идеальное решение
Я перестал использовать функцию ВПР много лет назад, когда открыл для себя функцию XПРОСМОТР. В то время как функция ВПР ищет только вправо и вылетает при перемещении столбцов, XПРОСМОТР работает в любом направлении и остаётся гибкой. XПРОСМОТР — одна из Функции Excel, которые могут сэкономить ваше время Найдите нужные данные в своих электронных таблицах.
В данных о ценах на компьютерные компоненты мне нужно найти конкретные цены на видеокарты в зависимости от модели продукта. С функцией ВПР мне пришлось бы реструктурировать всю таблицу. Но с функцией ПРОСМОТР X достаточно ввести:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP просматривает весь столбец «Товар», находит мою видеокарту и возвращает соответствующую цену. Расположение столбца «Цена» не имеет значения, и приложение не даст сбоя, если я добавлю ещё столбцы. Я постоянно использую эту функцию для ссылки на информацию о товарах на разных листах без необходимости переформатировать что-либо.
Основная формула для XLOOKUP:
=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив)
- искомое_значение: Значение, которое вы хотите найти.
- искомый_массив: Место, где вы ищете ценность.
- возвращаемый_массив: Столбец или строка, содержащие значение, которое вы хотите вернуть.
Итак, в моём случае нужно было найти значение «GIGABYTE GeForce RTX 3060 12GB Gaming OC». Мне нужно было найти это значение в столбце C:C и вернуть соответствующее значение из столбца D:D в той же строке, где было найдено совпадение.
Ещё одна вещь, которая мне нравится в XLOOKUP, — это то, что если добавить «,-1» в конец формулы, поиск будет выполняться снизу вверх, что позволяет автоматически найти самую последнюю запись о цене. Это избавляет меня от необходимости вручную сортировать данные при каждом обновлении таблиц.
3. Использование моих функций SUMIFS و COUNTIFS В электронных таблицах
Профессиональная работа с несколькими стандартами
Базовых функций СУММ и СЧЁТ достаточно для простых задач, но для реального анализа они неэффективны. Когда мне нужно проанализировать данные о ценах при различных условиях, я обычно использую функции СУММЕСЛИМН и СЧЁТЕСЛИМН. Они позволяют легко сегментировать сотни строк.
Допустим, я хочу подсчитать количество процессоров AMD, доступных на Amazon US. Вместо ручной фильтрации я ввожу:
=COUNTIFS(F:F, "Amazon US", K:K, "AMD")
Это сразу показывает, что в моём наборе данных на Amazon представлено 14 процессоров AMD. Преимущество в том, что я могу собрать столько бенчмарков, сколько мне нужно.
Для анализа цен функция СУММЕСЛИМН работает аналогичным образом. Чтобы рассчитать общую стоимость всех процессоров Intel, имеющихся в наличии, я использую:
=SUMIFS(D:D, K:K, "Intel", G:G, "В наличии")
Это добавит все цены в столбце D, где бренд — «Intel», а статус запаса — «В наличии».
Синтаксис функции СУММЕСЛИМН:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)
- сумма_диапазон: Столбец, который нужно просуммировать.
- criteria_range1: Первый столбец, по которому проверяются условия.
- критерии1: Состояние первого диапазона.
- диапазон_критериев2, критерии2: Дополнительные положения и условия (необязательно).
Функция СЧЁТЕСЛИМН работает аналогично, за исключением того, что она подсчитывает совпадающие строки вместо суммирования значений:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)
Я предпочитаю использовать функции СУММЕСЛИМН и СЧЁТЕСЛИМН для быстрых отчётов, потому что они мгновенно обновляют новые данные, хорошо вписываются в мои существующие формулы и позволяют мне поддерживать всё в порядке, не создавая отдельную сводную таблицу. Эти инструменты обеспечивают точный и эффективный анализ данных, экономя время и силы при разработке сложных отчётов. Использование таких функций, как СУММЕСЛИМН и СЧЁТЕСЛИМН, — важный навык для каждого аналитика данных, стремящегося быстро и легко извлекать ценную информацию из данных.
2. Подрезка и чистка: основные шаги для поддержания внешнего вида
Прощай, беспорядок в данных
Ничто не портит таблицу быстрее, чем неструктурированные данные, заполненные лишними пробелами и скрытыми символами. Я усвоил это на собственном горьком опыте, когда мои поиски постоянно терпели неудачу из-за лишних пробелов в конце названий форм.
Функция TRIM удаляет лишние пробелы в начале и конце текста, а также между словами. При импорте данных из разных источников названия товаров часто содержат несоответствующие пробелы. Вместо того, чтобы вручную очищать каждую ячейку, я создаю вспомогательный столбец и использую:
=ОБРЕЗАТЬ(C2)
Затем я перемещаю указатель мыши к краю ячейки, пока он не превратится в знак плюс (+), а затем перетаскиваю его вниз по всем строкам, к которым я хочу применить функцию TRIM.
1. TEXTBORE и TEXTAFTER: подробное объяснение и их важность
Точно извлекайте необходимые данные
Функции ТЕКСТПЕРЕД и ТЕКСТПОСЛЕ — одни из моих любимых функций Excel для наведения порядка в таблицах. Современные текстовые функции Excel превосходно извлекают конкретную информацию из неструктурированных текстовых строк. Например, в моём столбце с ценами были перемешаны такие записи, как «$177.52», «178.33 USD», «₱9055» и «9645.50 PHP».
Функция TEXTBORE извлекает все, что находится перед указанным разделителем:
=TEXTBEFORE(D2, "USD")
Таким образом, функция мгновенно извлекла «178.33» из «178.33 USD».
Функция ТЕКСТАФТЕР работает в обратном порядке, извлекая все, что находится после разделителя:
=TEXTAFTER(C2, "AMD ")
Таким образом я извлек функцию «Процессор Ryzen 5 5700X 8-Core AM4» из «Процессор AMD Ryzen 5 5700X 8-Core AM4».
Для сложных вычислений я объединяю обе функции. Чтобы получить числовое значение цены 177.52 доллара США:
=TEXTBEFORE(TEXTAFTER(D8, "$"), "USD")
Общий синтаксис функций TEXTBORE и TEXTAFTER следующий:
=ТЕКСТПЕРЕД(текст, разделитель) и =ТЕКСТПОСЛЕ(текст, разделитель)
Огромное преимущество этих двух функций заключается в их точности. Вместо использования сложных комбинаций функций ПСТР, НАЙТИ и ДЛСТР я могу добиться чистого извлечения данных, используя простые и легко читаемые формулы. Я часто использую эти функции для разделения номеров моделей, извлечения спецификаций продуктов и извлечения чистых данных из импортированного текста, что раньше требовало многочасового ручного редактирования.
Эти четыре функции решают некоторые из самых распространенных проблем с временем в Excel, таких как поиск данных с помощью гибкого поиска, анализ по нескольким критериям, очистка импортированного текста и извлечение нужной информации из сложных текстовых строк. Большинство людей выполняют эти задачи вручную, тратя часы на то, что обычно занимает всего несколько минут при использовании правильных формул.
Вы использовали эти функции для самых разных целей: от анализа цен на компоненты до отчётов по управлению запасами. Они работают независимо от вашей отрасли, поскольку неразборчивые данные и сложные требования к поиску — это универсальные проблемы. Освоив эти функции, вы удивитесь, как раньше работали с электронными таблицами без них.
