Составьте краткое изложение этой истории. Вот краткое изложение содержания истории, основанное на фактах: Вот упрощенная версия содержания истории: Вот беззаботный взгляд на содержание истории: Исследуйте другую точку зрения: Покажите мне факты Объясните это так, будто мне 5 лет Дайте мне беззаботный пересказ
Резюме
- Формула по примеру в Microsoft Excel помогает автоматизировать повторяющиеся задачи в структурированных таблицах Excel для Интернета.
- Инструмент похож на Flash Fill, но генерирует динамические формулы, которые обновляются при изменении ваших данных.
- Формула по примеру упрощает разделение и объединение текста, создание числовых последовательностей, извлечение информации из дат и создание арифметических формул.
Вам надоело вручную вводить повторяющиеся данные в электронную таблицу? Или вы разочарованы тем, что функция Flash Fill не всегда работает так, как ожидалось? Ну, остановитесь на этом, потому что малоизвестный инструмент Microsoft Excel, Formula by Example, может сделать всю тяжелую работу за вас.
На момент написания статьи (июнь 2025 г.) функция «Формула по примеру» доступна только тем, кто использует англоязычную версию Excel для Интернета с бесплатной учетной записью Microsoft или платной подпиской. Она доступно в настольном приложении Microsoft Excel.
Функция Flash Fill, представленная в Microsoft Excel в 2013 году, стала экономящим время инструментом. Она распознает закономерности в ваших данных и использует их для предложения способов автоматического заполнения оставшейся части столбца.
Однако, несмотря на свои преимущества, этот инструмент имеет серьезное ограничение — если исходные данные изменятся, Flash Fill не сможет вернуться и повторно применить шаблон к новой информации. Другими словами, значения, созданные Flash Fill, являются статическими, что бесполезно, если вы пытаетесь создать динамическую электронную таблицу.
Еще один недостаток Flash Fill заключается в том, что вся работа выполняется за кулисами. Да, он может распознавать шаблоны и дополнять данные для вас, но вы не можете узнать, как он это сделал. Если вы, как и я, заинтересованы в понимании внутренней работы вашей электронной таблицы, это досадный недостаток.
К счастью, Microsoft осознала эти проблемы и предложила превосходное решение — Формулу на примере.
Формула на примере: подготовка данных
Чтобы использовать функцию «Формула по примеру», сначала откройте Excel в Интернете и войдите в свою учетную запись Microsoft.
Затем откройте существующую электронную таблицу или нажмите «Пустая книга», чтобы начать с чистого листа.
Важно отметить, что Formula by Example работает только в форматированных таблицах Excel, а не в обычных диапазонах. Поэтому выберите данные и на вкладке «Вставка» на ленте нажмите «Таблица».
Если в вашей таблице есть заголовки, установите флажок в диалоговом окне, а затем нажмите «ОК».
Если вы предпочитаете использовать сочетания клавиш, выберите одну из ячеек в вашем непрерывном диапазоне и нажмите Ctrl+A, чтобы выбрать все ячейки. Затем нажмите Ctrl+L, чтобы открыть диалоговое окно «Создать таблицу», и, наконец, нажмите Enter, чтобы создать таблицу.
Теперь, когда вы преобразовали свои данные в таблицу Excel в Excel для Интернета, вы готовы использовать инструмент «Формула по примеру».
Пример 1: Преобразование текста
Существует множество способов преобразования текста из одного столбца в другой, включая использование инструмента «Текст в столбцы» или открытие редактора Power Query в Excel. Однако «Формула по примеру» — пожалуй, самый интуитивный.
В этом примере предположим, что вы хотите извлечь имена людей в столбец B из их адресов электронной почты в столбце A. Вы также хотите, чтобы имена были написаны заглавными буквами.
Начните с ввода первых нескольких примеров того, как вы хотите преобразовать текст. Как только Excel распознает шаблон, появится плавающее окно, предлагающее заполнить оставшиеся ячейки в этом столбце формулой. На этом этапе вы можете либо бросить взгляд вниз по столбцу, чтобы увидеть, соответствуют ли оставшиеся значения ожидаемым, либо нажать «Показать формулу», чтобы увидеть формулу, которую Excel сгенерировал для преобразования данных.
В этом случае формула имеет вид:
=ПРОПНАЧ(ТЕКСТПЕРЕД([@Email], «.», 1)) & » » & ПРОПНАЧ(СРЕДИН(ЛЕВС([@Email], НАЙТИ(«@», [@Email]) — 1), НАЙТИ(«.», [@Email]) + 1, ДЛСТР([@Email])))
где части формулы PROPER отвечают за регистр букв, а части TEXTBORE, MID, LEFT, FIND и LEN отвечают за позиционирование текста для извлечения.
Уделите минутку, чтобы оценить сложность этой формулы и то, сколько времени вам потребовалось бы, чтобы сгенерировать ее самостоятельно. В таких ситуациях Formula by Example — это очень удобный инструмент, который сэкономит вам кучу времени.
Если вы удовлетворены результатом, нажмите «Применить», чтобы завершить заполнение столбца.
Теперь, поскольку для создания этого текстового преобразования использовалась формула, вы можете использовать ее в других наборах данных — просто скопируйте и вставьте формулу в соответствующую ячейку и обновите части формулы, которые ссылаются на определенный столбец в исходной таблице (в данном случае, [@Электронная почта]) с новой структурированной ссылкой.
Более того, вы можете узнать о некоторых функциях, которые вы ранее не видели, внимательно изучив формулу. Однако самое важное — и самое большое преимущество использования этого инструмента по сравнению с Flash Fill — заключается в том, что если значения в предшествующих ячейках изменятся, формулы в зависимых ячейках подхватят эти изменения и автоматически обновят результат.
Формула, которую генерирует Excel, может быть специфична для структуры исходных данных. Например, если часть формулы ищет заглавные буквы в указанной ячейке, но в ней их нет, вы можете увидеть ошибку. Чтобы избежать этого, убедитесь, что ваши данные последовательно структурированы и отформатированы.
В аналогичном примере Excel создает формулу, которая извлекает первые три буквы кода продукта во второй столбец отформатированной таблицы:
=ЛЕВЫЙ([@[Код продукта]], 3)
Затем я использовал тот же принцип в столбце C, чтобы извлечь последние три числа. Вот формула, которую он сгенерировал:
=ЗНАЧЕНИЕ(ПРАВСИМВ([@[Код продукта]], ДЛСТР([@[Код продукта]]) — 3))
Пример 2: Нумерация строк
Одна из проблем с форматированными таблицами Excel заключается в том, что они несовместимы с динамическими формулами массива, то есть вы не можете использовать функции, которые распределяют результат по нескольким ячейкам, например, SORT или FILTER.
В этом примере предположим, что вы хотите добавить динамические числа в столбец A, чтобы можно было вести подсчет количества игроков в игре в любой момент.
Один из способов сделать это в неформатированной области электронной таблицы — вложить функцию COUNTA в функцию SEQUENCE, чтобы подсчитать количество ячеек в столбце A, содержащих значение, а затем использовать это для создания последовательности, которая увеличивается на единицу в каждой строке. Однако в таблице Excel это невозможно, поскольку функция SEQUENCE создает разлитый массив.
Однако благодаря «Формуле по примеру» есть обходной путь.
Начните с ввода первых нескольких чисел в столбец A, и когда Excel распознает шаблон, проверьте формулу и нажмите «Применить».
Нажатие «Apply As Value» вставляет числа как значения, а не формулы, как будто вы вводите их вручную. По сути, Excel дает вам возможность использовать функцию Flash Fill вместо Formula by Example, что в большинстве случаев не то, что вы захотите сделать.
Теперь вы знаете, что когда вы добавляете или удаляете строки в таблицу или из нее, счетчик автоматически обновляется. Более того, вы узнали, что можно использовать функцию ROW и ссылаться на заголовки столбцов, чтобы преодолеть проблему несовместимости с разливом таблиц, о которой я говорил ранее.
Пример 3: Работа с датами
Формула по примеру не просто помогает вам преобразовывать текст — она также хорошо работает с датами.
В этом примере после ввода первых нескольких лет в столбец B Excel распознал, что я беру эту информацию из столбца «Дата», и создал соответствующую формулу:
=ГОД([@Дата])
В столбце C программа увидела, что я преобразовываю месяц из сокращенной формы в полное название месяца, поэтому она сгенерировала следующую формулу:
=ТЕКСТ([@Дата], «мммм»)
Наконец, в столбце D я использовал функцию DAY для завершения моего набора данных:
=ДЕНЬ([@Дата])
Как и в примере 1, если какая-либо из исходных дат в столбце A изменится, соответствующие разделения в столбцах B, C и D обновятся соответственно. Аналогично, если вы добавите больше строк в конец таблицы, они примут те же формулы.
Пример 4: Генерация арифметических вычислений
Формула по примеру в Excel для Интернета также может показать, как можно получить результат с помощью формулы.
В этом примере, когда я вручную выполняю первые несколько умножений, Excel определяет закономерность и предлагает формулу.
Затем, в следующем столбце, когда я начал округлять полученные значения до ближайшего десятка, Excel порекомендовал мне использовать функцию ОКРУГЛВВЕРХ для завершения этой операции.
Теперь предположим, что я допустил ошибку при вводе количества магазинов в ячейку E3 — вместо 6 должно было быть 16. Итак, позвольте мне вернуться и исправить эту цифру, и мы посмотрим, обновятся ли значения в столбцах D и E автоматически.
Действительно, это так, поскольку для расчета этих значений Excel использовал формулу, а не функцию мгновенного заполнения.
Лучший способ привыкнуть к Formula by Example в Excel для Web — попробовать и посмотреть, что он может сделать. Например, помимо создания формул для сценариев на снимках экрана выше, этот инструмент может удалять ненужные пробелы из ваших данных, выполнять прямое заполнение столбца на основе примеров в первых нескольких строках и дублировать значения из предыдущего столбца, добавляя дополнительные символы.
