В статье рассказывается, как настроить подстановку данных в документы сложной структуры
(где каждая буква — в отдельной клетке), при помощи программы заполнения документов.
Выглядят заполняемые документы примерно так: (щелкните на картинке для увеличения)
Наша задача — настроить этот шаблон так, чтобы программа заполнения документов подставляла в него текстовые данные,
а шаблон сам, при помощи формул, распределял вставленный текст по отдельным ячейкам.
Самый простой, на мой взгляд, способ добиться желаемого, — подставлять данные в «невидимые» ячейки,
а потом, при помощи формулы =ПСТР(), подтягивать отдельные буквы нужные ячейки.
(для ручной вставки текста в подобные бланки есть специальный макрос, но в нашем случае он не подойдёт)
Сделать ячейку «невидимой» — очень просто. Для этого достаточно назначить шрифту ячейки белый цвет.
А формулы будут иметь такой вид:
=ПСТР(АдресНевидимойЯчейки;1;1)
=ПСТР(АдресНевидимойЯчейки;2;1)
=ПСТР(АдресНевидимойЯчейки;3;1)
и т.д.
На следующем скриншоте показано, как это сделано: (щелкните на картинке для увеличения)
Теперь осталось выбрать место для «невидимой» ячейки, вписать в неё код поля (например, {улица}),
и проставить формулы во все ячейки-клеточки.
Делать это вручную — достаточно долго, да и легко ошибиться при вводе формул.
Для автоматизации настройки подобных шаблонов, в программе заполнения документов имеется специальная опция (см. скриншот)
Включив опцию «Вставлять формулы для разбивки текста по буквам»,
вам станет доступен макрос, который при нажатии Ctrl + Shift + V
- возьмёт текст из буфера обмена Windows (предполагается, что перед этим вы скопировали код поля в исходной таблице)
- вставит этот текст в ПЕРВУЮ из выделенных ячеек, назначив этой ячейке белый цвет шрифта
- пропишет формулы =ПСТР() с нужными параметрами, СО ВТОРОЙ ДО ПОСЛЕДНЕЙ выделенной ячейки
Как это работает — можно посмотреть в видеоинструкции:
<видео будет добавлено чуть позже>
- 24513 просмотров
|
Вставка по одному символу в ячейку с автопереходом |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
- Обычной функцией
- Как протянуть алфавит формулой
- Создать алфавит в Excel формулами массива
- Способ №1
- Способ №2
- Применение функций создания алфавита в других задачах в Excel
Часто возникает задача быстро вывести все буквы алфавита и разместить их на разных строках или в разных столбцах по порядку. A, B, C… или А, Б, В… в зависимости от нужного алфавита.
Получить список чисел какой угодно длины очень просто. И всем известно, что есть даже несколько способов:
- Заполнением двух ячеек вручную и протягиванием курсором
- Опцией “Автозаполнение”
- Формулой, ссылающейся на предыдущую ячейку с нужным шагом (например, A1+1)
В случае с буквами алфавита автозаполнение не работает. Протянуть алфавит без формулы тоже не получится.
Но можно создать алфавит функциями, и есть даже несколько вариантов.
Обычной функцией
Наиболее простой вариант решить задачу – функция СИМВОЛ.
Она возвращает по коду ANSI знак, который ему соответствует.
Для английских заглавных букв это диапазон с 65 по 90. Остается только создать столбец таких чисел и обратиться к нему этой функцией. Сделаем диапазон чисел протягиванием, введем функцию с относительной адресацией один раз и тоже протянем ее вдоль чисел:
Буквы кириллицы можно создать аналогичным способом, разница только в диапазоне чисел. Для русского алфавита зарезервированы числа от 192 до 255. Подробнее можно почитать в статье про функцию СИМВОЛ в Excel.
Как протянуть алфавит формулой
Можно ли без создания столбца прибавить к букве число и получить следующую? Да, такая формула тоже возможна.
Чтобы получить одну букву из другой математически (вычитанием для предыдущей и сложением для следующей), поможет комбинация функций СИМВОЛ и КОДСИМВ. КОДСИМВ производит обратную операцию — возвращает код символа из самого символа.
А вот текстовое представление формулы:
=СИМВОЛ(КОДСИМВ(A1)+1) =СИМВОЛ(КОДСИМВ(A1)-1)
Как видно из примера выше, для создания алфавита потребовался отдельный столбец чисел.
А можно ли обойтись без необходимости создания такого диапазона на листе? Да, можно, именно в этом кроется прелесть формул массива. Они могут создать промежуточные массивы значений для вычислений.
Еще одно их преимущество – достаточно ввести формулу 1 раз, предварительно выделив область, в которую будет выведен массив, и он будет заполнен разом и полностью.
Важно: фигурные скобки в формулах массива не нужно вводить с клавиатуры, они появляются автоматически при вводе функций сочетанием клавиш Ctrl+Shift+Enter.
Способ №1
Чтобы создать массив чисел, воспользуемся функцией СТРОКА, которая будет обращаться к диапазону строк с нужными нам адресами и возвращать массив чисел, который будет брать на вход уже функция СИМВОЛ.
Такая формула массива потребуется, чтобы создать диапазон из 26 английских букв:
{=СИМВОЛ(СТРОКА(A65:A90))}
Если диапазон создается надолго и есть вероятность, что реальный диапазон A65:A90 может быть иначе сортирован или в него будут вставлены строки, такой способ не надежен.
Более надежно будет создать его функцией ДВССЫЛ:
{=СИМВОЛ(СТРОКА(ДВССЫЛ("65:90")))}
Аналогичные формулы для создания русского алфавита:
{=СИМВОЛ(СТРОКА(A192:A223))}
{=СИМВОЛ(СТРОКА(ДВССЫЛ("192:223")))}
Обратите внимание, буква Ё не входит в сплошной диапазон символов таблицы ANSI. Её придется ввести позже самостоятельно.
Способ №2
Еще один способ получить символы английского алфавита – получить их из адресов ячеек первых 26 столбцов.
Помогут в этом функция АДРЕС, которая выведет адреса ячеек текстом по их координатам, и функция ЛЕВСИМВ, которая извлечет из них первую букву.
Если нужно получить буквы в горизонтальном порядке, формула будет такой:
{=ЛЕВСИМВ(АДРЕС(1;СТОЛБЕЦ(A1:Z1);4;1);1)}
А так будет выглядеть процесс, если скопировать формулу выше в буфер без фигурных скобок и применить как формулу массива сочетанием клавиш:
А если нужно в вертикальном, формула выше обрабатывается дополнительно функцией ТРАНСП:
{=ТРАНСП(ЛЕВСИМВ(АДРЕС(1;СТОЛБЕЦ(A1:Z1);4;1);1))}
Процесс создания аналогичен изображенному выше. Отличие в том, что перед вводом функции массива нужно выделить вертикальный, а не горизонтальный, диапазон из 26 ячеек.
Применение функций создания алфавита в других задачах в Excel
Функции массива могут пригодиться не только для банального создания списка букв алфавита. Сами эти буквы могут быть нужны в других задачах. Примеры таких – по ссылкам ниже:
Найти английские буквы в русском тексте
Найти первую букву в ячейке
Следующая статья по формулам массива:
Учимся формулам массива – 2/4
Подсчет количества символов в диапазоне Excel
Часто сталкиваетесь с этой или похожими задачами при работе в Excel?
!SEMTools поможет решить их быстро и легко!
Функция автозавершения Excel позволяет легко вводить один и тот же текст в несколько ячеек. Используя автозавершение, введите несколько первых букв вашей записи в ячейку, и Excel автоматически завершит ее на основе других записей, которые вы уже сделали в столбце. В дополнение к уменьшению количества текста, который необходимо набирать, эта функция гарантирует, что ваши записи будут внесены правильно и согласованно.
Рассмотрим, как это работает. Предположим, вы вводите информацию о продукте в столбец. Один продукт называется «сахар-песок». Первый раз, когда вы вводите Сахар-песок в ячейку, Excel запоминает эту запись. Позже, когда вы начнете набирать слово Сахар в этом столбце, Excel распознает слово по нескольким первым буквам и завершит его ввод за вас. Просто нажмите Enter, и все готово.
Автозавершение также изменяет регистр букв автоматически. Если начать ввод со слова сахар (со строчной буквой «с») во второй записи, Excel сделает букву «с» прописной в соответствии с предыдущей записью в столбце.
Если столбец содержит несколько записей, которые соответствуют первым нескольким символам, Excel не отобразит предложение по автоматическому завершению, пока новая запись не будет соответствовать одной из старых однозначным образом. Например, если столбец также содержит продукт под названием «сахар-рафинад», автозавершение не будет реализовано, пока вы не введете либо первую букву слова «песок», либо первую букву слова «рафинад».
Вы также можете получить доступ к ориентированной на использование мыши версии автозавершения, щелкнув правой кнопкой мыши на ячейке и выбрав в контекстном меню пункт Выбрать из раскрывающегося списка. Excel отобразит список, в котором содержатся все записи в текущем столбце. Остается просто выбрать ту, которую вы хотите (рис. 32.1).
Вы даже можете получить доступ к списку с клавиатуры: нажмите Shift+F10 для отображения контекстного меню, а затем нажмите клавишу К (горячую клавишу для команды Выбрать из раскрывающегося списка). Используйте кнопки со стрелками, чтобы сделать свой выбор, и нажмите Enter.
Имейте в виду, что функция автозавершения работает только в пределах смежных столбцов ячеек. Если у вас есть, например, пустая строка, автозавершение будет анализировать только содержимое ячеек под пустой строкой. Если функция автозавершения покажется вам отвлекающей, ее можно отключить в разделе Дополнительно диалогового окна Параметры Excel (выберите Файл ► Параметры). Флажок Автозавершение значений ячеек находится в области Параметры правки.
Эксперименты делают из нас героев.
(Флэш)
Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное «вау!» аудитории — независимо от продвинутости и/или усталости слушателей.
Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете набирать рядом в соседнем столбце их же, но в каком-либо нужном вам измененном виде, то Excel рано или поздно намекнёт, что готов продолжить дальше за вас:
Чтобы выявить логику (шаблон, pattern) преоборазования и запустить эту функцию Excel обычно хватает ввода 1-3 первых результирующих значений вручную. Если предложенный вариант вам подходит, то достаточно нажать Enter — и остаток списка будет доделан моментально.
Если вы уже ввели 2-3 первых значения, а продолжение всё не появляется, то можно форсировать процесс сочетанием клавиш Ctrl+E или использовать кнопку Мгновенное заполнение (Flash Fill) на вкладке Данные (Data):
Давайте рассмотрим несколько примеров использования этого инструмента на практике, чтобы понять его возможности.
Извлечение слов из текста и перестановки
Написать формулу, которая извлекает, например, третье слово из текста в ячейке — маленький подвиг. Разобрать фразу по пробелу в разные колонки с помощью Данные — Текст по столбцам (Data — Text to Columns) тоже дело не быстрое. С помощью мгновенного заполнения это делается легко и красиво. Причем, можно попутно менять извлекаемые слова местами, комбинируя их в любом порядке:
Деление текста по регистру
Для выделения слов мгновенному заполнению совершенно не обязательно наличие именно пробела. Отлично сгодится и любой другой разделитель, например, запятая или точка-с-запятой после импорта CSV-файла. Но что совсем круто, так это то, что разделителя может и не быть совсем — достаточно только заглавных букв:
Реализовать подобное формулами очень тяжко. Если без мгновенного заполнения, то поможет только макрос.
Склейка текста
Если можно делить, то можно и клеить! Мгновенное заполнение легко соберёт для вас длинную фразу из нескольких фрагментов, перемежая их нужными пробелами, запятыми, союзами или словами:
Извлечение отдельных символов
Обычно для вытаскивания отдельных символов и подстрок в Excel используются функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID) и им подобные, но мгновенное заполнение с легкостью решает и эту задачу. Классический пример — формирование ФИО:
Извлечение только чисел, текста или дат
Если вы когда-нибудь пытались вытащить только нужный тип данных из буквенно-цифровой каши, то должны понимать всю сложность этой простой, на первый взгляд, задачи. Мгновенное заполнение и тут справляется «на ура», но нужен лёгкий пендель в виде Ctrl+E:
С извлечением текста тоже самое:
Даты — тоже не проблема (даже если они написаны в разных форматах):
Преобразование форматов чисел или дат
Мгновенное заполнение поможет изменить внешний вид имеющихся данных или привести их к «одному знаменателю». Например, обычную дату преобразовать «шиворот-навыворот» в Unix-формат:
Здесь нюанс в том, что перед вводом нужно заранее поменять формат итоговых ячеек на текстовый, чтобы Excel не пытался распознать введенные вручную в качестве образца «неправильные» даты.
Аналогичным образом можно также правильно представить телефонные номера, добавив код страны и трехзначный префикс оператора (города) в скобках:
Не забудьте сначала поменять формат ячеек в столбце В на текстовый — иначе Excel будет воспринимать значения начинающиеся со знака «+» как формулы.
Преобразование текста (чисел) в дату
При выгрузках из различных ERP и CRM-систем часто дата представляется 8-значным числом в формате ГГГГММДД. Конвертировать её в нормальный вид можно либо функцией ДАТАЗНАЧ (DATEVALUE), либо гораздо проще — мгновенным заполнением:
Изменение регистра
Если вам достался текст с нЕпрАвИльНЫм рЕгисТроМ, то можно просто намекнуть в соседнем столбце к какому виду вы хотите его преобразовать — и мгновенное заполнение сделает за вас всю работу:
Чуть сложнее будет, если нужно изменить регистр по разному у разных частей текста. Например, сделать заглавными только второе слово, оставив первое в нормальном виде. Здесь двух введённых в качестве образца значений окажется недостаточно и придется внести правки, которые мгновенное заполнение тут же учтёт в результатах:
Ограничения и нюансы
При использовании в работе мгновенного заполнения следует помнить несколько моментов:
- Оно срабатывает только, если вводить образцы строго рядом — в предыдущем или следующем столбце справа от данных. Если отступить одну пустую колонку от исходного текста, то ничего не получится.
- При выявлении шаблона учитываются все значения в строке — слева и справа от вводимого столбца. Мораль: лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить.
- Мгновенное заполнение отлично работает в умных таблицах.
- Малейшая ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет выявить паттерн и не сработает. Будьте внимательны.
- Есть ситуации, когда шаблон определяется некорректно, поэтому всегда нужно проверять результаты, которые вы получили (хотя бы выборочно).
Ссылки по теме
- Как извлечь последнее слово из текста в ячейке
- Нечёткий текстовый поиск (Пушкин = Пушкен) с помощью Fuzzy Lookup в Excel
- Три способа склеить текст в Excel
You can populate numbers, names of months or weekdays easily by dragging your mouse. However, you can’t populate letters in the same fashion. In this guide, we’re going to show you how to populate Alphabet letters in Excel using three different methods.
Introduction
Let us first take a look at how character sets work in Excel. There are two built-in functions which are related to converting numbers into characters:
- CHAR Function
- CODE Function
We can use these functions to convert numbers and letters from one another to populate letters in Excel.
Simpler Method
Uppercase and lowercase letters in ASCII character set reside between the numbers 65-90 and 97-122, respectively. 65 = A, 66 = B, 90 = Z, 106 = j, etc. Using character conversion formulas like CHAR, we can convert this numbers to corresponding letters.
Syntax
Example
The challenge for this approach is generating numbers for the CHAR function. As explained in the previous section, you need numbers between 65 and 90 or 97 and 122 for letters. For this, you can use the SEQUENCE function at this step. The SEQUENCE function can generate an array of sequential numbers automatically.
All you need to do is to supply how many numbers you need, and which number to start with.
Syntax
SEQUENCE(rows,[columns],[start],[step])
Example
=SEQUENCE(26,1,65) returns an array of numbers between 65 and 90
The function above returns numbers into 26 rows and single column. If you want to generate numbers or letters like in our example, you can use a formula like =SEQUENCE(1,26,65).
Let’s combine both functions to create formula for populating letters of the alphabet.
Syntax
=CHAR(SEQUENCE(26,1,<number where letters start>))
Example
Uppercase letters: =CHAR(SEQUENCE(26,1,65))Lowercase letters: =CHAR(SEQUENCE(26,1,97))
Using the CODE function for populating letters of the alphabet
Excel’s has the CODE function works in opposite way of the CHAR. The CODE function simply returns the corresponding number of the given character in a character set.
Syntax
Example
As a result, the CODE function with the character can be replaced with the number itself. The following is an example for using this formula.
Syntax
=CHAR(SEQUENCE(CODE(<last character>)-CODE(<first character> )+1,1,CODE(<first character>)))
Example
=CHAR(SEQUENCE(CODE(«Z»)-CODE(«A»)+1,1,CODE(«A»)))
Dynamic character interval
Using the CODE function, we can improve the model by following a more dynamic approach. We can replace the hard coded letters like “A” or “Z” with cell references containing these characters. This approach allows us to change the start and end characters of alphabet letters without updating the formula.
Syntax
=CHAR(SEQUENCE(CODE(<the cell contains the last character>)-CODE(<the cell contains the first character> )+1,1,CODE(<the cell contains the first character>)))
Example
=CHAR(SEQUENCE(CODE(I7)-CODE(I6)+1,1,CODE(I6)))
Using LET function to populate Alphabet letters
This is an optional step for populating alphabet letters in Excel. The relatively new LET function allows you to create named ranges inside the context of its own formula.
The syntax of the LET function contains the actual formula which using determined name-value pairs.
Syntax of the LET function
LET(name1, name_value1, [name2], [name_value2], …, calculation)
Syntax of our model with the LET function
=LET(Start,CODE(<the cell contains the first character>),Finish,CODE(<the cell contains the last character>),CHAR(SEQUENCE(Start-Finish+1,1,Finish)))
Example
=LET(Start,CODE(M7),Finish,CODE(M6),CHAR(SEQUENCE(Start-Finish+1,1,Finish)))
We defined named ranges for Start and Finish as CODE(M7) and CODE(M6) respectively. Each name is replaced with the corresponding CODE function block.
Microsoft Excel
трюки • приёмы • решения
Как в Excel использовать автоматическое завершение для автоматизации ввода данных
Функция автозавершения Excel позволяет легко вводить один и тот же текст в несколько ячеек. Используя автозавершение, введите несколько первых букв вашей записи в ячейку, и Excel автоматически завершит ее на основе других записей, которые вы уже сделали в столбце.
В дополнение к уменьшению количества текста, который необходимо набирать, эта функция гарантирует, что ваши записи будут внесены правильно и согласованно.
Рассмотрим, как это работает. Предположим, вы вводите информацию о продукте в столбец. Один продукт называется «сахар-песок». Первый раз, когда вы вводите Сахар-песок в ячейку, Excel запоминает эту запись. Позже, когда вы начнете набирать слово Сахар в этом столбце, Excel распознает слово по нескольким первым буквам и завершит его ввод за вас. Просто нажмите Enter, и все готово.
Автозавершение также изменяет регистр букв автоматически. Если начать ввод со слова сахар (со строчной буквой «с») во второй записи, Excel сделает букву «с» прописной в соответствии с предыдущей записью в столбце.
Если столбец содержит несколько записей, которые соответствуют первым нескольким символам, Excel не отобразит предложение по автоматическому завершению, пока новая запись не будет соответствовать одной из старых однозначным образом. Например, если столбец также содержит продукт под названием «сахар-рафинад», автозавершение не будет реализовано, пока вы не введете либо первую букву слова «песок», либо первую букву слова «рафинад».
Вы также можете получить доступ к ориентированной на использование мыши версии автозавершения, щелкнув правой кнопкой мыши на ячейке и выбрав в контекстном меню пункт Выбрать из раскрывающегося списка. Excel отобразит список, в котором содержатся все записи в текущем столбце. Остается просто выбрать ту, которую вы хотите (рис. 32.1).
Рис. 32.1. Выбор пункта контекстного меню Выбрать из раскрывающегося списка позволяет отображать список записей в столбце
Вы даже можете получить доступ к списку с клавиатуры: нажмите Shift+F10 для отображения контекстного меню, а затем нажмите клавишу К (горячую клавишу для команды Выбрать из раскрывающегося списка). Используйте кнопки со стрелками, чтобы сделать свой выбор, и нажмите Enter.
Имейте в виду, что функция автозавершения работает только в пределах смежных столбцов ячеек. Если у вас есть, например, пустая строка, автозавершение будет анализировать только содержимое ячеек под пустой строкой. Если функция автозавершения покажется вам отвлекающей, ее можно отключить в разделе Дополнительно диалогового окна Параметры Excel (выберите Файл ► Параметры). Флажок Автозавершение значений ячеек находится в области Параметры правки.
dvorkin
Личный блог и сайт обо всем на свете
7 хитростей чтобы сделать вас экспертом Excel
Программы для работы с электронными таблицами, такими как Excel, являются отличным инструментом для организации, хранения и оперирования данными. Вот некоторые приемы и парочка хитростей, которые сделают ваш ввод данных в электронную таблицу более удобным и быстрым.
1. Переход указателя к нужной ячейке во время ввода данных.
Excel автоматически перемещает указатель в следующую ячейку вниз, когда вы нажимаете Enter после ввода данных в ячейку. Это отвлекает, особенно, если вы хотите добавить данные в следующий столбец в строке, а не ниже. Чтобы изменить это поведение, используйте окно настроек Excel.
Выберите: Файл -> Параметры -> Дополнительно -> Параметры правки. Поставьте флажок «Переход к другой ячейке после нажатия клавиши ВВОД» и в соответствующем раскрывающемся списке выберите то направление, которое вам больше подходит в данный момент.
Совет: При отключении флажка, клавиши со стрелками можно использовать для управления перемещением указателя. Например, если вы вводите данные в строке, нажмите клавишу со стрелкой вправо, а не Enter, чтобы перейти к следующей ячейке в той же строке.
2. Храните данные в таблицах данных:
Таблица Excel является сеткой из строк и столбцов. При добавлении данных в Excel, он интуитивно не использует таблицы данных. Однако таблицы данных Excel являются простым и мощным инструментом. Они помогают легко добавлять больше строк и столбцов, не беспокоясь об обновлении ссылок формул, опций, настроек фильтров и т.д. Таблицы данных сами позаботятся о форматировании.
Для добавления таблицы данных Excel, выберите диапазон ячеек, содержащих данные, и нажмите на кнопку Таблица, находящуюся во вкладке «Вставка» на ленте или нажмите сочетание клавиш «CTRL + T».
Несколько хитростей таблиц данных:
- Таблицы данных вставляются со своим собственным набором фильтров данных и параметром сортировки по умолчанию. Без таблиц данных Excel допускает только один набор фильтров на листе. Однако если рабочий лист имеет более одной таблицы, то каждая таблица может иметь свои собственные фильтры и параметры сортировки.
- Формулы внутри таблицы данных могут использовать осмысленные имена заголовков вместо использования ссылок на ячейки. Кроме того, при добавлении или удалении строк не нужно беспокоиться об обновлении ссылок.
- С помощью структурированных ссылок, вычисления внутри столбцов становятся очень простыми. Кроме того, Excel автоматически заполняет формулу внутри остальной части ячеек в столбце.
- Таблицы данных могут быть отформатированы одним кликом мыши по пункту «Стили» во вкладке «Конструктор».
- Повторяющиеся данные могут быть удалены из таблиц данных одним кликом мыши по пункту «Удалить дубликаты» во вкладке «Конструктор» на ленте.
- Таблицы данных могут быть преобразованы обратно в диапазон одним кликом мыши по кнопке «Преобразовать в диапазон» во вкладке «Конструктор».
- Таблицы данных могут быть экспортированы в список SharePoint так же одним кликом мыши по кнопке «Экспорт» во вкладке «Конструктор».
- Таблицы данных можно суммировать, добавив итоговую строку, поставив флажок в чек-боксе «Строка итогов» во вкладке «Конструктор».
Ввод данных с помощью форм
После того, как Таблицы данных созданы на рабочем листе, формы обеспечивают возможность ввода данных в этих таблицах, используя специальное диалоговое окно. Для добавления кнопки формы на панели быстрого доступа, выполните следующие действия:
Нажмите на кнопку Настройка панели быстрого доступа
Выберите из списка пункт Другие команды. Выберите Все команды из выпадающего списка. Чуть ниже найдите Форма в большом списке и выделите его
Нажмите кнопку Добавить, и этот пункт должен добавиться в панель быстрого доступа после нажатия на кнопку ОК
Для того, чтобы получить доступ к диалоговому окну форм, просто выберите таблицу данных на рабочем листе и нажмите на добавленную кнопку из панели быстрого доступа.
В этом диалоговом окне есть следующие кнопки:
- Добавить: Добавляет новую запись в таблицу данных
- Удалить: Удаляет отображаемую запись из таблицы данных
- Вернуть: Восстановление изменений, внесенных в таблицу данных
- Назад / Далее: Находит записи, указанные в поле Критерии
- Критерии: Получение данных, которые удовлетворяют критериям набора
- Закрыть: Закрывает диалоговое окно
4. Проверка данных с помощью раскрывающегося списка:
Создание раскрывающегося списка и обеспечение ввода данных с его использованием гарантирует, что целостность данных всегда будет в порядке. Для создания раскрывающегося списка:
- Введите список элементов в диапазоне.
- Выделите ячейку, которая будет содержать выпадающий список
- Выберите вкладку Данные на ленте -> Работа с данными -> Проверка данных.
- В диалоговом окне Проверка вводимых значений, перейдите на вкладку Настройки.
- В раскрывающемся списке Тип данных, выберите Список.
- В поле Источник укажите диапазон, содержащий нужные вам элементы.
Совет: Если в раскрывающемся списке мало данных, то вы можете ввести значения непосредственно в поле Источник, разделенные запятыми.
5. Автозамены для ввода данных:
Автозамена по умолчанию подразумевает такие полезные вещи, как написание предложений с заглавной буквы, или исправление случайного нажатия Caps Lock. Кроме того, можно настроить Автозамену создавая сокращения для часто используемых слов или фраз. Например, ниже я использую аббревиатуру: «гос» для «государство».
Для того чтобы добраться до Параметров автозамены, нажмите на Файл -> Параметры -> Правописание -> Параметры автозамены. В качестве альтернативы можно воспользоваться сочетанием клавиш: ALT + T + Т.
Автозамена несколько хитростей:
- Чтобы добавить новую автозамену, введите заменяемый текст в поле Заменять и введите замещающий текст, который будет в поле На, а затем нажмите кнопку Добавить.
- Чтобы удалить автозамену найдите ее в списке автозамен и нажмите кнопку Удалить.
- Чтобы отменить сработавшую автозамену, нажмите CTRL + Z при вводе информации в ячейку.
- Для совместного использования автозамен с вашими друзьями или коллегами, просто найдите файл *.acl на своем компьютере и скопируйте его на компьютер друга или коллеги.
- Excel преобразует адрес электронной почты или записи веб URL в гиперссылки с использованием автозамены. Чтобы изменить автоматическое преобразование текста в гиперссылку, просто нажмите кнопку Отменить (или нажмите Ctrl + Z) после того, как вы вводите текст. Гиперссылка исчезнет, но текст, который вы ввели останется неизменным. Чтобы полностью отключить эту функцию, снова перейдите в раздел Параметры автозамены -> Нажмите на вкладку: Автоформат при вводе -> уберите флажок: «адреса Интернета и сетевые пути гиперссылками».
6. Автозаполнения для автоматизации ввода данных:
Функция автозаполнения в Excel позволяет легко вводить один и тот же текст в несколько ячеек. Просто введите первые несколько букв текста в ячейку и Excel автоматически завершит запись, основываясь на других записях, которые вы уже сделали в этом же столбце. Это помогает не только уменьшить процесс набора текста, но и согласует введение данных.
Автозаполнение несколько хитростей:
- Автозаполнение работает только на смежной ячейке, поэтому не оставляйте пустых строк, чтобы в полной мере использовать автозаполнение
- Автозаполнение изменяет регистр букв автоматически
- Если столбец содержит несколько вхождений записи, которые соответствуют первым нескольким вводимых вами символам, то автозаполнение не сработает пока ваша запись не станет соответствовать хотя бы одному из них полностью.
- Вы можете получить доступ к автозаполнению, кликнув правой кнопкой мыши по ячейке и выбрав пункт «Выбрать из раскрывающегося списка». Также вы можете получить доступ к этому же раскрывающемуся списку с помощью горячей клавиши: Shift + F10.
7. Аудиокоррекция (работает, к сожалению, только с английским текстом и числами)
Excel имеет удобный инструмент для преобразования текста в речь, который способен считывать содержимое ячейки по мере их ввода. Кроме того, он также может считывать определенный диапазон ячеек.
Чтобы добавить эту функцию на панель инструментов быстрого доступа, нажмите на кнопку Настройка панели быстрого доступа, выберите пункт Другие команды из списка, выберите Все команды из выпадающего списка. Добавьте все команды, начинающиеся с «Проговаривать», нажав на Добавить.
Чтобы прочесть диапазон ячеек, выберите нужный диапазон ячеек и нажмите на кнопку Проговорить ячейки. Вы можете изменить порядок чтения, нажав на «Проговаривать ячейки по строкам» или «Проговаривать ячейки по столбцам». Если вы хотите чтобы текст проговаривался по мере ввода, нажмите на кнопку «Проговаривать ячейки после ввода».
Автозаполнение ячеек в Excel из другой таблицы данных
На одном из листов рабочей книги Excel, находиться база информации регистрационных данных служебных автомобилей. На втором листе ведется регистр делегации, где вводятся личные данные сотрудников и автомобилей. Один из автомобилей многократно используют сотрудники и каждый раз вводит данные в реестр – это требует лишних временных затрат для оператора. Лучше автоматизировать этот процесс. Для этого нужно создать такую формулу, которая будет автоматически подтягивать информацию об служебном автомобиле из базы данных.
Автозаполнение ячеек данными в Excel
Для наглядности примера схематически отобразим базу регистрационных данных:
Как описано выше регистр находится на отдельном листе Excel и выглядит следующим образом:
Здесь мы реализуем автозаполнение таблицы Excel. Поэтому обратите внимание, что названия заголовков столбцов в обеих таблицах одинаковые, только перетасованы в разном порядке!
Теперь рассмотрим, что нужно сделать чтобы после ввода регистрационного номера в регистр как значение для ячейки столбца A, остальные столбцы автоматически заполнились соответствующими значениями.
Как сделать автозаполнение ячеек в Excel:
- На листе «Регистр» введите в ячейку A2 любой регистрационный номер из столбца E на листе «База данных».
- Теперь в ячейку B2 на листе «Регистр» введите формулу автозаполнения ячеек в Excel:
- Скопируйте эту формулу во все остальные ячейки второй строки для столбцов C, D, E на листе «Регистр».
В результате таблица автоматически заполнилась соответствующими значениями ячеек.
Принцип действия формулы для автозаполнения ячеек
Главную роль в данной формуле играет функция ИНДЕКС. Ее первый аргумент определяет исходную таблицу, находящуюся в базе данных автомобилей. Второй аргумент – это номер строки, который вычисляется с помощью функции ПОИСПОЗ. Данная функция выполняет поиск в диапазоне E2:E9 (в данном случаи по вертикали) с целью определить позицию (в данном случаи номер строки) в таблице на листе «База данных» для ячейки, которая содержит тоже значение, что введено на листе «Регистр» в A2.
Третий аргумент для функции ИНДЕКС – номер столбца. Он так же вычисляется формулой ПОИСКПОЗ с уже другими ее аргументами. Теперь функция ПОИСКПОЗ должна возвращать номер столбца таблицы с листа «База данных», который содержит название заголовка, соответствующего исходному заголовку столбца листа «Регистр». Он указывается ссылкой в первом аргументе функции ПОИСКПОЗ – B$1. Поэтому на этот раз выполняется поиск значения только по первой строке A$1:E$1 (на этот раз по горизонтали) базы регистрационных данных автомобилей. Определяется номер позиции исходного значения (на этот раз номер столбца исходной таблицы) и возвращается в качестве номера столбца для третьего аргумента функции ИНДЕКС.
Благодаря этому формула будет работать даже если порядок столбцов будет перетасован в таблице регистра и базы данных. Естественно формула не будет работать если не будут совпадать названия столбцов в обеих таблицах, по понятным причинам.
Автозаполнение ячеек в Excel
Автозаполнение ячеек Excel – это автоматический ввод серии данных в некоторый диапазон. Введем в ячейку «Понедельник», затем удерживая левой кнопкой мышки маркер автозаполнения (квадратик в правом нижнем углу), тянем вниз (или в другую сторону). Результатом будет список из дней недели. Можно использовать краткую форму типа Пн, Вт, Ср и т.д. Эксель поймет. Аналогичным образом создается список из названий месяцев.
Автоматическое заполнение ячеек также используют для продления последовательности чисел c заданным шагом (арифметическая прогрессия). Чтобы сделать список нечетных чисел, нужно в двух ячейках указать 1 и 3, затем выделить обе ячейки и протянуть вниз.
Эксель также умеет распознать числа среди текста. Так, легко создать перечень кварталов. Введем в ячейку «1 квартал» и протянем вниз.
На этом познания об автозаполнении у большинства пользователей Эксель заканчиваются. Но это далеко не все, и далее будут рассмотрены другие эффективные и интересные приемы.
Автозаполнение в Excel из списка данных
Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.
В следующем открывшемся окне видны те списки, которые существуют по умолчанию.
Как видно, их не много. Но легко добавить свой собственный. Можно воспользоваться окном справа, где либо через запятую, либо столбцом перечислить нужную последовательность. Однако быстрее будет импортировать, особенно, если данных много. Для этого предварительно где-нибудь на листе Excel создаем перечень названий, затем делаем на него ссылку и нажимаем Импорт.
Жмем ОК. Список создан, можно изпользовать для автозаполнения.
Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.
В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).
Посередине выбирается нужный тип:
- арифметическая прогрессия – каждое последующее значение изменяется на число, указанное в поле Шаг
- геометрическая прогрессия – каждое последующее значение умножается на число, указанное в поле Шаг
- даты – создает последовательность дат. При выборе этого типа активируются переключатели правее, где можно выбрать тип единицы измерения. Есть 4 варианта:
-
-
- день – перечень календарных дат (с указанным ниже шагом)
- рабочий день – последовательность рабочих дней (пропускаются выходные)
- месяц – меняются только месяцы (число фиксируется, как в первой ячейке)
- год – меняются только годы
-
- автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).
Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.
Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.
Автозаполнение чисел с помощью мыши
Автозаполнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Понадобятся обе.
Допустим, нужно сделать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а далее левой кнопкой мыши протягивают арифметическую прогрессию. Можно сделать по-другому. Заполняем только одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Далее открываем квадратик, который появляется сразу после протягивания в правом нижнем углу и выбираем Заполнить.
Если выбрать Заполнить только форматы, будут продлены только форматы ячеек.
Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl.
Этот трюк работает только с последовательностью чисел. В других ситуациях удерживание Ctrl приводит к копированию данных вместо автозаполнения.
Если при протягивании использовать правую кнопку мыши, то контекстное меню открывается сразу после отпускания кнопки.
При этом добавляются несколько команд. Прогрессия позволяет использовать дополнительные операции автозаполнения (настройки см. выше). Правда, диапазон получается выделенным и длина последовательности будет ограничена последней ячейкой.
Чтобы произвести автозаполнение до необходимого предельного значения (числа или даты), можно проделать следующий трюк. Берем правой кнопкой мыши за маркер чуть оттягиваем вниз, сразу возвращаем назад и отпускаем кнопку – открывается контекстное меню автозаполнения. Выбираем прогрессию. На этот раз выделена только одна ячейка, поэтому указываем направление, шаг, предельное значение и создаем нужную последовательность.
Очень интересными являются пункты меню Линейное и Экспоненциальное приближение. Это экстраполяция, т.е. прогнозирование, данных по указанной модели (линейной или экспоненциальной). Обычно для прогноза используют специальные функции Excel или предварительно рассчитывают уравнение тренда (регрессии), в которое подставляют значения независимой переменной для будущих периодов и таким образом рассчитывают прогнозное значение. Делается примерно так. Допустим, есть динамика показателя с равномерным ростом.
Для прогнозирования подойдет линейный тренд. Расчет параметров уравнения можно осуществить с помощью функций Excel, но часто для наглядности используют диаграмму с настройками отображения линии тренда, уравнения и прогнозных значений.
Чтобы получить прогноз в числовом выражении, нужно произвести расчет на основе полученного уравнения регрессии (либо напрямую обратиться к формулам Excel). Таким образом, получается довольно много действий, требующих при этом хорошего понимания.
Так вот прогноз по методу линейной регрессии можно сделать вообще без формул и без графиков, используя только автозаполнение ячеек в экселе. Для этого выделяем данные, по которым строится прогноз, протягиваем правой кнопкой мыши на нужное количество ячеек, соответствующее длине прогноза, и выбираем Линейное приближение. Получаем прогноз. Без шума, пыли, формул и диаграмм.
Если данные имеют ускоряющийся рост (как счет на депозите), то можно использовать экспоненциальную модель. Вновь, чтобы не мучиться с вычислениями, можно воспользоваться автозаполнением, выбрав Экспоненциальное приближение.
Более быстрого способа прогнозирования, пожалуй, не придумаешь.
Автозаполнение дат с помощью мыши
Довольно часто требуется продлить список дат. Берем дату и тащим левой кнопкой мыши. Открываем квадратик и выбираем способ заполнения.
По рабочим дням – отличный вариант для бухгалтеров, HR и других специалистов, кто имеет дело с составлением различных планов. А вот другой пример. Допустим, платежи по графику наступают 15-го числа и в последний день каждого месяца. Укажем первые две даты, протянем вниз и заполним по месяцам (любой кнопкой мыши).
Обратите внимание, что 15-е число фиксируется, а последний день месяца меняется, чтобы всегда оставаться последним.
Используя правую кнопку мыши, можно воспользоваться настройками прогрессии. Например, сделать список рабочих дней до конца года. В перечне команд через правую кнопку есть еще Мгновенное заполнение. Эта функция появилась в Excel 2013. Используется для заполнения ячеек по образцу. Но об этом уже была статья, рекомендую ознакомиться. Также поможет сэкономить не один час работы.
На этом, пожалуй, все. В видеоуроке показано, как сделать автозаполнение ячеек в Excel.