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

Одной из самых гениальных и полезных, на мой взгляд, функцией Excel является «Мгновенное заполнение» (доступно с Microsoft Office 2013 года). Оно творит такие чудеса, что за пару движений пальцами вы сэкономите время и нервы от выполнения огромной рутинной работы. Вместе с тем функция проста как дважды два и подойдёт для пользователей любого уровня. Почувствуйте себя всемогущим, мгновенно решив сложные задачи, это как щелчок Таноса, только лучше)) Заинтриговал?)) Теперь поподробней:


1. У нас имеется таблица с фамилиями, именем и отчеством. Из неё нам нужно сделать «Фамилию и инициалы». Для этого мы в соседнюю строку вводим как это должно быть, затем при вводе на следующей строке у нас появляется предлагаемый вариант, если он нас устраивает, то нажимаем «Enter», если список не появился жмём Ctrl+ E и вуаля, у вас готов весь список по заданной форме.

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Также можно вписать только Фамилию и имя и получить желаемый результат, можно вывести инициалы вперёд, можно оставить только фамилию, в общем вариантов много и Excel сделает как вам нужно

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Здесь есть одно правило, вводимый текст должен быть непосредственно рядом с исходным текстом, не важно слева или справа, но, чтобы рядом, иначе выйдет ошибка

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку
Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Также значения из одного столбца с текстом можно разбить на три разных. Заполняем каждый столбец как нам нужно, нажимаем Ctrl+E на нижней пустой ячейке и получаем готовый результат.

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Также он работает и в обратную сторону, больше не нужны формулы СЦЕПИТЬ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР и т.д.

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Можно задать регистр и сделать фамилии, написанные верхним регистром или полностью капсом. Можно исправлять слова с НеПрАвИлЬнЫм регистром. Если между словами нет пробела, это тоже не проблема, главное, чтобы Ф.И.О. начиналось с заглавной буквы, для определения начала слова

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Можно наоборот из заглавных букв сделать печатные, но в этом случае может неправильно отобразиться результат, в этом случае нажимаем на значок с молнией, где отменям мгновенное заполнение, затем вводим ещё раз в следующую строку и нажимаем Ctrl+ E и в этот раз отобразится как нужно.

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

2. Вы восхищены его супер возможностями? Тогда возрадуйтесь, потому что это ещё не всё)) Есть возможность склейки текста. Итак, у нас имеются значения, разбитые по ячейкам, но нам нужно, чтобы они были в одной ячейке и ещё с дополненным текстом. Вводим в первую строку так, как нам нужно, повторяем то же действие на второй строке и на третьей нажимаем Ctrl+E и радуемся результатом

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

3. Вы уже кричите от того почему вы не знали об этом раньше? Тогда продолжаем удивляться «Мгновенным заполнением»))

У нас есть текст с цифрами в ячейках и нам нужно вывести в другой столбец только цифры, делать это вручную означает почувствовать себя Золушкой, перебирающей горох, но волшебным нажатием Ctrl+E, получаем нужный результат, причём Excel не важно, есть ли там лишние пробелы, знаки или прочее, он как настоящий мужик – берёт и делает.

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

То же самое он делает и с извлечением только текста

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Если попадается разношёрстный текст, где первым стоит слово затем цифра, а в нижних строках имеется сначала цифра потом текст, лучше сначала в первых двух строках дописать цифры перед текстом, затем нажать Ctrl+E, тогда Excel правильно определит алгоритм и выведет текст без ошибок


4. Ты до сих пор сомневаешься в возможностях мгновенного заполнения? Говоришь он не справится с датами? Забери свои слова обратно, даты он изменяет на изи, причём определяет не только цифровые, но и текстовые, главное, чтобы они были в именительном падеже, либо на внятном сокращении месяца

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

5. Ты тайный поклонник нестандартных дат? Не волнуйся, мгновенное заполнение удовлетворит твои желания. Он может изменить обычную дату в Unix-формат ГГММДД. Для этого меняем формат соседнего столбца на текстовый, затем вводим форму значений в пару ячеек, чтобы правильно задать алгоритм и нажав Ctrl+E получаем результат

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

Вы ещё не устали удивляться великолепным «Мгновенным заполнением»? Тогда получайте очередную порцию волшебства))

6. У нас имеется столбец с телефонными номерами, но они выглядят как простые цифры, мы же придадим им красоты, добавив + и заключив код оператора в скобки. Преобразуем цифры и соседний столбец в текстовый формат, затем вводим нужный параметр в первые две ячейки соседнего столбца и нажав Ctrl+E, гордимся проделанной работой

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

7. Для тех, кто выгружает даты из различных ERP и CRM-систем, дата представляется 8-значным числом в формате ГГГГММДД. Исправлять в нормальный вид можно было функцией ДАТАЗНАЧ, либо вручную, но теперь ловите лайфхак от героя «Мгновенного заполнения», главное покажите ему правильный алгоритм, дальше он всё сам сделает за вас))

Мгновенное заполнение в Excel Microsoft Excel, Таблица, Полезное, Длиннопост, На заметку

При использовании мгновенного заполнения помните следующие моменты:

- первое правило напомню, ячейки должны находиться рядом с определяемым столбцом;

- при выявлении шаблона учитываются все значения в строке - слева и справа от вводимого столбца, поэтому лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить;

- мгновенное заполнение также работает и в умных таблицах;

- ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет найти нужное значение и не сработает;

- бывает, что шаблон определяется некорректно, поэтому всегда проверяйте полученные результаты (хотя бы выборочно).


Как вам «Мгновенное заполнение», поделитесь впечатлениями в комментариях))

MS, Libreoffice & Google docs

739 постов14.9K подписчиков

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

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

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

51
Автор поста оценил этот комментарий
Я не понял. В друг у друга темы воруете? Или так совпало?
Мгновенное заполнение
раскрыть ветку (1)
69
Автор поста оценил этот комментарий

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

показать ответы
31
Автор поста оценил этот комментарий
Ваш вариант мне понравился больше 🤗
раскрыть ветку (1)
23
Автор поста оценил этот комментарий

Спасибо)) вы первый кто хорошо отозвался о посте)  я старался с юмором преподнести информацию, но теперь он в баянах, пост пропал

показать ответы
2
Автор поста оценил этот комментарий
О, бог эксельки. Преклоняюсь и подписываюсь!
Облегчите мне жизнь рабочую, пожалуйста. Как протянуть формулу по всему столбцу не протягивая её руками? Я задолбалась тянуть по 2000строк...
раскрыть ветку (1)
7
Автор поста оценил этот комментарий

дважды щёлкнуть левой клавишей мыши по правому краю ячейки, там такая точечка есть

показать ответы
27
Автор поста оценил этот комментарий

@moderator, уберите из баянов, совсем другая подача материала, да и вообще полезно

раскрыть ветку (1)
7
Автор поста оценил этот комментарий

Ого)) спасибо Вам))

показать ответы
27
Автор поста оценил этот комментарий

@moderator, уберите из баянов, совсем другая подача материала, да и вообще полезно

раскрыть ветку (1)
6
Автор поста оценил этот комментарий

@moderator, что за дела? Вчера вытащили пост из баянов, почему опять туда запихали?

Автор поста оценил этот комментарий
Пост без формул - плюсы на ветер.
Где блин строка формул?
раскрыть ветку (1)
5
Автор поста оценил этот комментарий

Там нет формул, это всё делается нажатием ctrl+E

Автор поста оценил этот комментарий

Я так понимаю, вы нашли где-то один источник, и теперь наперегонки тырите на Пикабу, кто быстрее..

раскрыть ветку (1)
4
Автор поста оценил этот комментарий

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

4
Автор поста оценил этот комментарий

пРИКОЛЬНЫЙ пОСТ бЛИН, кАК ЗАМЕНИТЬ& мИмИмИ

Серьезно, полезный пост, благодарю.

раскрыть ветку (1)
4
Автор поста оценил этот комментарий

Спасибо)

Автор поста оценил этот комментарий

Пример:

В скопированных ячейках присутствует некое слово, которое я желаю не видеть.

Задача:

Как можно это слово удалить из всех ячеек разом?

раскрыть ветку (1)
4
Автор поста оценил этот комментарий

если слово одинаковое во всех ячейках, то замена поможет, нажмите Ctrl+H, введите это слово, а вторую строку оставьте пустую, нажмите заменить всё

показать ответы
4
Автор поста оценил этот комментарий
Спасибо Вам за интереснопредставленную инфу♡ душа рокера трепетала
раскрыть ветку (1)
3
Автор поста оценил этот комментарий

Рок навсегда \m/

1
Автор поста оценил этот комментарий
Спасибо за посты! Подписалась, прокачиваю навыки) очень мне в работе пригодятся ваши уроки)
раскрыть ветку (1)
3
Автор поста оценил этот комментарий

Спасибо)) я стараюсь облегчить жить офисных работников))

1
Автор поста оценил этот комментарий
Это всё отлично, но почему в списке нет Глеба Рудольфовича Самойлова и Эдмунда Мечиславовича Шклярского?
раскрыть ветку (1)
2
Автор поста оценил этот комментарий

В следующем посте учту ваши пожелания)

Автор поста оценил этот комментарий

Откуда  у вас список музыкантов с отчествами? Или вы их наизусть знаете? :)

раскрыть ветку (1)
2
Автор поста оценил этот комментарий

Люблю рок, уважаю великих певцов))

5
Автор поста оценил этот комментарий

Ты чертов гений! (А вовсе не тот, кто всего лишь придумал и внедрил сию функцию 😉). Подписываюсь! Моя (жизнь) работа никогда больше не будет прежней!

раскрыть ветку (1)
2
Автор поста оценил этот комментарий

Спасибо) как сказал Николай Павлов "Когда знаешь, всё просто"))

Автор поста оценил этот комментарий
Всё это, конечно, хорошо, но экселем и вордом невозможно пользоваться без строки меню, поэтому офис 2010 и новее идут нафиг...
раскрыть ветку (1)
2
Автор поста оценил этот комментарий

Офис 2007 почти такой же как и другие версии, на любое значение есть горячие клавиши

показать ответы
раскрыть ветку (1)
4
Автор поста оценил этот комментарий

@moderator, в моём посту больше информации, там я разъяснил приёмы и возможные ошибки, он текстовый, он более содержательный, вытащите его из баянов

показать ответы
1
Автор поста оценил этот комментарий

Всё работает в MS office. Огромное вам спасибо, пишите ещё!

М.б. для вас это элементарно, но сейчас вы очень сильно помогли!

раскрыть ветку (1)
1
Автор поста оценил этот комментарий

Рад был вам помочь)

Автор поста оценил этот комментарий

Почему-то не нашел.

Сейчас MS office скачаю, попробую - я вообще туповат в таких вещах)

раскрыть ветку (1)
1
Автор поста оценил этот комментарий

Наше сообщество создано, чтобы помогать пользователям)

показать ответы
Автор поста оценил этот комментарий

Не работает)

А если без быстрых клавиш, где искать в меню?

По идее, такая функция должна быть, но не нашел.

раскрыть ветку (1)
1
Автор поста оценил этот комментарий

На вкладке данные - мгновенное заполнение

показать ответы
1
Автор поста оценил этот комментарий

С какого вы города? Я в Навои раньше жил)

раскрыть ветку (1)
1
Автор поста оценил этот комментарий

Я из Ташкента

2
Автор поста оценил этот комментарий
Исправляла формат. Но при вставке все равно меняет (
При этом, если вставить сперва (он меняет в дату), а потом изменить формат - то число другое становится.
раскрыть ветку (1)
1
Автор поста оценил этот комментарий

Как так? вот например у вас есть одна ячейка с датой (в каком формате стоит ячейка? формат ДДММГГГГ? нужно, чтобы был текстовый) вторая ячейка пустая, в ней тоже нужно поставить текстовый формат, затем скопировать и вставить в неё дату

показать ответы
3
Автор поста оценил этот комментарий
А есть лечение для точки, которую ексель не понимает и при копировании меняет на месяц? Например, 10.02 становится 10.фев
раскрыть ветку (1)
1
Автор поста оценил этот комментарий

Формат ячеек исправляли? Если поставить дата, то она должна быть полностью 10.02.2019, если вам даты не нужны для формул, вы можете поставить текстовый формат и можно будет вводить любые цифры, они не изменятся

показать ответы
1
Автор поста оценил этот комментарий

Если я копирую набор вещественных чисел с разделителем дробной части "точка" в эксель (например 13.02, они автоматически превращаются в дату. и  плевать что у меня там простые числа, она все равно превратится в дату.

Как это отключить?

раскрыть ветку (1)
1
Автор поста оценил этот комментарий

Поставьте формат копируемых ячеек текстовый и куда вставлять будете тоже текстовый и должно получиться

показать ответы
Автор поста оценил этот комментарий

Дело не в 1С. Я с неё выгружаю данные в excel. С CRM тоже выгружаю данные в excel. И вот тут нужно сопоставить номенклатуру. Т.е., например, в 1с название Яблоки Голден 1с, а в CRM Голден 1сорт. Как-то можно сопоставить эти данные, чтобы цифры по этой номенклатуре сложить/вычесть/умножить между собой?

раскрыть ветку (1)
Автор поста оценил этот комментарий

а если просто сравнить таблицы без придания им названий?

Автор поста оценил этот комментарий

Привет! Подскажи, пожалуйста, есть ли возможность в excel подвязать разные названия одной и той же номенклатуры? Ну например, есть 2 программы (1С и CRM) - в этих программах одна и та же номенклатура называется по-разному. Мне нужно, чтобы, например, один раз сделать выгрузку названий в Excel и придать каждому артикулу одно общее название, по которому Excel будет искать их при добавлении данных, например, по количеству. Хз понятно ли объяснил?)

В общем мне надо сравнить оборот из 1с с продажами из CRM. Но проблема в разных названиях как торговых точек так и номенклатуры.

раскрыть ветку (1)
Автор поста оценил этот комментарий
Я с 1С не никогда не работал, поэтому я не знаю как вам помочь(
показать ответы
1
Автор поста оценил этот комментарий

Пришлось специально зарегистрироваться чтобы выразить "благодарчики"! Спасибо!

раскрыть ветку (1)
Автор поста оценил этот комментарий

И вам спасибо)

Автор поста оценил этот комментарий

Блин... прошу прощения, а для Либреофис это не работает?

раскрыть ветку (1)
Автор поста оценил этот комментарий

Не пользовался, не знаю, но вы можете проверить и написать об этом

показать ответы
Автор поста оценил этот комментарий

Ух ты, а не подскажете, как добавить в каждую ячейку слово?

Пример привожу, вот перед каждым названием надо добавить Ткань мебельная.

Это вообще реально?

Иллюстрация к комментарию
раскрыть ветку (1)
Автор поста оценил этот комментарий

Вставьте возле столбца "А" дополнительный столбец, не важно с какой стороны, затем в первой ячейке впишите "ткань мебельная ACM", во второй "ткань мебельная Barley" (покажите Excel алгоритм, что вы от него хотите), на третьей строке нажмите CTRL+E и будет вам счастье

показать ответы
1
Автор поста оценил этот комментарий
ААААА, это полезнее чем все, что я узнал за прошлый год!!!
раскрыть ветку (1)
Автор поста оценил этот комментарий

Это магия)))

Автор поста оценил этот комментарий

Может кто знает, мне с некоторой периодичностью приходится в ячейку вводить, к примеру, одну и ту же фамилию. Иногда начинаешь её вводить и окончание фамилии Excel сам предлагает, остаётся только нажать enter. Но иногда это не срабатывает. Есть ли где-то настройки этой функции, что б он, к примеру, постоянно, уже с первых 2х букв пытался сразу предложить это окончание?

раскрыть ветку (1)
Автор поста оценил этот комментарий

если один раз написать фамилию, то дальше excel будет предлагать ее, иначе никак

показать ответы
1
Автор поста оценил этот комментарий
За Дашу Ставрович держи плюс)
раскрыть ветку (1)
Автор поста оценил этот комментарий

Нуки рулит \m/

1
Автор поста оценил этот комментарий

@moderator,  а давайте теперь это еще в формате видео выложим и тоже не баяном будет. А еще этот тэг "моё" на обоих постах. У кого-то мульти акк или просто раздвоение личности?

раскрыть ветку (1)
1
Автор поста оценил этот комментарий

Пост был создан мной, не корректируй теги если не знаешь правил! В моём посте больше информации.

показать ответы
Автор поста оценил этот комментарий
Можно к вам обратиться документик заказать? Как с вами связаться?
раскрыть ветку (1)
Автор поста оценил этот комментарий

Какой документ?

показать ответы
Автор поста оценил этот комментарий

А как например из всех ячеек удалить определенный текст или числа?

раскрыть ветку (1)
Автор поста оценил этот комментарий

приведите пример

показать ответы
1
Автор поста оценил этот комментарий

Если ты нашел картинку в интернете, это еще не значит, что она твоя. @moderator, Обьясни ему правила. Налицо намерянный игнор баянометра и правил тегов.

раскрыть ветку (1)
Автор поста оценил этот комментарий

Какая картинка из интернета? Это мой скриншот, моё оформление, баянометр ничего не показал, ты со своими минусовыми постами разберись

показать ответы