Как в эксель написать номер счета

Безопасное отражение в EXCEL номеров счетов с помощью шаблона

Если у вас есть таблица с важными данными (номера кредитных карт, номера личных телефонов, номера страховых полисов), то для сторонних лиц вы можете настроить отображение только последних цифр номера.

Решение задачи зависит от количества цифр в номере.

Число цифр в номере больше 15

Так как EXCEL хранит и выполняет вычисления с точностью 15 значащих цифр , то номера кредитных карт (16 цифр), банковских счетов (20 цифр) сохранить в числовом формате не удастся, последние цифры будут заменены 0. Поэтому номера с количеством цифр больше 15 могут храниться в EXCEL только в текстовом формате .

Настроим отображение номеров кредитных карт в безопасном формате. Вместо отображения полного номера 4887 1467 1084 1647, мы отобразим только 4 последние цифры **** **** **** 1647. Номер сохранен в текстовом формате, между группами из 4-х цифр – пробелы.

Решением является использование формулы: =”**** **** **** “&ПРАВСИМВ(A1;4)

В ячейке A1 должен быть номер кредитной карты. Столбец А можно скрыть, выделив любую его ячейку и нажав CTRL+0 . В меню Рецензирование / Изменения выбрать Защитить лист . Имеет смысл также спрятать саму формулу , чтобы неавторизованный пользователь не написал формулу =A1 и не вывел полный номер карты.

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

В файле примера приведен вариант со скрытым столбцом и скрытой формулой. В столбцах А и В размещена таблица с полными номерами карт и фамилиями.

В ячейке D2 размещен Выпадающий список с фамилиями (ячейка не должна быть защищена). В ячейке Е2 размещена формула =”**** **** **** “&ПРАВСИМВ(ВПР(D2;A2:B4;2;0);4)

Скроем столбцы А и В . В формате ячейки Е2 поставим галочку “Скрыть формулу”. Через меню Рецензирование / Изменения выберем Защитить лист . Получим следующий результат (формулы не видно, скрытые столбцы нельзя отобразить).

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

Число цифр в номере меньше 16

Пусть необходимо скрыть номер телефона (10 цифр), который хранится в числовом формате.

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

Пользовательский формат можно ввести через диалоговое окно Формат ячеек ( нажав CTRL+1 ), вкладка Число , (все форматы) . Сам формат вводите в поле Тип, предварительно все из него удалив.

Скроем последние 3 цифры в номере телефона (916) 734-66-58: (916)-734-6*-**.

Запишем формат в виде: [ =ТЕКСТ(A1;”[ , предварительно закомментировав обратным слешом специальный символ *. В этом случае мы получим результат в текстовом формате.

Чтобы скрыть последние 6 цифр можно использовать формулу =ТЕКСТ(A2;”[

В формате между 0 и ” содержится 2 символа пробела (скрываются 2 группы разрядов, т.е. 6 цифр).

Для контроля правильности ввода номеров (должно быть строго определенное число знаков) можно использовать Условное форматирование или Проверку данных .

Источник статьи: http://excel2.ru/articles/bezopasnoe-otrazhenie-v-ms-excel-nomerov-schetov-s-pomoshchyu-shablona

При вводе длинных чисел в ячейках Excel последние цифры меняются на нули.

Симптомы

Когда вы вводите число, содержащее более 15 цифр в ячейке в Microsoft Excel, Excel изменяет все цифры после пятнадцатого места на нули. Например, введите ИДЕНТИФИКАЦИОНный номер кредитной карты в следующем формате:

В этом случае Excel изменяет последнюю цифру на ноль.

Причина

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

Обходной путь

Добавление знака кавычек

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

Для этого выберите пустую ячейку, введите одиночную кавычку (‘), а затем введите номер. Все цифры отображаются в ячейке.

Форматирование ячеек

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

Выберите все затронутые ячейки, а затем нажмите клавиши CTRL + 1 , чтобы открыть диалоговое окно Формат ячеек .

На вкладке число выберите пункт текст в списке Категория , а затем нажмите кнопку ОК.

Дополнительная информация

Такое поведение возникает только в том случае, если ячейка форматируется как число, а введенное число превышает 15 цифр. Для ячеек, отформатированных как текст, можно ввести до 32 767 символов. В Excel отображается до 1 024 символов на листе.

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

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

Требуется дополнительная помощь? Зайдите на сайт сообщества Майкрософт.

Источник статьи: http://docs.microsoft.com/ru-ru/office/troubleshoot/excel/last-digits-changed-to-zeros

СЧЁТ (функция СЧЁТ)

Функция СЧЁТ подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов. Функция СЧЁТ используется для определения количества числовых ячеек в диапазонах и массивах чисел. Например, для вычисления количества чисел в диапазоне A1:A20 можно ввести следующую формулу: =СЧЁТ(A1:A20). Если в данном примере пять ячеек из диапазона содержат числа, то результатом будет значение 5.

Синтаксис

Аргументы функции СЧЁТ указаны ниже.

Значение1 — обязательный аргумент. Первый элемент, ссылка на ячейку или диапазон, для которого требуется подсчитать количество чисел.

Значение2; . — необязательный аргумент. До 255 дополнительных элементов, ссылок на ячейки или диапазонов, в которых требуется подсчитать количество чисел.

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

Замечания

Учитываются аргументы, являющиеся числами, датами или текстовым представлением чисел (например, число, заключенное в кавычки, такое как “1”).

Логические значения и текстовые представления чисел, введенные непосредственно в списке аргументов, также учитываются.

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

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

Если необходимо подсчитать логические значения, элементы текста или значения ошибок, используйте функцию СЧЁТЗ.

Если требуется подсчитать только те числа, которые соответствуют определенным критериям, используйте функцию СЧЁТЕСЛИ или СЧЁТЕСЛИМН.

Пример

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

Источник статьи: http://support.microsoft.com/ru-ru/office/%D1%81%D1%87%D1%91%D1%82-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D1%81%D1%87%D1%91%D1%82-a59cd7fc-b623-4d93-87a4-d23bf411294c

Сохранение начальных нулей и больших чисел

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.

Преобразование чисел в текст при импорте текстовых данных

Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query). В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.

Откройте вкладку Данные, нажмите кнопку Получить данные и выберите вариант Из текстового/CSV-файла. Если вы не видите кнопку Получить данные, выберите Создать запрос > Из файла > Из текста, найдите нужный файл и нажмите кнопку Импорт.

Excel загрузит данные в область предварительного просмотра. Нажмите кнопку ” изменить ” в области предварительного просмотра, чтобы загрузить Редактор запросов.

Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная > Преобразовать > Тип данных > Текст.

Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.

В диалоговом окне Изменение типа столбца выберите команду Заменить текущие, и Excel преобразует выделенные столбцы в текст.

преобразование данных в текст”/>

По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.

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

В Excel 2010 и Excel 2013 импортировать текстовые файлы и преобразовывать числа в текст можно двумя способами. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если надстройку Power Query скачать не удается, можно воспользоваться мастером импорта текста. В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.

На ленте откройте вкладку Power Query и выберите Получение внешних данных > Из текста.

Excel загрузит данные в область предварительного просмотра. Нажмите кнопку ” изменить ” в области предварительного просмотра, чтобы загрузить Редактор запросов.

Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная > Преобразовать > Тип данных > Текст.

Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.

В диалоговом окне Изменение типа столбца выберите команду Заменить текущие, и Excel преобразует выделенные столбцы в текст.

преобразование данных в текст”/>

По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.

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

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

Если эту задачу необходимо решить в пределах книги, поскольку она не используется другими программами в качестве источника данных, для сохранения начальных нулей можно использовать пользовательский или специальный формат. Этот способ подходит для цифровых кодов, содержащих менее 16 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.

Источник статьи: http://support.microsoft.com/ru-ru/office/%D1%81%D0%BE%D1%85%D1%80%D0%B0%D0%BD%D0%B5%D0%BD%D0%B8%D0%B5-%D0%BD%D0%B0%D1%87%D0%B0%D0%BB%D1%8C%D0%BD%D1%8B%D1%85-%D0%BD%D1%83%D0%BB%D0%B5%D0%B9-%D0%B8-%D0%B1%D0%BE%D0%BB%D1%8C%D1%88%D0%B8%D1%85-%D1%87%D0%B8%D1%81%D0%B5%D0%BB-1bf7b935-36e1-4985-842f-5dfa51f85fe7

Как в EXCEL записать № карточки длиной в 16 цифр, чтобы не искажались?

В ячейке поддерживается только 15 цифр. Нужно сначала сделать ячейку текстовой (Правой кнопкой – формат ячеек – Число – Текстовый), а потом уже номер карты в нее вписать. Либо перед номер карточки из 16 цифр вставить этот значок ‘ (буква Э в англ. раскладке). В ячейке его не будет видно, номер карты не будет “искажаться”.

Что бы не искажались кто? Цифры? Так они итак не искажаются. Нужно просто записать цифры в ячейку по порядку и всё.
Ьььььььььььььььььььььььььььььььььььььььььььььььььь

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

Как сделать, чтобы в ячейке Excel текст начинался с заглавной буквы, а остальные были строчные?

После применения, можно скопировать полученный результат и вставить на место старого “как значение”.

Есть выгрузка с числами в Excel. Точка вместо запятой перед дес.частью. При замене некоторые превращаются в даты. Как правильно поправить?

1) Выделяете проблемные данные;

2) “Данные” – “Текст по столбцам”

3) Первый и второй шаг пропускаете

4) На третьем жмете “Подробнее” – “Разделитель целой и дробной части” – “точка”

Как скрыть нулевые значения в ячейках excel?

Есть разные варианты. Например, такие:

Файл – Параметры – Дополнительно – Параметры отображения листа – снять галочку Показывать нули в ячейках, которые содержат нулевые значения. Эта настройка действует на выбранный лист. Если нужно применить на несколько – повторите для каждого

Выделяете диапазон, где надо скрыть нули. Жмете Ctrl+1.

Выбираете Число – (все форматы).

В поле Тип вводите такой формат: Основной;Основной;

Как в Excel посчитать количество символов в ячейке без HTML-тегов?

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

Чтобы узнать количество символов Excel, компания Майкрософт разработала несколько внутренних формул.

Проще всего узнать количество знаков в иксель можно с помощью ДЛСТР. Как работает эта функция? Сейчас объясним подробно. Эта формула помогает определить данные только в ячейке, а не всем файле.

ДЛСТР (длина строки…сокращенно). Эта формула подходит как для экселя офлайн, так и для документа на гугл диске в онлайн. Как посчитать количество символов Excel с ее помощью:

1. Поставить курсор в любую свободную ячейку.
2. Поставить знак равно = и написать большие буквы ДЛСТР (или найти эту функцию в списке функций, но это сложнее), далее открыть скобку и выбрать нужную ячейку, затем закрыть скобку и нажать Enter. Ниже пример, как это сделать в экселе:

Источник статьи: http://yandex.ru/q/question/computers/kak_v_exel_zapisat_no_kartochki_16_tsifr_75714d0b/

Если у вас есть таблица с важными данными (номера кредитных карт, номера личных телефонов, номера страховых полисов), то для сторонних лиц вы можете настроить отображение только последних цифр номера.

Решение задачи зависит от количества цифр в номере.

Число цифр в номере больше 15

Так как EXCEL хранит и выполняет вычисления

с точностью 15 значащих цифр

, то номера кредитных карт (16 цифр), банковских счетов (20 цифр) сохранить в числовом формате не удастся, последние цифры будут заменены 0. Поэтому номера с количеством цифр больше 15

могут храниться в EXCEL только в текстовом формате

.

Настроим отображение номеров кредитных карт в безопасном формате. Вместо отображения полного номера 4887 1467 1084 1647, мы отобразим только 4 последние цифры **** **** **** 1647. Номер сохранен в текстовом формате, между группами из 4-х цифр — пробелы.

Решением является использование формулы:

=»**** **** **** «&ПРАВСИМВ(A1;4)

В ячейке

A1

должен быть номер кредитной карты. Столбец

А

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

CTRL+0

. В меню

выбрать

Защитить лист

. Имеет смысл также

спрятать саму формулу

, чтобы неавторизованный пользователь не написал формулу

=A1

и не вывел полный номер карты.

Номер в безопасном формате и полный номер можно разместить на разных листах, тогда

лист с полными номерами можно скрыть

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

В файле примера приведен вариант со скрытым столбцом и скрытой формулой. В столбцах

А

и

В

размещена таблица с полными номерами карт и фамилиями.

В ячейке

D2

размещен

Выпадающий список

с фамилиями (ячейка не должна быть защищена). В ячейке

Е2

размещена формула

=»**** **** **** «&ПРАВСИМВ(ВПР(D2;A2:B4;2;0);4)

Скроем столбцы

А

и

В

.

В формате ячейки

Е2

поставим галочку «Скрыть формулу». Через меню

выберем

Защитить лист

. Получим следующий результат (формулы не видно, скрытые столбцы нельзя отобразить).

Выбрав в ячейке

D2

другую фамилию, получим в ячейке

Е2

соответствующий номер карты в безопасном формате.

Число цифр в номере меньше 16

Пусть необходимо скрыть номер телефона (10 цифр), который хранится в числовом формате.

Для отображения нескольких

последних

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

первых

цифр номера попробуем использовать

пользовательский формат

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

Пользовательский формат можно ввести через диалоговое окно

Формат ячеек (

нажав

CTRL+1

), вкладка

Число

,

(все форматы)

. Сам формат вводите в поле

Тип,

предварительно все из него удалив.

Скроем последние 3 цифры в номере телефона (916) 734-66-58: (916)-734-6*-**.

Запишем формат в виде: [<=9999999]0-0 «*-**»;(00)000-0 «*-**»

В формате между 0 и » содержится символ пробела (в записи, которая приведена далее, символ пробела заменен словом

Пробел:

[<=9999999]0-0

Пробел

«*-**»;(00)000-0

Пробел

«*-**»). Формат работает при записи номера в 7 значном и 10 значном формате.

Столбец

А

и формулу в ячейках

B1

и

B2

можно скрыть, как показано в предыдущем разделе.

Обратите внимание, что вместо (916)-734-

6

*-** мы получили (916)-734-

7

*-**, т.к. на самом деле мы

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

, и при этом произошло округление. Если бы исходный номер был (916) 734-6

1

-58, то мы получили бы верный результат. Именно по этому использование пользовательского формата в данном случае может быть источником ошибки.

Кроме того, открыв любой незащищенный лист с общим форматом ячейки, можно сослаться на ячейки

B1

или

B2

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

=ТЕКСТ(A1;»[<=9999999]0-0 «&»*-**;(00)000-0 «&»*-**»)

, предварительно закомментировав обратным слешом специальный символ *. В этом случае мы получим результат в текстовом формате.

Чтобы скрыть последние 6 цифр можно использовать формулу

=ТЕКСТ(A2;»[<=9999999]0 «&»**-**-**;(00)-0 «&»**-**-**»)

В формате между 0 и » содержится 2 символа пробела (скрываются 2 группы разрядов, т.е. 6 цифр).

Для контроля правильности ввода номеров (должно быть строго определенное число знаков) можно использовать

Условное форматирование

или

Проверку данных

.