Присвоение имени ячейкам Excel
Для выполнения некоторых операций в Экселе требуется отдельно идентифицировать определенные ячейки или диапазоны. Это можно сделать путем присвоения названия. Таким образом, при его указании программа будет понимать, что речь идет о конкретной области на листе. Давайте выясним, какими способами можно выполнить данную процедуру в Excel.
Присвоить наименование массиву или отдельной ячейке можно несколькими способами, как с помощью инструментов на ленте, так и используя контекстное меню. Оно должно соответствовать целому ряду требований:
- начинаться с буквы, с подчеркивания или со слеша, а не с цифры или другого символа;
- не содержать пробелов (вместо них можно использовать нижнее подчеркивание);
- не являться одновременно адресом ячейки или диапазона (то есть, названия типа «A1:B2» исключаются);
- иметь длину до 255 символов включительно;
- являться уникальным в данном документе (одни и те же буквы, написанные в верхнем и нижнем регистре, считаются идентичными).
Как из фио сделать имя отчество. Как разделить в экселе фио. Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы. Примеры работы с ФИО в Excel
Если вам, например, постоянно присылают данные в определенном виде, а вам постоянно необходимо их делить, то это занимает определенное время, кроме того, если данные вам прислали заново, то вам снова нужно будет проделать все операции.
Приступаем к делению первой части текста — Фамилии
Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:
- текст — это ФИО, в нашем примере это ячейка A2;
- начальная_позиция — в нашем случае это 1, то есть начиная с первой буквы;
- число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.
Создание и использование имен в MS Excel. Как присвоить имя значению Excel
- текст — это тот же текст ФИО, в нашем примере это ячейка A2;
- начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1;
- — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50
Собственно такой вопрос поступил в комментариях, поэтому было решено дополнить эту статью. Для удобства в соседнем столбце укажем этот разделитель, чтобы не прописывать его в формуле а просто ссылаться на ячейку. Это так же позволит нам использовать файл для решения других задач, просто поменяв разделитель в ячейках.
Как разделить текст по столбцам в экселе
Выделяем диапазон ячеек, на ленте нажимаем Данные-Текст по столбцам, появится Мастер текстов.
Ничего не меняем, если у вас переключатель стоит в положении: с разделителем, жмем далее.
и в окошко вносим точку с запятой [ ; ]. Жмем либо далее, хотя там особо смотреть не чего, верней нажать готово. Будет задан вопрос: заменить содержимое ячеек? Ответ да!
Мы смогли разбить ячейку на столько значений, сколько их было между [ ; ]. Добавим пустые строки под ячейкой с датой, в количестве, ячеек правей столбца С.
и под первой ячейкой жмем правой кнопкой «Специальная вставка»- «Транспонировать».
Данные перенесутся из строки в столбец, проделаем все то же самое для остальных ячеек.
Получилось разделить текст в ячейке, оформить по дням, в столбик.
Как сделать фамилию и инициалы в excel?
Мало, кто обращается к кнопке Параметры в диалоговом окне Найти и заменить. А зря. В ней скрыто много полезностей, которые помогают решить проблемы поиска. После нажатия кнопки Параметры добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.
Пример 2. Как разделить текст по столбцам в Excel с помощью формулы
Рассмотрим второй пример, который так же очень часто встречался на практике. Пример похож предыдущий, но данных которые нужно разделить значительно больше. В этом примере я покажу прием, который позволит достаточно быстро решить вопрос и не запутаться.
Допустим у нас есть список чисел, перечисленных через запятую, нам необходимо разбить текст таким образом, чтобы каждое число было в отдельной ячейке (вместо запятых это могут быть любые другие знаки, в том числе и пробелы). То есть нам необходимо разбить текст по словам.
Напомним, что вручную (без формул) это задача очень просто решается с помощью , который мы уже рассматривали. В нашем же случае требуется это сделать с помощью формул.
Для удобства в соседнем столбце укажем этот разделитель, чтобы не прописывать его в формуле а просто ссылаться на ячейку. Это так же позволит нам использовать файл для решения других задач, просто поменяв разделитель в ячейках.
Шаг 1 . В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки
Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.
Шаг 3. Находимо позиции всех остальных разделителей. Для этого формулу нахождения второго разделителя (шаг 2) протянем вправо на то количество ячеек, сколько всего может быть отдельно разбитых значений с небольшим запасом. Получим все позиции разделителей. Там где ошибка #Знач означает что значения закончились и формула больше не находит разделителей. Получаем следующее
Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.
Начальная позиция у нас 1 , количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз
Шаг 5 . Находимо второе слово так же с помощью функции ПСТР в ячейке P1
Начальная позиция второго числа у нас начинается после первой запятой. Позиция первой запятой у нас есть в ячейке D1, прибавим единицу и получим начальную позицию нашего второго числа.
Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1
Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.
Шаг 6 . Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.
Шаг 7. В принципе задача наша уже решена, но для красоты все в той же ячейке P1 пропишем формула отлавливающую ошибку заменяя ее пустым значением. Так же можно сгруппировать и свернуть вспомогательные столбцы, чтобы они не мешали. Получим итоговое решение задачи
Примечание . Первую позицию разделителя и первое деление слова мы делали отлично от других и из-за этого могли протянуть формулу только со вторых значений. Во время написания задачи я заметил, что можно было бы упростить задачу. Для этого в столбце С нужно было прописать 0 значения первого разделителя. После этого находим значение первого разделителя
После этого можно сразу протягивать формулу на остальные значения. Именно этот вариант оставляю как пример для скачивания. В принципе файлом можно пользоваться как шаблоном. В столбец «A» вставляете данные, в столбце «B» указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат.
Либо второе решение — это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.
Если вы имеете диапазон ячеек, содержащих имена и фамилии людей, часто бывает необходимо работать только с именем или только с фамилией. Например, вам может потребоваться хранение имен и фамилий отдельно для дальнейшего импортирования в базу данных, в которой хранится ваш аттестат профбухгалтера . Или вам может потребоваться отсортировать имена.
Решением является использование функции НАЙТИ() для поиска пробела, разделяющего две части имени, а затем использование функции ЛЕВСИМВ() или ПРАВСИМВ() для выделения необходимой части. Для выделения левой части (предположим, там находится фамилия) используйте следующую формулу:
=ЛЕВСИМВ(A2;НАЙТИ(«»;A2)-1)
Обратите внимание, что в формуле происходит вычитание единицы из необходимого для выделения числа символов, дабы предотвратить появление пробела. Вы можете использовать данную формулу для любого более общего примера для выделения первого слова из любой строки. Для выделения имени необходимо построить похожую формулу, используя функцию ПРАВСИМВ():
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(«»;A2))
Для выделения необходимого числа символов формула берет длину слова и вычитает из него положение пробела. Вы можете использовать данную формулу для любого более общего примера для выделения второго слова из любой строки. На рис. 4.13 показаны результаты работы.
Здесь же в столбце D вы видите результат объединения двух предыдущих формул для изменения порядка следования слов. Обратите внимание, что данные формулы выведут ошибку, в случае если строка содержит только одно слово. Для предотвращения этого используйте функцию ЕСЛИОШИБКА() :
=ЕСЛИОШИБКА(ЛЕВСИМВ<a2;НАЙТИ("";A2)-1);A2) . В таком случае, если ячейка не содержит пробела, просто будет выведено значение ячейки A2.
Например, нам дано следующее имя: Булгаков Виктор Кирсанович. Предполагая, что имя находится в ячейке A2, формула =НАЙТИ(» «;A2) , вернет 9 — позицию первого пробела. Для нахождения позиции следующего пробела необходимо указать позицию первого пробела плюс 1: =НАЙТИ(» «;A2;НАЙТИ(» «;A2)+1).
Теперь можно применить данную конструкцию для нахождения самой правой части (в данном случае это отчество): =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2)+1)) .
Для выделения центральной части необходимо определить разницу в положении первого и второго пробела и воспользоваться формулой выделения из любой части строки: =ПСТР(A2;НАЙТИ(» «;;A2)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2)+1-НАЙТИ(» «;A2)) . На рис. 4.14 показаны результаты работы.
Часто при составлении договора, счета, коммерческого предложения или другого документа, мы работаем с фамилией именем и отчеством клиента. Бывает необходимо разделить ФИО и получить только имя, что обратиться к клиенту в тексте документа, бывает нужно сделать графу с подписантом, оставив фамилию и инициалы.
Конечно можно написать формулу , которая извлечет нужные данные из ФИО, но даже опытному человеку потребуется 5-7 минут на это. А что если заранее не знаешь расположение фамилии в тексте? Гораздо проще и удобнее пользоваться готовыми функциями из надстройки VBA-Excel .
Найти фамилию в ФИО
После установки надстройки будет доступна новая функция =ФАМИЛИЯ(ФИО) , где
Задача данной функции — извлечь из ячейки только фамилию. Причем ей не важен порядок. Фамилия может идти как в начале, так в конце или середине текста.
Найти имя в ФИО
Аналогичная функция для имени =ИМЯ(ФИО) . Она извлекает имя из текста с ФИО, также не важно где расположенное.
Найти отчество в ФИО
Последняя функция из этой серии — функция =ОТЧЕСТВО(ФИО) для извлечения отчества.
Имена в формулах
- текст — это тот же текст ФИО, в нашем примере это ячейка A2 ;
- начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1 ;
- число_знаков — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50
Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке “Главная”, в группе кнопок “Редактирование”, в меню кнопки “Найти и выделить”. Если в этом меню выбрать пункт “Перейти” и далее нажать кнопку “Выделить”, то в диалоговом окне “Выделение группы ячеек” можно выбрать одну из опций “Отличия по строкам” или “Отличия по столбцам”.
Создание на листе имени для ячейки или диапазона ячеек
Выделите ячейку, диапазон ячеек или несмежные ячейки, которым вы хотите присвоить имя.
Щелкните поле Имя у левого края строка формул.
Введите имя, которое будет ссылаться на выбор. Длина имени не может превышать 255 знаков.
Примечание: При изменении содержимого ячейки ей нельзя присвоить имя.
Можно преобразовать в имена существующие заголовки строк и столбцов.
Выберите диапазон, которому вы хотите присвоить имя, включая заголовки строк и столбцов.
На вкладке Формулы в группе Определенные имена нажмите кнопку Создать из выделенного фрагмента.
В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки, установив флажок в строке выше, в столбце слева, в строке ниже или в столбце справа. Имя, созданное с помощью этой процедуры, ссылается только на ячейки, содержащие значения, и исключает существующие названия строк и столбцов.
На вкладке Формулы в группе Определенные имена выберите пункт Присвоить имя.
В диалоговом окне Новое имя в поле Имя введите имя, которое вы хотите использовать для ссылки.
Примечание. Длина имени не может быть больше 255 знаков.
Для задания области действия имени в поле со списком Область выберите элемент Книга или имя листа в книге.
Также в поле Примечание можно ввести описание длиной до 255 знаков.
В поле Диапазон выполните одно из указанных ниже действий.
Чтобы указать ссылку на ячейки, введите ссылку на ячейки.
Совет. По умолчанию в этом режиме ввели текущий выбор. Чтобы ввести другие ссылки на ячейки в качестве аргумента, нажмите кнопку Свернуть диалоговое окно (диалоговое окно будет временно сжато), выйдите ячейки на этом сайте и нажмите кнопку Развернуть .
Чтобы указать константу, введите = (знак равенства), а затем значение константы.
Чтобы указать формулу, введите =, а затем формулу.
Чтобы закончить и вернуться на лист, нажмите кнопку ОК.
Совет: Чтобы расширить или удлинить диалоговое окно Создание имени, щелкните и перетащите маркер захвата, расположенный внизу.
[expert_bq id=»1570″]В столбец A вставляете данные, в столбце B указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Обратите внимание, что в формуле происходит вычитание единицы из необходимого для выделения числа символов, дабы предотвратить появление пробела. Вы можете использовать данную формулу для любого более общего примера для выделения первого слова из любой строки. Для выделения имени необходимо построить похожую формулу, используя функцию ПРАВСИМВ():=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(«»;A2))
Как разделить в экселе фио. Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы.
Для выделения центральной части необходимо определить разницу в положении первого и второго пробела и воспользоваться формулой выделения из любой части строки: =ПСТР(A2;НАЙТИ(» «;;A2)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2)+1-НАЙТИ(» «;A2)) . На рис. 4.14 показаны результаты работы.
Функция СМЕЩ в Excel
Разберем более детально функции, которые мы вводили в поле диапазон при создании динамического имени.
Функция =СМЕЩ определяет наш диапазон в зависимости от количества заполненных ячеек в столбце B. 5 параметров функции =СМЕЩ(начальная ячейка; смещение размера диапазона по строкам; смещение по столбцам; размер диапазона в высоту; размер диапазона в ширину):
- «Начальная ячейка» – указывает верхнюю левую ячейку, от которой будет динамически расширяться диапазон как вниз, так и вправо (при необходимости).
- «Смещение по строкам» – параметр определяет, на какое количество нужно смещать диапазон по вертикали от начальной ячейки (первого параметра). Значения могут быть нулевыми и отрицательными.
- «Смещение по столбцам» – параметр определяет, на какое количество нужно смещать по горизонтали от начальной ячейки. Значения могут быть даже нулевыми и отрицательными.
- «Размер диапазона в высоту» – количество ячеек, на которое нужно увеличить диапазон в высоту. По сути, название говорит само за себя.
- «Размер диапазона в ширину» – количество ячеек, на которое нужно увеличить в ширину от начальной ячейки.
Последние 2 параметра функции являются необязательными. Если их не заполнять, то диапазон будет состоять из 1-ой ячейки. Например: =СМЕЩ(A1;0;0) – это просто ячейка A1, а параметр =СМЕЩ(A1;2;0) ссылается на A3.
Теперь разберем функцию: =СЧЕТ, которую мы указывали в 4-ом параметре функции: =СМЕЩ.
[expert_bq id=»1570″]В диалоговом окне Диспетчер имен не отображаются имена, определенные в Visual Basic для приложений VBA или скрытые имена свойство Visible имени имеет вид False. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] О том как выделять ячейки и группы ячеек уже рассказывалось в одной из наших публикаций. Также ранее рассматривалась тема о том как выделять строки в рабочих листах Excel , но строка является одним из частных видов диапазона ячеек. Рассмотрим несколько способов выделения диапазонов ячеек в общем виде.Как присвоить имя ячейке в Excel
- В нём не должно быть пробелов;
- Оно обязательно должно начинаться с буквы;
- Его длина не должна быть больше 255 символов;
- Оно не должно быть представлено координатами вида A1 или R1C1 Имя Продажи диапазону B2:B10. При создании имени будем использовать абсолютную адресацию .
Совет. В качестве определенного имени нельзя использовать буквы «C», «c», «R» и «r», поскольку они используются в качестве краткого текста для выбора строки или столбца для выбранной ячейки при их вводе в текстовое поле Имя или Перейти.
