Excel. Мгновенное заполнение

Рубрика: 7. Полезняшки Excel

При импорте данных часто требуется немного подправить текст. Например, имена были записаны в верхнем регистре, а в них нужно оставить заглавными только первые буквы. Один из вариантов — использовать формулы для изменения текста (например, =ПРОПНАЧ). Другой способ — применить новую функцию Excel 2013, которая называется Мгновенное заполнение. [1]

%d1%80%d0%b8%d1%81-1-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2%d0%b5%d0%bd%d0%bd%d0%be%d0%b5-%d0%b7%d0%b0%d0%bf%d0%be%d0%bb%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b1%d1%8b%d1%81%d1%82%d1%80%d0%be-%d0%bf%d1%80%d0%b5

Рис. 1. Мгновенное заполнение быстро преобразует имена из столбца А в нужный регистр

Скачать заметку в формате Word или pdf, примеры в формате Excel

Мгновенное заполнение использует распознавание шаблонов для извлечения, а также конкатенации (объединения текста) данных из смежных столбцов. Просто введите несколько примеров в столбец, прилегающий к столбцу с данными, а потом выполните команду Данные –> Работа с данными –> Мгновенное заполнение, или нажмите Ctrl+E (английское). Excel проанализирует введенные данные и попытается заполнить остальные ячейки. Если Excel не распознает шаблон, который вы имели в виду (и заполнит ячейки неудачно), нажмите Ctrl+Z, добавьте еще пару примеров и попробуйте снова.

Например (рис. 1), в столбце В приведен список президентов США. Введите в ячейку С1 «Джордж Вашингтон», и начните набирать в ячейке С2 «Джон А…». Вы заметите, что Excel вмешается уже на этапе ввода. Программа распознает шаблон (который можно описать как «оставить заглавными во всем тексте только первые буквы») и заполнит столбец преобразованным (светло-серым) текстом. Нажмите Enter, чтобы подтвердить догадку Excel, либо внести еще несколько записей. В любой момент можно нажать Ctrl+E, чтобы указать программе заполнить столбец по угаданному шаблону.

В следующем примере (рис. 2) требуется извлечь фамилии президентов так, чтобы список можно было отсортировать по фамилиям. Это простая задача для Мгновенного заполнения. Программа распознает шаблон уже на второй записи. Теперь можно отсортировать список по столбцу С в алфавитном порядке. Правда, возможны и осечки (обратите внимание на две последние записи на рис. 3).

%d1%80%d0%b8%d1%81-2-%d0%b8%d0%b7%d0%b2%d0%bb%d0%b5%d1%87%d0%b5%d0%bd%d0%b8%d0%b5-%d1%84%d0%b0%d0%bc%d0%b8%d0%bb%d0%b8%d0%b9-%d0%bc%d0%b5%d1%82%d0%be%d0%b4%d0%be%d0%bc-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2

Рис. 2. Извлечение фамилий методом мгновенного заполнения

%d1%80%d0%b8%d1%81-3-excel-%d0%bd%d0%b5-%d1%87%d0%b5%d0%bb%d0%be%d0%b2%d0%b5%d0%ba-%d0%be%d0%bd-%d0%bf%d1%80%d0%be%d1%81%d1%82%d0%be-%d0%b8%d0%b7%d0%b2%d0%bb%d0%b5%d0%ba%d0%b0%d0%b5%d1%82

Рис. 3. Excel – не человек; он просто извлекает последнее слово из записи 🙂

Мгновенное заполнение правильно извлекает и имена (рис. 4). Опять же Excel распознал шаблон уже на второй записи. Подсказка у меня почему-то не сработала, но заполнение ячейки D1 и последующая команда Ctrl+E сделали свое дело.

%d1%80%d0%b8%d1%81-4-%d0%b8%d0%b7%d0%b2%d0%bb%d0%b5%d1%87%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b8%d0%bc%d0%b5%d0%bd-%d0%bc%d0%b5%d1%82%d0%be%d0%b4%d0%be%d0%bc-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2%d0%b5%d0%bd%d0%bd

Рис. 4. Извлечение имен методом мгновенного заполнения

У некоторых президентов есть не только имя и фамилия, но и второе имя. Может ли функция мгновенного заполнения извлекать вторые имена? Отчасти. Я вводил в Excel несколько примеров, содержавших вторые имена, и Мгновенное заполнение успешно их извлекало. Однако если в наборе были перемешаны записи, содержавшие второе имя, и записи, включавшие только фамилию и имя, то программа везде извлекала имя. Как я ни пытался, мне не удалось заставить приложение в такой ситуации игнорировать записи из двух компонентов.

Мгновенное заполнение может быть полезным и для извлечения имени домена (или имени файла) из полного URL-адреса (рис. 5).

%d1%80%d0%b8%d1%81-5-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2%d0%b5%d0%bd%d0%bd%d0%be%d0%b5-%d0%b7%d0%b0%d0%bf%d0%be%d0%bb%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b8%d0%b7%d0%b2%d0%bb%d0%b5%d0%ba%d0%b0%d0%b5%d1%82

Рис. 5. Мгновенное заполнение извлекает имена доменов и файлов из URL-адреса

Мгновенное заполнение — отличная функция, но при использовании ее с важными данными следует учитывать некоторые возможные проблемы:

  • Иногда Мгновенное заполнение просто не работает. Казалось бы, извлечение вторых имен — несложный шаблон, но Мгновенное заполнение его не распознало.
  • Механизм неточный. Опробовав Мгновенное заполнение на небольшом количестве данных, вы убедитесь, что оно сработало верно. Однако при применении данной возможности с тысячами строк вы не можете быть уверены в абсолютной правильности, не проверив каждую строку самостоятельно. Лучше использовать Мгновенное заполнение с данными, для которых характерно определенное единообразие.
  • Мгновенное заполнение нединамическое. Если вы измените какую-либо информацию, к которой было применено Мгновенное заполнение, изменения не отразятся в автоматически заполненном столбце.
  • Отсутствует «аудиторский след». Если вы применяете формулы для извлечения данных, то они служат «документацией» для любого пользователя, желающего узнать, как были извлечены сведения. При использовании Мгновенного заполнения такой «аудиторский след» отсутствует. Вы никак не сможете определить, какие правила использовались в Excel при извлечении данных.

Мгновенное заполнение умеет не только извлекать данные. Оно также способно объединить данные. Если необходимо объединить данные в одном или нескольких столбцах, можно написать формулу, использующую оператор конкатенации (&). Например, в следующей формуле комбинируется содержимое ячеек A1, В1 и С1: =А1&В1&С1. Если требуется выполнять более сложные варианты объединения, Мгновенное заполнение может сделать эту работу автоматически и избавить вас от сложностей, связанных с созданием и отладкой формулы.

Например, в столбце А (рис. 6) перечислены имена, а в столбце В — фамилии. Можно воспользоваться Мгновенным заполнением для создания электронных адресов (в столбце С) для домена example.ru. Эти адреса состоят из первого инициала, нижнего подчеркивания и фамилии — все в нижнем регистре.

%d1%80%d0%b8%d1%81-6-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2%d0%b5%d0%bd%d0%bd%d0%be%d0%b5-%d0%b7%d0%b0%d0%bf%d0%be%d0%bb%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d0%bf%d0%be%d0%b7%d0%b2%d0%be%d0%bb%d1%8f%d0%b5%d1%82

Рис. 6. Мгновенное заполнение позволяет быстро создать из имен и фамилий адреса электронной почты

Достаточно ввести в ячейку С1 адрес и нажать Ctrl+E, и Мгновенное заполнение распознает шаблон и запишет оставшуюся часть информации в столбец С. Работа с Мгновенным заполнением значительно проще, чем создание равнозначной формулы: =СТРОЧН(ЛЕВСИМВ(A1;1)&"_"&B1&"@example.ru")

И последний пример (рис. 7). В столбцах A:D содержатся исходные данные, а в столбце Е было выполнено Мгновенное заполнение на основании записи в Е2. Мгновенное заполнение сработало не идеально, и в строке 3 отразилось «2016 в.». Если раскрыть контекстную подсказку, то вы увидите несколько опций (рис. 8). Так вот, пока вы не приняли предложение этого меню, можно отредактировать исходную ячейку (Е2), и содержимое остальных ячеек столбца Е изменится. Я удалил фрагмент « г.», и ячейки приняли релевантный вид (рис. 9).

%d1%80%d0%b8%d1%81-7-%d1%82%d0%b5%d0%ba%d1%81%d1%82-%d0%b2-%d1%81%d1%82%d0%be%d0%bb%d0%b1%d1%86%d0%b5-%d0%b5-%d1%81%d0%b3%d0%b5%d0%bd%d0%b5%d1%80%d0%b8%d1%80%d0%be%d0%b2%d0%b0%d0%bd-%d1%81-%d0%bf

Рис. 7. Текст в столбце Е сгенерирован с помощью Мгновенного заполнения

%d1%80%d0%b8%d1%81-8-%d0%ba%d0%be%d0%bd%d1%82%d0%b5%d0%ba%d1%81%d1%82%d0%bd%d0%be%d0%b5-%d0%bc%d0%b5%d0%bd%d1%8e-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2%d0%b5%d0%bd%d0%bd%d0%be%d0%b3%d0%be-%d0%b7%d0%b0%d0%bf

Рис. 8. Контекстное меню Мгновенного заполнения

%d1%80%d0%b8%d1%81-9-%d0%ba%d0%be%d1%80%d1%80%d0%b5%d0%ba%d1%82%d0%bd%d1%8b%d0%b9-%d0%b2%d0%b0%d1%80%d0%b8%d0%b0%d0%bd%d1%82-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2%d0%b5%d0%bd%d0%bd%d0%be%d0%b3%d0%be-%d0%b7

Рис. 9. Корректный вариант Мгновенного заполнения

Эквивалентная формула для генерирования текста: =A2&" "&B2&": "&ТЕКСТ(D2;"$0")&" от "&C2&" октября 2016"

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 189–192.


Прокомментировать