При работе с данными в Excel некоторые задачи могут показаться излишне утомительными. Возможно, вам нужно разделить столбец с полными именами на отдельные столбцы для имени и фамилии или объединить текст из нескольких ячеек, используя определённые запятые. Это не сложные аналитические задачи, а простые задачи обработки данных, которые возникают регулярно.
Хорошая новость заключается в том, что в Excel есть встроенные функции, специально разработанные для таких ситуаций. Однако их часто упускают из виду, поскольку они не являются частью Стандартный набор инструментов Excel, который осваивает большинство людей, включая меня. Функции, которые я здесь рассмотрю, не предназначены для сложных вычислений, но если вам приходится выполнять повторяющуюся работу с данными, эти функции могут сэкономить вам время.
Быстрые ссылки
5. ТЕКСПЛИТ
Разделяет склеенные тексты
Если вы когда-либо получали таблицу, в которой кто-то запихнул свои имя, фамилию, а может быть, даже отчество, в одну ячейку, вы знаете, как сложно разделить эти данные. TextSplit решает именно эту проблему: он берёт текст из одной ячейки и разбивает его на несколько столбцов, используя указанный вами разделитель.
Давайте поработаем с примером таблицы продаж. Вы увидите имена торговых представителей: «Сара Чен», «Майк Джонсон» и «Лиза Парк», все в одном столбце. Вместо того, чтобы вручную вводить каждое имя в отдельные столбцы, TextSplit может сделать это автоматически.
Формула выглядит следующим образом:
=TEXTSPLIT(текст, разделитель_столбцов, [разделитель_строк], [игнорировать_пустое], [режим_сопоставления], [заполнение_с_колонкой])
Вот что делает каждый учитель:
- текст: Ячейка, содержащая текст, который вы хотите разделить.
- col_delimiter: Символ, разделяющий данные (например, пробел, запятая или точка с запятой).
- row_delimiter (необязательно): Используется при разделении на строки и столбцы.
- ignore_empty (необязательно): TRUE игнорирует пустые значения, FALSE сохраняет их (по умолчанию FALSE).
- match_mode (необязательно): Управляет чувствительностью к регистру (0 — с учетом регистра, 1 — без учета регистра).
- pad_with (необязательно): Чем заполнять пустые ячейки, если результаты имеют неравную длину?
Например, для имен торговых представителей я бы использовал следующую формулу, чтобы разбить имена на отдельные столбцы:
=ТЕКСТРАЗДЕЛ(A2; " ")
Функция автоматически создаёт необходимое количество столбцов на основе ваших данных. Хотя этот базовый подход работает в большинстве случаев, существуют дополнительные параметры, которые дают вам более точный контроль. Функция ТЕКСТРАЗДЕЛ в Excel.
4. ТЕКСТ ПРИСОЕДИНИТЬСЯ
Объединить несколько ячеек в одну ячейку
Функция TEXTJOIN действует наоборот функции TEXTSPLIT. Она берёт текст из нескольких ячеек и объединяет его в одну, используя выбранный вами разделитель. Это полезно, когда нужно создать последовательные значения, например, полные адреса, описания товаров или списки адресов электронной почты.
Формула выглядит так:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Вот что контролирует каждый параметр:
- разделитель: Символ или текст, разделяющий встроенные значения (запятая, пробел, тире и т. д.).
- игнорировать_пустое: TRUE — игнорировать пустые ячейки, FALSE — включить их в результат.
- текст1, текст2 и т. д.: Ячейки или диапазоны, которые вы хотите объединить (можно указать отдельные ячейки или целые диапазоны).
Если бы в таблице продаж у меня были отдельные столбцы для имени и региона, но мне нужно объединить их в один столбец, я бы использовал TEXTJOIN. ignore_empty Значение TRUE означает, что все пустые ячейки автоматически пропускаются.
=TEXTJOIN(" - ", TRUE, B2, D2)При выборе между различными методами интеграции текста необходимо понимать Различия между функциями CONCAT и TEXTJOIN Это поможет вам выбрать правильный инструмент для ваших конкретных потребностей в интеграции данных.
3. ВЫБЕРИТЕ
Укажите конкретные столбцы ваших данных.
Функция CHOOSECOLS позволяет извлекать определённые столбцы из диапазона без копирования и вставки, а также без создания ссылок. Если у вас большой набор данных, но для анализа нужны только столбцы 2, 5 и 8, эта функция выберет нужные данные и отбросит остальные.
На основе данных о продажах мне может потребоваться извлечь только имена продавцов и их имена, игнорируя даты заказов, категории продуктов и другие данные. Вместо ручного выбора и копирования столбцов функция CHOOSECOLS создаёт динамическую ссылку, которая автоматически обновляется при изменении исходных данных.
Функция следует следующей формуле:
=CHOOSECOLS(array, col_num1, [col_num2], ...)
Вот как работает каждый параметр:
- массив: Диапазон или таблица, содержащая исходные данные (это может быть диапазон ячеек, например A1:F100, или ссылка на таблицу).
- col_num1: Номер первого столбца, который вы хотите извлечь (1 для первого столбца, 2 для второго столбца и т. д.).
- col_num2 и т. д.: Дополнительные номера столбцов, которые вы хотите включить (необязательно — вы можете указать столько, сколько хотите).
Например, если бы я хотел извлечь имена торговых представителей из столбца 2 и их статус из столбца 9, я бы использовал:
=ВЫБРАТЬДЕСЯТКИ(A1:I23; 2; 9)
Функция возвращает оба столбца в виде потокового массива, размер которого автоматически изменяется в соответствии с данными. Именно поэтому CHOOSECOLS — один из… Функции Excel, которые могут сэкономить вам много времениЭто устраняет необходимость использования нескольких формул ВПР или ручного копирования столбцов при работе с большими наборами данных.
В Excel также есть функция CHOOSEROWS, которая работает аналогичным образом, но выбирает определенные строки вместо столбцов, используя ту же структуру формулы с номерами строк.
2. ВОЗЬМИ и ОТБРОСЬ
Извлечение частей ваших данных
Команды TAKE и DROP работают в паре, захватывая определённые части диапазона данных. TAKE извлекает определённое количество строк или столбцов из начала или конца набора данных, а DROP удаляет строки или столбцы из начала или конца, оставляя только то, что осталось.
Эти функции служат точными инструментами для выборки данных. Независимо от того, нужны ли вам только первые десять строк данных для быстрого анализа или вы хотите удалить строки заголовков, которые загромождают вычисления, эти функции справятся с этой задачей без проблем.
TAKE использует следующую формулу:
=TAKE(массив, строки, [столбцы])
DROP следует схожей схеме:
=DROP(массив, строки, [столбцы])
Вот как работают параметры для обеих функций:
- массив: Диапазон исходных данных, который вы хотите извлечь или изменить.
- ряды: Количество строк, которые необходимо взять/отбросить (положительные числа работают сверху, отрицательные снизу).
- столбцы (необязательно):
Число столбцов, которые вы хотите убрать или удалить (положительное слева, отрицательное справа).
Чтобы получить первые пять строк данных о продажах, используйте следующую формулу:
=ВЗЯТЬ(A1:C100; 5)
Чтобы удалить первые 20 строк и работать с чистыми данными, попробуйте:
=ОТБРОСИТЬ(A1:C23; 20)
Вы можете комбинировать операции со строками и столбцами. Например, следующая формула возвращает первые десять строк и первые три столбца:
=ВЗЯТЬ(A1:F23; 10; 3)
Эти функции становятся очень полезными, особенно когда вам нужны динамические подмножества данных, которые адаптируются автоматически. Как использовать функции TAKE и DROP в Excel Это открывает вам возможности создания гибких отчетов, адаптирующихся к изменяющимся размерам наборов данных.
1. ОБЩИЙ
Мощные вычисления, обрабатывающие сложные данные
Функция АГРЕГАТ объединяет функциональность 19 различных статистических функций в одной гибкой формуле. Её отличительной особенностью является возможность игнорировать ошибки, скрытые строки и отфильтрованные данные, чего не могут сделать стандартные функции, такие как СУММА или СРЗНАЧ.
Если ваши данные содержат ошибки #N/A или вы отфильтровали данные только по определённым регионам, AGGREGATE может рассчитать суммы, средние значения или другие статистические данные, не искажая результаты. Я считаю это полезным при работе с динамическими наборами данных, где видимость и качество данных часто меняются.
Структура предложения включает несколько компонентов:
=АГРЕГАТ(номер_функции; параметры; массив; [k])
Каждый критерий контролирует различные аспекты расчета:
- номер_функции: Число от 1 до 19, которое определяет используемую функцию (1=СРЗНАЧ, 4=МАКС, 9=СУММА, 12=МЕДИАНА и т. д.).
- опции: Управляет тем, что следует игнорировать при расчете (0=нет, 1=скрытые строки, 2=значения ошибок, 3=скрытые строки и ошибки, 5=только значения ошибок, 6=скрытые строки и значения ошибок).
- массив: Диапазон ячеек, подлежащих расчету.
- к (необязательно):
- Используется только с определенными функциями, такими как LARGE, SMALL или PERCENTILE.
Чтобы суммировать отображаемые суммы продаж, игнорируя любые ошибки, я могу использовать:
=АГРЕГАТ(9; 6; D2:D23)
Число 9 определяет функцию SUM, а число 6 указывает функции игнорировать как скрытые строки, так и значения ошибок.
Именно эта мощная возможность выполнять вычисления является причиной включения AGGREGATE. Список функций Excel, которые должен знать каждый офисный работник— Он справляется с хаосом реальных данных, с которым более простые функции не могут эффективно справиться.
Встроенные инструменты, которые стоит использовать
Самые важные функции Excel часто не те, которые люди осваивают в первую очередь. Однако они решают тонкие проблемы, возникающие при реальной работе с электронными таблицами, включая работу с неструктурированными текстовыми данными, извлечение отдельных фрагментов из больших наборов данных и выполнение вычислений с неполными данными. Ни одна из рассмотренных нами функций не требует глубоких навыков работы с Excel. Однако функции TEXTSPLIT, CHOOSECOLS, TAKE и DROP доступны только в Microsoft 365 и Excel Web.
В следующий раз, когда вам придётся постоянно очищать данные или вручную копировать столбцы, помните об этих функциях. Они уже встроены в Excel для решения этой утомительной задачи, так что вы сможете сосредоточиться на том, что на самом деле говорят вам данные.
