Как сделать почтовый индекс в excel

Отображение чисел в виде почтовых индексов, страховых номеров (SSN) и номеров телефонов

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

Применение к числам заранее задав формат почтового индекса.

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" рядом с полем "Общий" в поле "Число щелкните стрелку и выберите "Другие числовые форматы".

В диалоговом окне "Формат ячеек" в категории выберите "Специальный".

В списке "Тип" выберите "Почтовый индекс" или "Почтовый индекс+4".

Примечание: Эти специальные числные форматы доступны только в том случае, если в формате "Местоположение (язык) " установлено положение "США(на английском языке)". В разных региональных местной области для почтовых индексов не за исключением специальных числных форматов.

Создание пользовательского формата почтового индекса

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

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

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" рядом с полем "Общий" в поле "Число щелкните стрелку и выберите "Другие числовые форматы".

В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.

В списке Тип выберите числовой формат, который нужно настроить. Код, используемый для числовых форматов, появится в поле "Тип" над списком "Тип".

Совет: Чтобы настроить предопределный формат "Почтовый индекс + 4", выберите в списке "Тип" номер 00000-0000. При выборе встроенного числого формата в списке "Тип" Excel создает его копию, которую можно настроить.

В поле Тип внесите необходимые изменения в выбранный числовой формат.

Совет: Если диапазон ячеек содержит как пяти, так и девятизначные почтовые индексы, можно ввести [

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

Ячейку или диапазон ячеек можно отформатировать таким образом, чтобы перед почтовым индексом отображались символы, заполнявшие ее ширину. Например, нули или тире можно использовать для отображения почтового индекса следующим образом: 00000000 98052 или ——- 98052.

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

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" рядом с полем "Число" в поле щелкните стрелку и выберите "Другие числовые форматы".

В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.

В поле "Тип" введите "*0" и нужный формат почтового индекса.

Совет: Например, для 5-значного почтового индекса введите *0#####

Отображение номеров социального страхования полностью

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" рядом с полем "Число" в поле щелкните стрелку и выберите "Другие числовые форматы".

В диалоговом окне "Формат ячеек" в категории выберите "Специальный".

В списке "Тип" выберите номер социального обеспечения.

Примечание: Этот специальный числовой формат доступен только в том случае, если в формате "Местоположение (язык) установлено положение "СОЕДИНЕННЫЕ Штаты (английский)". В разных региональных сфере особые числовые форматы для номеров социального страхования не отличаются или не имеют специального формата.

Отображение только последних цифр номеров социального страхования

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

Важно: Чтобы запретить другим пользователям просматривать весь номер социального обеспечения, сначала можно скрыть столбец с этим номером, а затем защитить его, чтобы неавторизованные пользователи не могли отвести данные. Лучше не хранить полные номера социального страхования (SOCIAL SECURITY) в книгах. Вместо этого храните полные номера социального страхования в расположении, которое отвечает строгим стандартам безопасности (например, в программе для работы с базами данных), а затем используйте только четыре последних цифры чисел в книгах.

В пустом столбце щелкните первую ячейку.

На вкладке "Формулы" нажмите кнопку "Вставить функцию",введите "ТЕКСТ"и введите "С CONCATENATE".

В области формул отредактируем формулу, чтобы она выглядела вот так:

=С CONCATENATE("000-00-"; ПРАВАЯ( : ;4))

где — это первая ячейка (например, A1), а CELLN>— последняя ячейка (например, A10) в диапазоне ячеек, содержащих номера социального страхования (SOCIAL SECURITY).

Excel отображает текстовую строку "000-00-" вместо 5 первых цифр номера социального обеспечения и объединяет ее с четырьмя последними цифрами номера социального обеспечения (например, 000-00-1234).

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

Отображение номеров телефонов

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" рядом с полем "Число" в поле щелкните стрелку и выберите "Другие числовые форматы".

В диалоговом окне "Формат ячеек" в категории выберите "Специальный".

В списке "Тип" выберите "Номер телефона".

Примечание: Этот специальный числовой формат доступен только в том случае, если в формате "Местоположение (язык) установлено положение "СОЕДИНЕННЫЕ Штаты (английский)". В разных региональных окнах для номеров телефонов не за исключением специальных форматов номеров.

Применение к числам заранее задав формат почтового индекса.

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" вобласти "Число" во всплывающее меню "Числовом формате" выберите пункт "Пользовательский".

В диалоговом окне "Формат ячеек" в категории выберите "Специальный".

В списке "Тип" выберите "Почтовый индекс" или "Почтовый индекс+4".

Примечание: Эти специальные числные форматы доступны только в том случае, если в формате "Местоположение (язык) " установлено положение "США(на английском языке)". В разных региональных местной области для почтовых индексов не за исключением специальных числных форматов.

Создание пользовательского формата почтового индекса

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

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

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" вобласти "Число" во всплывающее меню "Числовом формате" выберите пункт "Пользовательский".

В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.

В списке Тип выберите числовой формат, который нужно настроить. Код, используемый для числовых форматов, появится в поле "Тип" над списком "Тип".

Совет: Чтобы настроить предопределный формат "Почтовый индекс + 4", выберите в списке "Тип" номер 00000-0000. При выборе встроенного числого формата в списке "Тип" Excel создает его копию, которую можно настроить.

В поле Тип внесите необходимые изменения в выбранный числовой формат.

Совет: Если диапазон ячеек содержит как пяти, так и девятизначные почтовые индексы, можно ввести [

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

Ячейку или диапазон ячеек можно отформатировать таким образом, чтобы перед почтовым индексом отображались символы, заполнявшие ее ширину. Например, нули или тире можно использовать для отображения почтового индекса следующим образом: 00000000 98052 или ——- 98052.

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

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" вобласти "Число" во всплывающее меню "Числовом формате" выберите пункт "Пользовательский".

В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.

В поле "Тип" введите "*0" и нужный формат почтового индекса.

Совет: Например, для 5-значного почтового индекса введите *0#####

Отображение номеров социального страхования полностью

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" вобласти "Число" во всплывающее меню "Числовом формате" выберите пункт "Пользовательский".

В диалоговом окне "Формат ячеек" в категории выберите "Специальный".

В списке "Тип" выберите номер социального обеспечения.

Примечание: Этот специальный числовой формат доступен только в том случае, если в формате "Местоположение (язык) установлено положение "СОЕДИНЕННЫЕ Штаты (английский)". В разных региональных сфере особые числовые форматы для номеров социального страхования не отличаются или не имеют специального формата.

Отображение только последних цифр номеров социального страхования

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

Важно: Чтобы запретить другим пользователям просматривать весь номер социального обеспечения, сначала можно скрыть столбец с этим номером, а затем защитить его, чтобы неавторизованные пользователи не могли отвести данные. Лучше не хранить полные номера социального страхования (SOCIAL SECURITY) в книгах. Вместо этого храните полные номера социального страхования в расположении, которое отвечает строгим стандартам безопасности (например, в программе для работы с базами данных), а затем используйте только четыре последних цифры чисел в книгах.

В пустом столбце щелкните первую ячейку.

На вкладке "Формулы" в области "Функция"нажмите кнопку "Вставить",наведите указатель мыши на пункт "Текст"и выберите пункт "СЖАТИЕ".

В области формул отредактируем формулу, чтобы она выглядела вот так:

=С CONCATENATE("000-00-"; ПРАВАЯ( : ;4))

где — это первая ячейка (например, A1), а CELLN>— последняя ячейка (например, A10) в диапазоне ячеек, содержащих номера социального страхования (SOCIAL SECURITY).

Excel отображает текстовую строку "000-00-" вместо 5 первых цифр номера социального обеспечения и объединяет ее с четырьмя последними цифрами номера социального обеспечения (например, 000-00-1234).

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

Отображение номеров телефонов

Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.

На вкладке "Главная" вобласти "Число" во всплывающее меню "Числовом формате" выберите пункт "Пользовательский".

В диалоговом окне "Формат ячеек" в категории выберите "Специальный".

В списке "Тип" выберите "Номер телефона".

Примечание: Этот специальный числовой формат доступен только в том случае, если в формате "Местоположение (язык) установлено положение "СОЕДИНЕННЫЕ Штаты (английский)". В разных региональных окнах для номеров телефонов не за исключением специальных форматов номеров.

Функция ИНДЕКС в программе Microsoft Excel

Функция ИНДЕКС в Microsoft Excel

Одной из самых полезных функций программы Эксель является оператор ИНДЕКС. Он производит поиск данных в диапазоне на пересечении указанных строки и столбца, возвращая результат в заранее обозначенную ячейку. Но полностью возможности этой функции раскрываются при использовании её в сложных формулах в комбинации с другими операторами. Давайте рассмотрим различные варианты её применения.

Использование функции ИНДЕКС

Оператор ИНДЕКС относится к группе функций из категории «Ссылки и массивы». Он имеет две разновидности: для массивов и для ссылок.

Вариант для массивов имеет следующий синтаксис:

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

Синтаксис для ссылочного варианта выглядит так:

Тут точно так же можно использовать только один аргумент из двух: «Номер строки» или «Номер столбца». Аргумент «Номер области» вообще является необязательным и он применяется только тогда, когда в операции участвуют несколько диапазонов.

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

Способ 1: использование оператора ИНДЕКС для массивов

Давайте, прежде всего, разберем на простейшем примере алгоритм использования оператора ИНДЕКС для массивов.

Имеем таблицу зарплат. В первом её столбце отображены фамилии работников, во втором – дата выплаты, а в третьем – величина суммы заработка. Нам нужно вывести имя работника в третьей строке.

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

Переход в Мастер функций в Microsoft Excel

Происходит процедура активации Мастера функций. В категории «Ссылки и массивы» данного инструмента или «Полный алфавитный перечень» ищем наименование «ИНДЕКС». После того, как нашли этого оператора, выделяем его и щелкаем по кнопке «OK», которая размещается в нижней части окна.

Мастер функций в Microsoft Excel

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

Выбор типа функции ИНДЕКС в Microsoft Excel

Открывается окно аргументов функции ИНДЕКС. Как выше говорилось, у неё имеется три аргумента, а соответственно и три поля для заполнения.

В поле «Массив» нужно указать адрес обрабатываемого диапазона данных. Его можно вбить вручную. Но для облегчения задачи мы поступим иначе. Ставим курсор в соответствующее поле, а затем обводим весь диапазон табличных данных на листе. После этого адрес диапазона тут же отобразится в поле.

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

После того, как все указанные настройки совершены, щелкаем по кнопке «OK».

Окно аргументов функции ИНДЕКС в Microsoft Excel

  • Результат обработки выводится в ячейку, которая была указана в первом пункте данной инструкции. Именно выведенная фамилия является третьей в списке в выделенном диапазоне данных.
  • Результат обработки функции ИНДЕКС в Microsoft Excel

    Мы разобрали применение функции ИНДЕКС в многомерном массиве (несколько столбцов и строк). Если бы диапазон был одномерным, то заполнение данных в окне аргументов было бы ещё проще. В поле «Массив» тем же методом, что и выше, мы указываем его адрес. В данном случае диапазон данных состоит только из значений в одной колонке «Имя». В поле «Номер строки» указываем значение «3», так как нужно узнать данные из третьей строки. Поле «Номер столбца» вообще можно оставить пустым, так как у нас одномерный диапазон, в котором используется только один столбец. Жмем на кнопку «OK».

    Окно аргументов функции ИНДЕКС для одномерного массива в Microsoft Excel

    Результат будет точно такой же, что и выше.

    Результат обработки функции ИНДЕКС для одномерного массива в Microsoft Excel

    Это был простейший пример, чтобы вы увидели, как работает данная функция, но на практике подобный вариант её использования применяется все-таки редко.

    Способ 2: применение в комплексе с оператором ПОИСКПОЗ

    На практике функция ИНДЕКС чаще всего применяется вместе с аргументом ПОИСКПОЗ. Связка ИНДЕКСПОИСКПОЗ является мощнейшим инструментом при работе в Эксель, который по своему функционалу более гибок, чем его ближайший аналог – оператор ВПР.

    Основной задачей функции ПОИСКПОЗ является указание номера по порядку определенного значения в выделенном диапазоне.

    Синтаксис оператора ПОИСКПОЗ такой:

    =ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

    • Искомое значение – это значение, позицию которого в диапазоне мы ищем;
    • Просматриваемый массив – это диапазон, в котором находится это значение;
    • Тип сопоставления – это необязательный параметр, который определяет, точно или приблизительно искать значения. Мы будем искать точные значения, поэтому данный аргумент не используется.

    С помощью этого инструмента можно автоматизировать введение аргументов «Номер строки» и «Номер столбца» в функцию ИНДЕКС.

    Посмотрим, как это можно сделать на конкретном примере. Работаем все с той же таблицей, о которой шла речь выше. Отдельно у нас имеется два дополнительных поля – «Имя» и «Сумма». Нужно сделать так, что при введении имени работника автоматически отображалась сумма заработанных им денег. Посмотрим, как это можно воплотить на практике, применив функции ИНДЕКС и ПОИСКПОЗ.

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

    Имя вписано в поле в Microsoft Excel

    Выделяем ячейку в поле «Сумма», в которой будет выводиться итоговый результат. Запускаем окно аргументов функции ИНДЕКС для массивов.

    В поле «Массив» вносим координаты столбца, в котором находятся суммы заработных плат работников.

    Поле «Номер столбца» оставляем пустым, так как мы используем для примера одномерный диапазон.

    А вот в поле «Номер строки» нам как раз нужно будет записать функцию ПОИСКПОЗ. Для её записи придерживаемся того синтаксиса, о котором шла речь выше. Сразу в поле вписываем наименование самого оператора «ПОИСКПОЗ» без кавычек. Затем сразу же открываем скобку и указываем координаты искомого значения. Это координаты той ячейки, в которую мы отдельно записали фамилию работника Парфенова. Ставим точку с запятой и указываем координаты просматриваемого диапазона. В нашем случае это адрес столбца с именами сотрудников. После этого закрываем скобку.

    После того, как все значения внесены, жмем на кнопку «OK».

    Окно аргументов функции ИНДЕКС в комбинации с оператором ПОИСКПОЗ в Microsoft Excel

    Результат количества заработка Парфенова Д. Ф. после обработки выводится в поле «Сумма».

    Результат обработки функции ИНДЕКС в комбинации с оператором ПОИСКПОЗ в Microsoft Excel

  • Теперь, если в поле «Имя» мы изменим содержимое с «Парфенов Д.Ф.», на, например, «Попова М. Д.», то автоматически изменится и значение заработной платы в поле «Сумма».
  • Изменение значений при использовании функции ИНДЕКС в комбинации с оператором ПОИСКПОЗ в Microsoft Excel

    Способ 3: обработка нескольких таблиц

    Теперь посмотрим, как с помощью оператора ИНДЕКС можно обработать несколько таблиц. Для этих целей будет применяться дополнительный аргумент «Номер области».

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

      Выделяем ячейку, в которой будет производиться вывод результата и обычным способом открываем Мастер функций, но при выборе типа оператора выбираем ссылочный вид. Это нам нужно потому, что именно этот тип поддерживает работу с аргументом «Номер области».

    Выбор ссылочного вида функции ИНДЕКС в Microsoft Excel

    Открывается окно аргументов. В поле «Ссылка» нам нужно указать адреса всех трех диапазонов. Для этого устанавливаем курсор в поле и выделяем первый диапазон с зажатой левой кнопкой мыши. Затем ставим точку с запятой. Это очень важно, так как если вы сразу перейдете к выделению следующего массива, то его адрес просто заменит координаты предыдущего. Итак, после введения точки с запятой выделяем следующий диапазон. Затем опять ставим точку с запятой и выделяем последний массив. Все выражение, которое находится в поле «Ссылка» берем в скобки.

    В поле «Номер строки» указываем цифру «2», так как ищем вторую фамилию в списке.

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

    В поле «Номер области» ставим цифру «3», так как нам нужно найти данные в третьей таблице, в которой содержится информация о заработной плате за третий месяц.

    После того, как все данные введены, щелкаем по кнопке «OK».

    Окно аргументов функции ИНДЕКС при работе с тремя областями в Microsoft Excel

  • После этого в предварительно выделенную ячейку выводятся результаты вычисления. Там отображается сумма заработной платы второго по счету работника (Сафронова В. М.) за третий месяц.
  • Результат обработки функции ИНДЕКС при работе с тремя областями в Microsoft Excel

    Способ 4: вычисление суммы

    Ссылочная форма не так часто применяется, как форма массива, но её можно использовать не только при работе с несколькими диапазонами, но и для других нужд. Например, её можно применять для расчета суммы в комбинации с оператором СУММ.

    При сложении суммы СУММ имеет следующий синтаксис:

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

    Результат функции СУММ в Microsoft Excel

    Но можно её немного модифицировать, использовав функцию ИНДЕКС. Тогда она будет иметь следующий вид:

    Результат комбинации функции СУММ и ИНДЕКС в Microsoft Excel

    В этом случае в координатах начала массива указывается ячейка, с которой он начинается. А вот в координатах указания окончания массива используется оператор ИНДЕКС. В данном случае первый аргумент оператора ИНДЕКС указывает на диапазон, а второй – на последнюю его ячейку – шестую.

    Как видим, функцию ИНДЕКС можно использовать в Экселе для решения довольно разноплановых задач. Хотя мы рассмотрели далеко не все возможные варианты её применения, а только самые востребованные. Существует два типа этой функции: ссылочный и для массивов. Наиболее эффективно её можно применять в комбинации с другими операторами. Созданные таким способом формулы смогут решать самые сложные задачи.

    Мы рады, что смогли помочь Вам в решении проблемы.

    Помимо этой статьи, на сайте еще 11905 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

    Ссылка на основную публикацию