Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как извлечь почтовый индекс из списка адресов в Excel?

Excel трюк: Достаем фамилию из ФИО

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

Пример (Текстовые формулы Excel)

Текстовые формулы Excel, которые нам понадобятся

Для решения этой задачи нам понадобится Excel и следующие стандартные текстовые функции

  1. SEARCH — находит текст или значение в тексте
  2. DLSTR — подсчитывает количество символов в ячейке с текстом
  3. RIGHTSIMB — отдает заданное количество символов от конца текста.

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

Нашу проблему можно решить:

  1. Вычислите длину текста в имени с помощью функции DLSTR
  2. Найдите номер символа, с которого начинается имя, с помощью функции SEARCH
  3. Вычислите длину имени как разницу результатов шагов 1 и 2
  4. Найдите имя с помощью функции RIGHT и длины имени из шага 3.

Это похоже на план действий. Давайте приступим!

Фукнция ДЛСТР

Эта текстовая функция Excel является одной из самых простых. Он просто требует указать ячейку и вывести количество символов в ней. Вы можете увидеть это в примере:

Функция ДЛСТР (Текстовые формулы Excel)

Функция ПОИСК

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

Обратите внимание, что место или текст, который нужно искать в этой функции, записывается в кавычках. В частности, это будет что-то вроде =Search(» «; ‘ячейка с текстом’).

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

Функция ПОИСК (Текстовые формулы Excel)

Функция ПРАВСИМВ

Наша задача — извлечь фамилию из имени и решить нашу задачу. Это можно сделать, указав имя актера в аргументе функции и выведя количество символов как разницу между первым и вторым действиями. В конце мы складываем решение:

Функция ПРАВСИМВ (Текстовые формулы Excel)

Давайте посмотрим, как работают текстовые функции Excel. Вы можете подписаться, чтобы оставаться в курсе событий. Мы расскажем еще много интересного.

Добавление гиперссылок, закладок и почтовых ссылок в Excel

Вы когда-нибудь задавались вопросом, как добавить гиперссылки, закладки или zip-ссылки в Excel? Ответы вы найдете здесь.

Следующие шаги относятся к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel 2011 для Mac и Excel Онлайн.

Читайте так же:
Как легко преобразовать несколько единиц энергии в Excel?

Что такое гиперссылки, закладки и почтовые ссылки?

Прежде всего, давайте уточним, что мы подразумеваем под каждым из терминов.

Гиперссылка позволяет открыть веб-страницу, выбрав ячейку на рабочем листе. Доступ к другим книгам Excel также упрощается с его помощью в Excel.

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

Ссылка mailto – это ссылка на адрес электронной почты. Выбор ссылки mailto открывает новое окно сообщения в программе электронной почты по умолчанию и вставляет адрес электронной почты в строку «Кому» сообщения.

Гиперссылка или закладка — это удобный способ перехода от одной области данных к другой в Excel. Ссылки на электронную почту облегчают отправку писем конкретным лицам или организациям. Во всех случаях:

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

Откройте диалоговое окно Вставить гиперссылку

Откройте диалоговое окно Вставить гиперссылку, нажав клавиши Ctrl + K в Windows или Command + K в Mac.

  1. Выберите ячейку, которая будет содержать гиперссылку, в рабочем листе Excel.
  2. Введите слово, которое вы хотите использовать в качестве якорного текста, например, «Tables» или «June_Sales.xlsx», и нажмите Enter .
  3. Выберите ячейку с якорным текстом во второй раз.
  4. Нажмите и удерживайте клавишу Ctrl (Windows) или Command⌘ (Mac).
  5. Нажмите и отпустите клавишу K, чтобы открыть диалоговое окно Вставка гиперссылки.

Как открыть диалоговое окно «Вставить гиперссылку» с помощью ленты

  1. На листе Excel выберите ячейку, которая будет содержать гиперссылку.
  2. Введите слово, которое будет действовать как текст привязки, например «Таблицы» или «June_Sales.xlsx», и нажмите Enter .
  3. Выберите ячейку с текстом привязки во второй раз.
  4. Выберите Вставить . (В Excel 2011 для Mac перейдите в меню Вставка .)
  5. Выберите Гиперссылка или Ссылка >Вставить ссылку в группе ссылок. Откроется диалоговое окно «Вставить гиперссылку».

Добавить гиперссылку в Excel

Чтобы создать гиперссылку на веб-страницу или файл Excel, выполните следующие действия.

Добавить гиперссылку на веб-страницу

  1. Откройте диалоговое окно «Вставка гиперссылки» одним из описанных выше способов.
  2. Откройте вкладку Файл или существующая веб-страница.
  3. В строке Адрес введите полный URL-адрес. Выберите OK, чтобы завершить создание гиперссылки и закрыть диалоговое окно.

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

Читайте так же:
Как найти и получить первый понедельник определенного года в Excel?

Добавить гиперссылку в файл Excel

Примечание. В Excel Online такой возможности нет.

  1. Откройте диалоговое окно Вставка гиперссылки.
  2. Откройте вкладку Существующий файл или веб-страница.
  3. Выберите Найти файл, чтобы открыть диалоговое окно Заполнить.
  4. Найти, чтобы найти имя файла Excel, выберите файл и выберите OK. Имя файла добавляется в строку Адрес диалогового окна Вставка гиперссылки.
  5. Выберите OK, чтобы завершить создание гиперссылки и закрыть диалоговое окно.

Гиперссылка отображается путем изменения цвета фона якорного текста в ячейке рабочего листа на синий и его подчеркивания. Как только она выбрана, открывается назначенная рабочая книга Excel.

Создать закладку на тот же лист Excel

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

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

Как создать закладку на тот же лист

В приведенном ниже примере вкладка была создана в другом месте той же таблицы Excel.

  1. Введите в ячейку имя, которое будет использоваться в качестве якорного текста закладки, и нажмите клавишу Enter.
  2. Выделите ячейку, чтобы сделать ее активной.
  3. Откройте диалоговое окно Вставка гиперссылки.
  4. Откройте вкладку Расположение в этом документе (или нажмите кнопку Расположение в этом документе в Excel Online).
  5. В текстовом поле Введите ссылку на ячейку введите ссылку на ячейку, расположенную в другом месте того же рабочего листа, например «Z100».
  6. Выберите OK, чтобы выйти из вкладки и закрыть диалоговое окно.

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

Курсор активной ячейки перемещается к ссылке на ячейку, которую вы ввели для закладки после выбора закладки.

Создать закладку на другой лист

Для того чтобы закладки отображались на разных листах рабочей книги Excel, требуется дополнительный шаг. Кроме того, необходимо указать рабочий лист в качестве места назначения закладки. Закладки легче создавать при переименовании рабочих листов в файлах с большим количеством рабочих листов.

  1. Открыть многоуровневую рабочую книгу Excel или добавить несколько рабочих листов в один файл электронной таблицы.
  2. Введите имя одного из рабочих листов в ячейку, которая будет использоваться в качестве текста привязки для вкладки.
  3. Выберите ячейку, чтобы сделать ее активной.
  4. Откройте диалоговое окно Вставка гиперссылки.
  5. Выберите вкладку Поместить в этот документ (или нажмите кнопку Поместить в этот документ в Excel Online).
  6. Введите ссылку на ячейку в поле Введите ссылку на ячейку.
  7. В поле Выберите либо место в этом документе , выберите имя целевого рабочего листа. Рабочие листы без имени называются Лист1, Лист2, Лист3 и т.д. Выберите OK, чтобы выйти из вкладки и закрыть диалоговое окно.
Читайте так же:
Как запустить макрос автоматически перед печатью в Excel?

Якорный текст в ячейке рабочего листа теперь синий и подчеркнутый, чтобы указать, что он содержит закладку.

Вы увидите, как курсор активной ячейки переместится к ссылке на ячейку, которую вы ввели при выборе закладки.

Вставьте ссылку Mailto в файл Excel

Документ можно легко отправить с помощью контактной информации в листе Excel.

  1. Введите в ячейку имя, которое будет служить якорным текстом для ссылки mailto, и нажмите Enter.
  2. Выберите эту ячейку, чтобы сделать ее активной.
  3. Откройте диалоговое окно Вставка гиперссылки.
  4. Выберите вкладку Адрес электронной почты (или нажмите кнопку Адрес электронной почты в Excel Online).
  5. В поле Адрес электронной почты введите адрес электронной почты лица, которое будет получать электронное сообщение. Этот адрес будет записан в строке «Кому» нового письма при выборе ссылки.
  6. В строке Тема введите тему письма. Этот текст пишется в теме нового сообщения. Эта опция недоступна в Excel Online.
  7. Выберите OK, чтобы завершить создание ссылки на электронную почту и закрыть диалоговое окно.

Якорный текст в ячейке рабочего листа теперь синий и подчеркнутый, чтобы показать, что он содержит гиперссылку.

Выберите ссылку mailto, и ваше стандартное приложение электронной почты откроет новое сообщение с введенным адресом и темой.

Удалить гиперссылку, не удаляя текст привязки

Вы можете удалить информацию гиперссылки без удаления текста, служившего якорем, если она вам больше не нужна.

  1. Наведите курсор на гиперссылку, которую нужно удалить. Указатель должен измениться на символ руки.
  2. Щелкните правой кнопкой мыши на якорном тексте гиперссылки, чтобы открыть контекстное меню.
  3. Выберите Удалить гиперссылку .

Синий цвет и подчеркивание должны быть удалены из якорного текста, чтобы показать, что гиперссылка удалена.

Как в Excel сделать выпадающий список

Одной из самых полезных функций Excel при работе с информацией является создание выпадающего списка. С его помощью можно быстро и правильно заполнить ячейки. Значения не вводятся вручную, а просто выбираются из заранее определенного списка. Также можно установить средства контроля, чтобы другие пользователи не могли вносить изменения и добавлять новые значения, которые изначально не предназначались. Возможность сортировки данных будет полезна во многих случаях, от контроля расходов до управления проектами.

Простой выпадающий список

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

В Excel вы можете легко создать список несколькими щелчками мыши. Для этого:

  1. Выберите столбец с определенными именами, щелкните по нему правой кнопкой мыши. В выпадающем меню выберите опцию «Присвоить имя».
Читайте так же:
Как изменить верхний регистр на нижний в Microsoft Excel?

  1. Откроется окно, в котором в соответствующем поле изменяется название выбранного столбца. По умолчанию Excel называет его именем первой ячейки.

  1. Выберите область, в которую нужно вставить список. На верхней панели перейдите на вкладку «Данные», а затем на «Проверка данных».

  1. Найдите параметр «Тип данных» в новом окне. Выберите «Список».

  1. Теперь введите «Источник». Щелкните левой кнопкой мыши на поле, введите «=» без кавычек и имя из шага 2. В качестве источника можно использовать столбцы из других листов электронной таблицы. Нажмите кнопку «OK».

Также можно добавлять специальные сообщения для облегчения быстрой обработки информации:

  1. Повторите действия, описанные в шаге 3. В открывшемся окне перейдите на вкладку «Сообщение для ввода».

  1. Введите заголовок и само сообщение — это своего рода подсказка для пользователя.

Связанный выпадающий список

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

  1. Подготовьте стол. В первой строке каждого столбца должна быть категория меню (для удобства).

  1. Дайте диапазонам перечисленных блюд название, соответствующее их категории. Общее название каждого диапазона должно четко соответствовать тому, что написано в первой ячейке каждого столбца.

  1. Создайте выпадающий список в отдельной области с помощью пункта меню Данные — Проверка данных. Источником является первая ячейка в каждом столбце.

  1. Затем перейдите к созданию списка блюд в одной из категорий. Вам поможет функция INDIRECT, которая преобразует текст в стандартную ссылку Excel, а источник данных — в нее. Выберите категорию, снова откройте окно управления данными и введите функцию =DVSSYL(H2).

Выбор другого элемента в H2 автоматически изменяет исходную ссылку и для H3. То есть источник связанного выпадающего списка в H3 меняется, чтобы отразить данные, которые были выбраны в H2.

Пробелы в названии при создании связанного выпадающего списка

Название категории или группы должно содержать пробелы, если оно содержит более одного слова. В именованных диапазонах пробелы в названиях не допускаются. Для этого существует подсказка.

При именовании диапазона значений важно использовать знак подчеркивания «_» вместо пробелов. Например, «Мясо_пища». Однако существует риск пропустить это правило в одном из имен, что приведет к ошибке при выполнении операции. В самих именах можно использовать обычный пробел, а при замене в списке заменить его подчеркиванием с помощью формулы SUBSTITUTE:

=ПОДСТАВИТЬ(F3;» «;»_») без кавычек.

Итоговая формула выглядит следующим образом:

=DWSSS(SUBSTITUTE($F$3;» «;»_)) без инвертированных запятых

Стоит обратить внимание на отсутствие пробелов в названии в начале и в конце, чтобы избежать некорректного вывода названий. Если вы конструируете название вручную, вы также можете автоматизировать этот процесс с помощью следующей функции:

Читайте так же:
Как импортировать данные с другого листа?

=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек.

Список с автозаполнением

Автоматически добавляет элементы из указанного диапазона. При внесении изменений предлагаемый выбор в Excel также немедленно изменится. Это также замечательно тем, что вам не нужно настраивать какие-либо параметры.

Способ 1

Нажмите на ячейку третьей стороны. Перейдите на вкладку «Данные» — «Валидация данных». В качестве «Типа данных» выберите «Список». В поле «Источник» выберите необходимое количество ячеек, но с отступом (можно весь столбец, формула будет =$A:$A).

Теперь, когда вы добавили новые пункты в список, вы также увидите их в раскрывающемся списке готовых пунктов.

Способ 2

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

  1. Выделите существующий список имен и назовите его в левой строке формул.

  1. Щелкните на третьей ячейке. Перейдите в раздел «Данные» — «Проверить данные». В качестве источника введите имя, присвоенное таблице на шаге 1, со знаком «=».

  1. Чтобы добавить другое значение, необходимо использовать пустую строку вставки. Выделите ячейку с любым именем, в меню «Главная» нажмите на «Вставка» — «Вставить строки в лист». На листе появится пустая ячейка, введите имя.

Способ 3

Современные версии Excel имеют более продвинутые и интересные функции для работы с информацией. Благодаря специальному форматированию диапазон ведет себя как единое целое: он автоматически повторяет свой размер и изменяется при корректировке исходных данных.

  1. Выберите содержимое, на вкладке Начало выберите Формат как таблица. В появившемся окне убедитесь, что установлен флажок «Таблица с заголовками».

  1. Назначьте подготовленную таблицу в качестве источника, введите формулу =DWSSYL(«Table1[Hot]»), где table1 — автоматически назначенное имя, [Hot] — имя столбца.

В нижних строках немедленно появятся новые значения.

Как в Excel сделать выпадающий список в ячейке с выбором нескольких значений?

В раскрывающемся списке множественного выбора пользователь выбирает по очереди фрагмент содержимого, который появится справа или снизу от выбранной строки, подлежащей изменению. Содержимое может появляться горизонтально, вертикально или оставаться на том же месте.

Сначала заполните таблицу. Выделите содержимое и выполните описанные выше действия, используя функцию «Проверка данных».

Чтобы добавить макрос, щелкните правой кнопкой мыши на ярлыке листа с выпадающим списком и выберите опцию Источник. Откроется Visual Basic, и вы должны использовать код для горизонтального отображения в окне редактора:

Результат должен быть таким:

Для вертикального вида можно использовать код:

Результат получился следующий:

Элементы, накапливающиеся в одной ячейке, кодируются следующим образом:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector