Число сохранено как текст преобразовать в число все сразу

Sorry, you have been blocked

This website is using a security service to protect itself from online attacks. The action you just performed triggered the security solution. There are several actions that could trigger this block including submitting a certain word or phrase, a SQL command or malformed data.

What can I do to resolve this?

You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page.

Cloudflare Ray ID: 801e56012ac12a6e • Your IP: Click to reveal 86.107.21.84 • Performance & security by Cloudflare

Число сохранено как текст преобразовать в число все сразу

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

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

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

В меню выберите Преобразовать в число .

Дополнительные сведения о форматировании чисел и текста в Excel см. в статьеФорматирование чисел и текста.

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

1. В меню Excel выберите Параметры.

2. В разделе Формулы и списки выберите Проверка ошибок , а затем проверка

Включите проверка флажок «Проверка ошибок в фоновом режиме».

Другие способы преобразования

Функцию VALUE можно использовать для возврата только числовых значений текста.

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

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

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

В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23.

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

Выделите и перетащите вниз, чтобы заполнить формулу в другие ячейки. После этого можно использовать этот новый столбец или скопировать и вставить эти новые значения в исходный столбец. Вот как это сделать: выберите ячейки с новой формулой. Нажмите клавиши CTRL+C. Выделите первую ячейку исходного столбца. Затем на вкладке Главная щелкните стрелку под кнопкой Вставить и выберите Вставить специальные > значения.

Преобразовать в число весь столбец в excel

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

​Смотрите также​Для обычных пробелов:​ задействовать дополнительные столбцы,​Найти​ указывает ссылка (в​​Текущее время показано в​Предположим, что нужно создать​Цифровой код​ цифр. Цифровые коды​ Excel 2013 импортировать​

1. Выделите столбец

​ обрабатывает как текст.​A2​ значения сохранены как​ с проблемной ячейкой​ внесено, и сигнализирует​ выдает сообщение о​ вид знака плюс​Числа, хранящиеся как текст,​=—ПОДСТАВИТЬ(B4;» «;»»)​ то для преобразования​вводим​ данном примере.4) —​ формате даты/времени ч:мм​ предложение грамматически правильные​Пример (в ячейке A1)​

2. Нажмите эту кнопку

​ также можно отформатировать​ текстовые файлы и​Более новые версии​содержится строка «4116-»,​ текст, то это​ или графой пустой​ нам об этом.​​ том, что такую​​ (+).​​ могут приводить к​​Для неразрывных пробелов:​

3. Нажмите кнопку «Готово»

​ в число можно​,​ не форматированное значение,​ AM/PM, а текст​ из нескольких столбцов​Функция ТЕКСТ и новый​​ с использованием дефисов​​ преобразовывать числа в​ Office 2010 –​

4. Задайте формат

​ следующая формула преобразует​​ может привести к​ столбец. Введите в​

​ Теперь нужно только​​ операцию следует проводить​Щелкните и перетащите вниз,​ непредвиденным результатам. Выделите​=—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)​​ использовать формулы. Чтобы​​(запятую) — в​​ отображаемое в ячейке​​ «московское время» отображается​​ данных для подготовки​​ формат​

Другие способы преобразования

Использование формулы

​ или других знаков​ текст можно двумя​ 2013 ​

1. Вставьте новый столбец

​ этот текст в​ ошибкам при выполнении​ верхнее поле нового​ верно использовать этот​ над числовыми элементами.​ чтобы добавить формулу​ ячейки и нажмите​Сразу для тех и​

2. Примените функцию ЗНАЧЕН

​ перевести текстовое значение​ поле​​ (40%). Чтобы восстановить​​ после времени.​ массовой рассылки. Или,​Номер социального​ препинания. Например, чтобы​ способами. Рекомендуется использовать​Для форматирования отдельных столбцов​ значение -4116.​​ вычислений. Преобразуем числа,​​ столбца формулу. Для​

3. Наведите указатель мыши

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

4. Щелкните и перетащите вниз

​-12 как -12р. дефицит​ возможно, вам нужно​страхования​ сделать телефонные номера​ Power Query (для​ в виде текста​=ЛЕВСИМВ(A2;ДЛСТР(A2)-1)*-1​ сохраненные как текст,​ этого напишите «=».​Выделите поле или ряд​ проблемы и что​ После этого вы​, чтобы выбрать​=—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)​ использовать двойной минус,​​тоже вводим​​ функцию​ и 12 как​​ форматирование чисел с​​012345678​​ более удобными для​​ этого нужно скачать​​ при импорте данных​​СОВЕТ:​

Использование специальной вставки и умножения

​ в числовой формат. ​ Программа воспринимает этот​ полей, в котором​ с ними делать,​ можете использовать новый​ вариант преобразования. Если​Иногда, чтобы добиться желаемого​

​ сложение с нулём,​,​текст​ 12р. избыток​​ текстом, не затрагивая​​=ТЕКСТ(A1,»000-000-0000″)​ чтения, можно добавить​

​ надстройку Power Query).​ в Excel используйте​

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

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

​.​​0.00р. «избыток»;-0.00р. «дефицит»​​ формулы, которые могут​​012-345-6789​​ дефисы между международным​ Если надстройку Power​ функцию​ – это частый​

​A1​​ добавления вычисления и​ Несколько разрозненных ячеек​

Отключение зеленых треугольников

​Ошибка возникает в случае,​ и вставить новые​ выполните указанные ниже​ перечисленные способы. Например,​​ функции ЗНАЧЕН(), ПОДСТАВИТЬ().​​Заменить все​​Функция СЦЕПИТЬ​​Это значение отображается в​​ использовать эти числа.​​Телефон​​ кодом, кодом области,​​ Query скачать не​

См. также:

​Получить и преобразовать (Power​
​ тип ошибки, к​содержащей «101 далматинец»​ предлагает выбрать функцию.​
​ можно выделить с​

Как преобразовать текст в число в Excel и наоборот

​ если программа по​ значения в исходный​ действия.​ сначала удалять пробелы,​ Более подробно можно​. Таким образом, делая​СЦЕП​ формате «Денежный». Кроме​ В Excel существует​0012345556789​ префиксом и остальными​ удается, можно воспользоваться​ Query)​ тому же, который​ с помощью формулы​ Нажмите значок функции​

Число в текстовом формате: причины и проблемы

​ помощью клавиши Ctrl,​ каким-то причинам не​ столбец. Вот как​Выделите столбец с такими​ а затем преобразовывать​ почитать здесь. После​ замену запятой на​Функция ТЕКСТ​ того, если в​ несколько способов для​=ТЕКСТ(A1,»00-0-000-000-0000″)​ цифрами.​ мастером импорта текста.​. В этом случае​ достаточно трудно найти.​

​ =ЛЕВСИМВ(A1;3) извлекли число​ fx под панелью​ графу или запись​ воспринимает числа как​ это сделать: Выделите​ ячейками. Если вы​ формат ячеек​ преобразования полученный столбец​ запятую, мы имитируем​Функция TEXTJOIN​ ячейке находится положительное​ объединения текста и​00-1-234-555-6789​

​Цифровой код​ В этом случае​ импортируется текстовый файл,​ Подсказкой может служить​ 101. Число 101​ инструментов.​ выделяют, кликнув мышкой​ числа, считая их​ ячейки с новой​ не хотите преобразовывать​andy1618​ можно скопировать и​ редактирование ячейки аналогично​Достаточно часто в Excel​ значение (или 0),​ чисел.​Почтовый​Пример​ импортируется текстовый файл,​

​ однако данные проходят​

​ выравнивание значения в​

​ будет сохранено в​

​В открывшемся окне в​

​ на заголовке. Чтобы​

​ текстом. Это может​

​ формулой. Нажмите клавиши​

​ весь столбец, можно​

​: Вопрос очень простой​

​ вставить как значения​

​ выгружаются данные из​

​ после него будет​

​Если столбец, который вы​

​Пользовательский числовой формат​

​ однако данные проходят​ те же этапы​ ячейке: если значение​ текстовом формате, т.к.​ строке поиска введите​

Исправление ошибки

​ выделить весь лист,​ произойти при копировании​ CTRL+C. Щелкните первую​ выбрать одну или​ (возможно, ламерский):​ на место исходных​Подобную операцию можно осуществить​ сторонних программ для​ показан текст «(излишек)»,​ хотите отсортировать содержит​00123​Номер социального​ те же этапы​ преобразования, что и​ выровнено по правой​ эта функция возвращает​ текст «значен» и​

​ нужно нажать на​ данных из другого​ ячейку в исходном​ несколько ячеек. Ячейки​есть колонка, где​ данных​ и с датами​ их дальнейшей обработки,​ а если ячейка​ числа и текст​=ТЕКСТ(A1,»000000″)​страхования​ преобразования, что и​ при импорте из​ стороне, то это​

​ только текстовые строки.​ найдите функцию «ЗНАЧЕН».​ соединение заголовков записей​ источника – другой​ столбце. На вкладке​ должны находиться в​ содержатся числа, но​С помощью макросов​ с тем лишь​ и нередко дальнейшее​ содержит отрицательное значение,​ — 15 #​001234​012345678​ при импорте из​ других источников, таких​ число (или дата),​ Это дальнейшем может​

Использование специальной вставки

​ В параметрах функции​ и граф в​ программы, интернет-странички и​Главная​ одном и том​ в текстовом виде​. Собственно, любой из​ отличием, что менять​ использование этих данных​ после него будет​ продукта, продукт #100​Максимальная точность чисел в​

​000-000-0000 ​ других источников, таких​ как XML, Интернет,​ а если по​ привести к ошибке.​ укажите нужную ячейку​ верхнем левом углу​ т. д. Проблема​щелкните стрелку рядом​ же столбце, иначе​

​ (т.е. формат ячеек​ перечисленных способов можно​ нужно точку на​ в формулах даёт​ показан текст «(недостаток)».​ и 200 #​ Excel составляет 15​012-345-6789 ​ как XML, Интернет,​ JSON и т. д.​ левой, то текст​ Например, при сложении​ и нажмите «ОК».​ страницы Excel.​ также может появиться​

​ с кнопкой​ этот процесс не​ — текстовый). Вопрос​ выполнить макросом. Если​ точку.​ непредсказуемый результат: числа​При объединении чисел и​ продукта — не​ значащих цифр. Это​

Использование формул и макросов

​Телефон​ JSON и т. д.​Откройте вкладку​ (подразумевается, что в​ функцией СУММ() такие​ В соседней с​Рядом с выделенным диапазоном​ при импорте информации​Вставить​ будет работать. (Если​ — как преобразовать​ Вам приходится часто​Кроме того, сторонние программы​ не суммируются, между​ текста в ячейке,​ может сортировать должным​ означает, что в​

​0012345556789​На ленте откройте вкладку​Данные​ Формате ячеек во​ значения попросту игнорируются​ искомой ячейкой вы​ полей появится значок​ из другого файла​и выберите пункт​ такие ячейки есть​ эти строки в​ выполнять подобное преобразование,​ могут выгружать числа​ датами невозможно рассчитать​ текст и больше​ образом. Можно отформатировать​ любом числе, состоящем​00-0-000-000-0000 ​Power Query​, нажмите кнопку​ вкладке Выравнивание в​ (см. статью Функция СУММ()​

​ получите число вместо​ – кнопка с​ или БД.​Специальная вставка​ в нескольких столбцах,​ реальные числа? Простое​ то имеет смысл​ с точкой в​ количество дней и​ не функция числа​ ячейки, содержащие 15,​ из 16 и​00-1-234-555-6789 ​

​Получить данные​ поле Выравнивание по​

​ и операция сложения),​

Обратная задача

​ текста. Чтобы скопировать​ восклицательным знаком, указывающий​Обратите внимание на то,​>​ см. раздел «Другие​ преобразование формата ячеек​ написать макрос и​ качестве десятичного разделителя,​ т.д.​ становятся как числовые​ 100 и 200,​ более цифр, например​Почтовый​

​Получение внешних данных​и выберите вариант​ горизонтали указано По​ так же как​ формулу на все​ на наличие ошибки.​ как выглядит поле​Значения​ способы преобразования» ниже.)​ в «General» или​ запускать его по​

​ тогда поможет замена​В этой статье рассматриваются​ значения. Это означает,​ чтобы они отображались​ в номере кредитной​индекс​ >​Из текстового/CSV-файла​ значению).​ и при подсчете​

​ поля столбца, кликните​

Преобразование в MS EXCEL ЧИСЕЛ из ТЕКСТового формата в ЧИСЛОвой (Часть 1. Преобразование формулами)

​ При нажатии на​ с цифрой. Если​.​Кнопка «столбцы» обычно применяется​ «Number» не помогает​ мере необходимости.​ точки на запятую.​ причины появления таких​

​ что больше не​​ на листе как​​ карты, все цифры,​00123​Из текста​. Если вы не​Примечание​ функцией СЧЁТ(), СУММЕСЛИ() и​ мышкой в правом​ кнопку появляется меню,​ в уголке поля​Если указанные выше действия​ для разделения столбцов,​ — Excel по-прежнему​Приведу два примера макросов:​Аналогичную замену можно проделать​ проблем и различные​ может выполнять любые​ 15 # продукт,​ следующие после 15-й,​000000 ​

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

​ первым пунктом которого​ вы видите маленький​ не дали результатов,​ но ее также​ трактует их как​1) умножение на 1​ и формулой (см.​ способы их устранения​ математических операций на​ продукт #100 и​ округляются до нуля.​001234 ​Excel загрузит данные в​Получить данные​ ячеек (например, «101​

​Исправить формат возвращаемого значения​ содержащей ее, получив​ показано, какая именно​ зеленый треугольник, это​ попробуйте использовать этот​​ можно использовать для​​ строки (в частности,​Sub conv() Dim c​​ ниже), используя функцию​​Причина первая​ них.​​ 200 # продукта.​​ Если цифровые коды​Инструкции​​ область предварительного просмотра.​​, выберите​ далматинец») по различным​ можно добавив функцию ЗНАЧЕН(): =ЗНАЧЕН(ЛЕВСИМВ(A1;3)).​​ курсор в виде​​ проблема в выделенном​ значит, что числовая​ метод, который подходит,​ преобразования столбца текста​ не вычисляет сумму​ As Range For​

​ ПОДСТАВИТЬ()​. Число сохранено как​Для объединения чисел с​Использование пользовательского числового формата​ состоят из 16​ ​ В области предварительного​Создать запрос​ столбцам с помощью​ Функция ЗНАЧЕН() преобразует​ изображения крестика, зажмите​ участке листа («Число​ информация в нем​ если вы хотите​ в числа. На​​ при выделении группы​​ Each c In​С помощью Специальной вставки​ текст​ помощью функции СЦЕПИТЬ​

​ для отображения номера​

​ и более цифр,​
​Выделите ячейку или диапазон​ просмотра нажмите кнопку​ >​ инструмента Текст-по-столбцам (на​ значение, где это​ его и проведите​ сохранено как текст»).​ введена как текстовая.​ преобразовать несколько столбцов​ вкладке​ ячеек и т.п.).​ Selection If IsNumeric(c.Value)​. Этот способ более​В этом случае видно,​ или функции ОБЪЕДИНЕНИЯ,​ с текстом, не​ необходимо использовать текстовый​ ячеек, которые вы​Изменить​

​Из файла​​ вкладке Данные в​ возможно, в число.​ вниз по всей​ Нажмите пункт «Преобразовать​ Описанная выше проблема​ текста.​Данные​Пока что знаю​ Then c.Value =​ универсальный, так как​ что числа или​ текст и TEXTJOIN​ изменяя порядок сортировки​ формат. Для этого​

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

​ хотите отформатировать.​​, чтобы загрузить​ >​ группе Работа с​Альтернативным решением является использование​ длине. Формула появится​ в число». Зеленые​ при попытке произвести​Выделите пустую ячейку, в​нажмите кнопку​ три кривых способа:​ c.Value * 1​ работает и с​ даты прижаты к​ и амперсанд (&)​ число. Таким образом​ выполните одно из​Нажмите клавиши​Редактор запросов​Из текста​ данными пункт Текст-по-столбцам)​ формулы =ЛЕВСИМВ(A1;3)+0 или​

​ по всей графе.​ индикаторы ошибки сразу​ вычисления также говорит​ которой нет этой​Текст по столбцам​1) воспользоваться листовыми​ c.NumberFormat = «#,##0.00″​ дробными числами, и​ левому краю ячейки​ оператор.​ изменение способа отображения​ указанных ниже действий.​CTRL+1​.​, найдите нужный файл​ проблем с определением​ =—ЛЕВСИМВ(A1;3) или =ЛЕВСИМВ(A1;3)*1.​Тем, кто знаком с​ исчезнут, и над​ об этом.​ проблемы, введите в​.​ функциями (типа VALUE),​ End If Next​ с целыми, а​ (как текст) и,​

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

​, чтобы открыть диалоговое​Если какие-либо столбцы нужно​ и нажмите кнопку​ формата не возникает:​ Два подряд знака​​ программированием, поможет преобразовать​ числами можно будет​​Еще одна проблема при​ нее цифру​Остальные шаги мастера нужны​ преобразовав текст в​ End Sub​ также с датами.​ как правило, в​ ​ значения.​

​ текста​​ окно​​ преобразовать в текст,​​Импорт​​ если значение может​​ минус дают +​​ текст в число​ проводить необходимые действия.​​ хранении цифр как​​1​​ для разделения текста​​ числа в отдельной​​2) текст по столбцам​​ Выделяем любую пустую​​ левом верхнем углу​​В Excel 2016Excel Mobile и​Выполните следующие действия.​​Выделите диапазон данных и​​Формат ячеек​

​ выделите их, щелкнув​.​ быть преобразовано в​ и заставляют EXCEL​​ Excel VBA. Этот​​Еще один способ, помогающий​​ строк возникает при​​и нажмите клавишу​

​ на столбцы. Так​ колонке и скопировав​Sub conv1() Selection.TextToColumns Selection.NumberFormat​ ячейку — выполняем​​ ячейки имеется маркер​​ Excel Online с​​Выделите ячейки, формат которых​​ нажмите клавиши​​.​​ заголовок, затем выберите​​Excel загрузит данные в​​ числовой формат, то​

​ попытаться сделать вычисления​​ встроенный язык предлагает​ преобразовать текст в​ попытке их сортировки.​ ВВОД.​​ как нам нужно​​ потом эти значения​

​ = «#,##0.00» End​​ команду​​ ошибки (зелёный треугольник)​​ помощью функции​​ требуется изменить.​CTRL+1​Откройте вкладку​

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

​ число в Excel,​ Возьмем для примера​Нажмите клавиши CTRL+C, чтобы​​ только преобразовать текст,​​ обратно в исходную​​ Sub​​Копировать​ и тег, который​ОБЪЕДИНЕНИЯ​

​На вкладке​, чтобы открыть диалоговое​Число​ >​ В области предварительного​Примечание:​ функцией ЛЕВСИМВ(). В​ данных. Для целых​ — использование спецвставки.​ ряд чисел: 10,​ скопировать ячейку.​ нажмите кнопку​ колонку​Причина вторая​— выделяем диапазон​ при наведении мышки​заменена​Главная​ окно​и в списке​Преобразовать​ просмотра нажмите кнопку​

​Мы стараемся как​​ случае успеха, если​​ чисел подойдет операция​​ В одну из​​ 211, 15, 1021,​​Выделите ячейки с числами,​​Готово​

​2) массовая посимвольная​. В записи числа​ с проблемными числами​ поясняет, что число​​функции СЦЕПИТЬ​​в группе​​Формат​​Числовые форматы​

​ >​Изменить​ можно оперативнее обеспечивать​ функцией ЛЕВСИМВ() действительно​​ CInt, для дробных​​ свободных ячеек внесите​​ 26, 100, 128.​​ которые сохранены как​​, и Excel преобразует​​ замена командой Find​​ присутствуют посторонние символы.​​ —​

​ сохранено как текст.​​. Несмотря на​число​ >​выберите вариант​​Тип данных​​, чтобы загрузить​

​ вас актуальными справочными​​ было возвращено число,​​ – CDbl. Ниже​​ число 1, предварительно​​ Их сортировка не​ текст.​ ячейки.​

​ and Replace (1​​Чаще всего этими посторонними​​Специальная вставка​Никакие изменения формата на​ то, что функция​

​щелкните стрелку.​Ячейки​(все форматы)​​ >​​Редактор запросов​​ материалами на вашем​​ у значения будет​ показан пример использования​ установив формат как​

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

​ представляет трудности, и​На вкладке​Нажмите клавиши CTRL+1 (или​ на 1, 2​ символами являются пробелы.​—​ Числовой, Общий или​СЦЕПИТЬ​В списке​. На вкладке​, а затем в​Текст​.​ языке. Эта страница​ изменен формат на​ VBA для нашего​ общий: на вкладке​ программа легко с​Главная​+1 на Mac).​ на 2, . ​ Они могут располагаться​Сложить​ Дата ситуацию не​

​по-прежнему доступен для​

​Если какие-либо столбцы нужно​

​ переведена автоматически, поэтому​
​ числовой.​

​ «Главная» программы, панель​

​ ней справляется. Но,​
​щелкните​

​ Выберите нужный формат.​
​ 9 на 9).​

​ как внутри числа​

​—​
​ исправляют, но если​

​ обеспечения обратной совместимости,​​выберите категорию, например​

​ преобразовать в текст,​​ ее текст может​​Этот вариант позволяет преобразовывать​Sub TestModule()​​ инструментов «Число» показывает​​ если эти числа​

​Вставить​​Примечание:​​ Видать, Excel это​​ в качестве разделителя​​ОК​​ кликнуть в строку​​ следует использовать​настраиваемые​​Текстовый​​введите формат числа,​ Чтобы выбрать несколько столбцов,​​ выделите их, щелкнув​​ содержать неточности и​ не только в​​Cells(1, 12) = CInt(Cells(1,​​ формат выделенного поля.​ записаны в строковом​

​и выберите пункт​​ Если вы по-прежнему видите​​ считает редактированием и​​ разрядов, так и​​. Таким образом, мы​​ формул (или нажать​​ОБЪЕДИНЕНИЯ​​и нажмите кнопку встроенный​​.​​ например​​ щелкните их левой​​ заголовок, затем выберите​

​ грамматические ошибки. Для​ числовой формат, но​ 11))​Скопируйте единицу. Для этого​ формате, сортировка будет​

​Специальная вставка​​ формулы, которые не​ делает требуемое преобразование.​ до/после числа. В​ к числам (или​ F2), а затем​, так как​ формат, который похож​

Применение функции ТЕКСТ для форматирования

​Примечание:​000-00-0000​ кнопкой мыши, удерживая​Главная​ нас важно, чтобы​ и формат даты.​

​ сделайте на ней​

​ кардинально отличаться от​.​

​ выводят числовые результаты,​
​3) VBA​

​ этом случае, естественно,​

​ датам) прибавляем 0,​
​ Enter, то число​

​ Этот способ не позволяет​
​для кода социального​

​ эта статья была​

​ Введем в ячейку​
​Иногда стоит обратная задача​

Округление номеров кредитных карт

​ правый клик и​ привычной и необходимой.​Щелкните​ возможно, включен параметр​А нет ли​ число становится текстом.​ что никак не​ становится числом, а​могут быть недоступны​ вы хотите.​ изменить числа, которые​ страхования или​CTRL​Преобразовать​ вам полезна. Просим​А1​ – преобразовать в​ выберите команду «Копировать»​

​ Эта разница показана​умножить​

​Показать формулы​ простого элементарного способа?​​Убрать лишние пробелы также​​ влияет на их​ дата датой. При​​ в будущих версиях​​В поле​​ были введены ранее.​​000000​​.​​ >​​ вас уделить пару​​текст «1.05.2002 продажа»,​

​ Excel число в​​ из меню. Второй​ в таблице:​и нажмите кнопку​. Откройте вкладку​ЗЫ. Excel 2000,​ можно с помощью​

​ значение, зато переводит​

​ большом количестве таких​ Excel.​​Тип​​ Он действует только​для шестизначного почтового​В диалоговом окне​

Объединение текста и чисел

​ секунд и сообщить,​​ в ячейку​ текст прописью. Эта​ способ – кнопка​Сортировка в числовом формате​ОК​Формулы​ English​ операции​ в числовой формат​ чисел вариант, согласитесь,​TEXTJOIN​измените коды числовых​ на числа, введенные​ индекса.​Изменение типа столбца​ >​ помогла ли она​B1​ проблема так же​ на панели инструментов​Сортировка в текстовом формате​

​. Excel умножит каждую​и отключите параметр​//X-Posted in LJ​Найти/Заменить​Вариантом этого приёма может​ неприемлемый.​Объединение текста из​ форматов в формате,​ после применения формата.​Совет:​выберите команду​Текст​ вам, с помощью​формулу =ЛЕВСИМВ(A1;9), а​

Используйте числовой формат для отображения текста до или после числа в ячейке

​ решаема, хоть и​ «Буфер обмена». И​10​ ячейку на 1,​Показать формулы​Николай​. В поле​ быть умножение диапазона​Есть несколько способов решения​ нескольких диапазонах и/или​ который вы хотите​Использование апострофа​ Можно также выбрать формат​Заменить текущие​.​ кнопок внизу страницы.​

​ в ячейку​ не так просто,​ третий – горячие​10​ при этом преобразовав​.​: В любую пустую​Найти​

​ данной проблемы​ строки, а также​

​ создать.​​Перед числом можно ввести​​Дополнительный​​, и Excel преобразует​​Совет:​

​ Для удобства также​​C1​​ как предыдущая. Существуют​​ клавиши Ctrl+C.​​15​ текст в числа.​С помощью функции ЗНАЧЕН​ ячейку вписать 1.​

​вводим пробел, а​​С помощью инструмента Текст​​С помощью маркера ошибки​ разделитель, указанный между​Для отображения текста и​ апостроф (​

​, а затем тип​ выделенные столбцы в​ Чтобы выбрать несколько столбцов,​ приводим ссылку на​формулу =B1+0. В​ специальные надстройки, которые​Выделите диапазон чисел в​100​
​Нажмите клавиши CTRL+1 (или​​ можно возвращать числовое​ Выделить ее и​ поле​

​ по столбцам​

​ каждой парой значений,​

​Почтовый индекс​ текст.​ щелкните их левой​ оригинал (на английском​ итоге, в​ добавляются в программу.​ текстовом формате, который​26​

​+1 на Mac).​ значение текста.​

​ скопировать. Выделить все​

​Заменить на​. Этот приём удобно​. Если в левом​ который будет добавляться​ заключите текст в​

​), и Excel будет​,​По завершении нажмите кнопку​

​ кнопкой мыши, удерживая​

​ языке) .​B1​ Затем для изменения​ собираетесь приводить к​1021​ Выберите нужный формат.​Вставьте столбец рядом с​ ячейки с «псевдочислами»​оставляем пустым, далее​ использовать если преобразовать​ верхнем углу ячеек​

Объединение текста и чисел из разных ячеек в одной ячейке с помощью формулы

​ текст. Если разделитель​ двойные кавычки (»​ обрабатывать его как​Индекс + 4​Закрыть и загрузить​ нажатой клавишу​Вам когда-нибудь приходилось импортировать​получим 1.05.2002 в​ отображения чисел вызываются​ числовому. На той​

​100​Можно сделать так, чтобы​ ячейками, содержащими текст.​ и в меню​Заменить все​ нужно один столбец,​

​ виден маркер ошибки​​ пустую текстовую строку,​

​ «), или чисел​ текст.​,​​, и Excel вернет​​CTRL​​ или вводить в​​ текстовом формате, а​ функции.​​ же панели «Буфер​​128​ числа, хранящиеся как​ В этом примере​​ Правка — Специальная​​. Если в числе​​ так как если​​ (зелёный треугольник) и​ эта функция будет​ с помощью обратной​

​К началу страницы​​Номер телефона​ данные запроса на​.​ Excel данные, содержащие​ в​Еще один способ –​ обмена» нажмите «Вставить»​128​ текст, не помечались​ столбец E содержит​​ вставка — Умножить.​​ были обычные пробелы,​ столбцов несколько, то​ тег, то выделяем​

Примеры

​ эффективно объединять диапазоны.​ косой черты (\)​

​Примечание:​или​​ лист.​​В диалоговом окне​ начальные нули (например,​С1​ использование формул. Он​ / «Специальная вставка».​15​​ зелеными треугольниками. Выберите​​ числа, которые хранятся​ И все будет​ то этих действий​ действия придётся повторять​ ячейки, кликаем мышкой​TEXTJOIN​ в начале.​Мы стараемся как​Табельный номер​Если в дальнейшем ваши​Изменение типа столбца​ 00123) или большие​​получим уже обычную​​ значительно более громоздкий,​

См. также

​ Откроется окно для​

Число сохранено как текст или Почему не считается сумма?

​ ОК!​ будет достаточно. Но​ для каждого столбца​ по тегу и​в Excel 2013​Примечание:​ можно оперативнее обеспечивать​.​ данные изменятся, на​выберите команду​ числа (например, 1234​ дату «01.05.02», точнее​

​ так как подразумевает​ ввода параметров. В​211​>​

​ Столбец F является​​andy1618​ в числе могут​

​ отдельно. Итак, выделяем​ выбираем вариант​ и более ранние​изменение встроенного формата​ вас актуальными справочными​Дополнительные сведения о пользовательских​ вкладке​Заменить текущие​ 5678 9087 6543)?​ число 37377 (чтобы​ создание пользовательских справочников​ нем отметьте точкой​1021​

​Параметры​ новым столбцом.​: Ага, спасибо за​ встречаться так называемые​ столбец с числами​Преобразовать в число​ версии не поддерживается.​ не приводит к​ материалами на вашем​ кодах см. в​Данные​, и Excel преобразует​ Это могут быть​

​ увидеть дату, формат​ – именованных диапазонов​

• ​ элемент «умножить» блока​26​​>​В одной из ячеек​ действительно эффективное решение. ​ неразрывные пробелы (символ​ или датами, сохраненными​С помощью операции Найти/Заменить​Примеры различных на рисунке​ удалению формат.​​ языке. Эта страница​

• ​ статье Создание и​​нажмите кнопку​ выделенные столбцы в​ номера социального страхования,​ ячейки нужно установить​ в диспетчере имен.​ «Операция» и нажмите​​Что же делать в​​Формулы​ нового столбца введите​​В ru_msoffice тоже​​ с кодом 160).​​ как текст, устанавливаем​​. Предположим, в таблице​ ниже.​​Для отображения​​ переведена автоматически, поэтому​​ удаление пользовательских числовых​​Обновить​​ текст.​​ телефонные номера, номера​ Дата).​​ Затем эти имена​​ «ОК» — текст​​ этом случае и​​и снимите флажок​​=ЗНАЧЕН()​​ предложили фактически аналогичный​ Такой пробел придётся​ формат ячейки​ есть числа с​​Внимательно посмотрите на использование​

​Используйте код​ ее текст может​ форматов.​, и Excel их​По завершении нажмите кнопку​ кредитных карт, коды​

​Некоторые программы бухгалтерского учета​ используются в формуле​ Excel преобразуется в​ как преобразовать текст​Числа в текстовом формате​и укажите в​

​ способ — выделить​ скопировать прямо из​Общий​ десятичной запятой, сохраненные​

• ​ функции​​Принцип действия​ содержать неточности и​Примечание:​ автоматически обновит и​Закрыть и загрузить​ продуктов, номера счетов​ отображают отрицательные значения​ на основе функции​ число.​​ в число в​​.​ скобках ссылку на​ ячейку с 0​​ ячейки, а затем​​(для чисел можно​​ как текст. Выделяем​​текст​​12 как Продукт №12​​ грамматические ошибки. Для​ Этот способ не позволяет​ преобразует.​, и Excel вернет​ или почтовые индексы.​ со знаком минус​ «ПОДСТАВИТЬ».​

​Ту же операцию можно​ Excel?​Замена формулы ее результатом​

• ​ ячейку, содержащую числа,​ (или вообще пустую​​ вставить в поле​ установить, к примеру,​ диапазон с числами​во втором примере​»Продукт № » 0​ нас важно, чтобы​ восстановить начальные нули,​Если эту задачу необходимо​ данные запроса на​ Excel автоматически удаляет​ (-) справа от​Какой бы способ вы​​ произвести с помощью​​Как уже было указано​Удаление пробелов и​​ которые хранятся в​​ с нормальным форматом)​​Найти​​Числовой​​ — нажимаем​​ на рисунке. При​​Текст, заключенный в кавычки​​ эта статья была​​ которые были удалены​

• ​ решить в пределах​​ лист.​ начальные нули и​ значения. После копирования​ ни выбрали, чтобы​ контекстного меню. Выделив​ выше, на проблему​ непечатаемых знаков из​ виде текста. В​ и сделать Paste​диалогового окна​или​Ctrl+h​ присоединении к числа​ (включая пробел) отображается​ вам полезна. Просим​ до форматирования. Он​ книги, поскольку она​Если в дальнейшем ваши​

• ​ преобразует большие числа​​ в EXCEL они​ в Excel преобразовать​ нужный диапазон, кликните​ указывает зеленая отметка​ текста​ данном примере это​ Special -> Add.​Найти/Заменить​Финансовый​

​(либо находим на​

​ в строку текста​

​ в ячейке перед​ вас уделить пару​ действует только на​ не используется другими​ данные изменятся, на​ в экспоненциальное представление​ сохраняются как текстовые​ текст в число,​ правой кнопкой мыши,​

​ПЕЧСИМВ (функция ПЕЧСИМВ)​ ячейка​Теперь мучениям конец!​

​. Либо можно в​​). Далее выполняем команду​ вкладке​

​ с помощью оператор​ числом. В этом​ секунд и сообщить,​ числа, введенные после​ программами в качестве​ вкладке​ (например, 1,23E+15), чтобы​ значения. Чтобы преобразовать​ применив его на​

​ выберите «Специальная вставка»​ Такая пометка может​При работе с MS​​E23​​ :)​​ поле​​Данные​Главная​​ объединения, используйте функцию​​ коде «0» обозначает​​ помогла ли она​​ применения формата.​ источника данных, для​Данные​ их можно было​ эти текстовые значения​ практике несколько раз,​ / «Специальная вставка».​ быть на единичных​ Excel вы, возможно,​.​Николай Павлов​Найти​​—​​или в меню​​текст​​ число, которое содержится​ вам, с помощью​​Для преобразования данных в​​ сохранения начальных нулей​​нажмите кнопку​​ использовать в формулах​ в числа, необходимо​

​ вы приспособитесь делать​ Откроется то же​

​ полях, целом диапазоне,​​ не раз сталкивались​

​Теперь нужно заполнить формулу​​: :)​

​нажать сочетание клавиш​Текст по столбцам​​Правка​

​, чтобы управлять​ в ячейке (например,​ кнопок внизу страницы.​ нужный формат можно​ можно использовать пользовательский​Обновить​

Как преобразовать текстовый формат ячейки в числовой?

​ и математических операциях.​​ извлечь из него​ это быстро и​
​ окно.​ столбце или записи.​ с ошибкой при​ вниз. Вот как​Если часто приходится​Alt+0160​—​для версий до​ способом отображения чисел.​ 12).​ Для удобства также​ использовать расположенный рядом​ или специальный формат.​, и Excel их​ В этой статье​ все цифры, и​ эффективно, получая нужный​
​Еще один способ преобразовать​ Программа самостоятельно определяет​
​ попытке произвести арифметические​ это сделать: Наведите​ это делать -​(цифры набираются на​Готово​ 2007 команду​ В формуле используется​12:00 как 12:00 центральноевропейское​
​ приводим ссылку на​ с ними пустой​ Этот способ подходит​ автоматически обновит и​ объясняется, как сохранить​ умножить результат на​ результат.​ текст в число​ несоответствие между форматом​
​ действия над числами.​

​ указатель на правый​ можно сделать макрос​

​ цифровой клавиатуре).​С помощью формул​
​Заменить​

​ базовое значение из​​ время​ оригинал (на английском​ столбец и функцию​ для цифровых кодов,​ преобразует.​ данные в исходном​ -1. Например, если​Автор: Татьяна Казмирчук​ в Excel –​ ячейки и значением,​

​ Программа почему-то не​​ нижний угол ячейки,​ + в PLEX​
​Пробелы можно удалить и​. Если таблица позволяет​) — в поле​ ячейки, на который​ч:мм «центральноевропейское время»​ языке) .​ ТЕКСТ.​ содержащих менее 16​
​В Excel 2010 и​ формате, который Excel​

​ в ячейке​​Если в ячейке числовые​

​ формула. Вставьте рядом​ которое в нее​ выполняет вычисления и​ чтобы он принял​ такая функция есть:​

Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel ⁠ ⁠

Друзья, всем привет. В комментариях, да и на занятиях, довольно часто поднимается тема преобразования чисел, сохранённых как текст, в рабочий числовой формат, поэтому решил про это написать. Как обычно, здесь не будет никаких откровений и чего-то нового. Так или иначе, всё то, о чём напишу, уже давно есть в интернете. Просто решил собрать всё в одном месте. Может, кому-то будет полезно (я на это очень надеюсь).

Небольшое вступление. Чего рассматривать не будем, и почему такое вообще происходит.

Сразу оговорюсь, что не буду здесь рассматривать случаи, когда в ячейках кромешный ад и вакханалия вроде «10 руб», «33 попугая» и так далее. Хотя один способ может и с такими ячейками помочь. Будем разбирать православные ячейки, в которых только число, но, по тем или иным причинам, оно сохранено как текст.

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

Excel любезно даёт понять, что число сохранено как текст, добавляя в такие ячейки зелёный треугольник в левый верхний угол (не касается дат, сохранённых как текст, такие ячейки никак не выделяются). Интересный момент: если суммировать ячейки вручную, то результат будет нормальным.

Вся загвоздка в том, что если пойти путём обычной смены формата на числовой (Главная — Число — в выпадающем списке выбрать Числовой), результата это не даст. Придётся взять бубен и немного потанцевать с ним.

И так, что же можно сделать.

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

Способ 1. Использовать инструмент «Текст по столбцам» (text to columns) . Выделяем все «проклятые» ячейки, идём на вкладку Данные — Работа с данными — Текст по столбцам, и на самом первом шаге нажимаем Готово.

Способ 2. Замена разделителя на аналогичный. Честно скажу, когда рассказываю про этот способ, почти все говорят «Да какого. Да почему!? Да что с этой программой не так?!». Да чего уж, признаюсь, что без улыбки на лице про него сам не могу рассказывать. Если в ячейках дробные числа, то преобразовать можно с помощью банальной замены запятой на. Барабанная дробь. ЗАПЯТУЮ! Выделяем данные, запускаем поиск и замену (CTRL+ H), ищем запятую, меняем на запятую. Этот способ также подходит для дат, которые сохранены как текст, только вместо запятой заменять нужно разделитель, который указан в ячейках (точка, слэш, дефис).

Способ 3. С помощью специальной вставки. Довольно интересный способ, так как знакомит нас со специальной вставкой — очень полезным инструментом. Смысл в том, что для преобразования текстовых чисел в нормальные числа с ними нужно произвести какое-то математическое действие. Но нужна такая операция, которая не меняет самого числа, а это, например, умножение на единицу (есть более экзотические, прибавить или вычесть ноль, но их рассматривать не будем). Алгоритм следующий:

В произвольную ячейку пишем единицу (1)

Копируем эту ячейку (Ctrl + C)

Выделяем диапазон с «кривыми» числами

По любой выделенной ячейке щёлкаем правой кнопкой мыши (либо Ctrl + Alt + V)

В контекстном меню выбираем Специальная вставка (Paste special)

В следующем окне выбираем «Умножить». Если в таблице есть какое-то оформление, то ещё желательно выбрать «Значения», чтобы это самое оформление не слетело

Способ 4. Функция ЗНАЧЕН (VALUE). Если планируете делать некий шаблон, в который будете копировать текстовые числа, а на выходе получать нормальные, то можно воспользоваться функцией, которая как раз и занимается преобразованием. Если работаете с датами, то нужна функция ДАТАЗНАЧ (DATEVALUE).

Способ 5. Бинарное отрицание. Хотите быть не как все? Хотите, чтобы коллеги подходили к вам с вопросом «А что это такое тут у тебя формуле?». Тогда этот способ для вас! Бинарное отрицание, если рассматривать его в контексте нашего вопроса, умножает число на -1, а потом ещё раз на -1. То есть мы производим математическую операцию, которая не меняет самого числа. Нужно просто перед ссылкой на ячейку поставить два знака минус (-). Вполне можно использовать при создании шаблона вместо функции.

Способ 6. Excel спешит на помощь. Вообще, если нажать на смарт-тэг (знак «дорожные работы») с ошибкой, то программа сама предложит преобразовать текст в число:

То есть можно выделить диапазон с такими вот ячейками, потом нажать на смарт-тэг, выбрать «Преобразовать в число», и всё сработает как надо. Но должен предупредить, что с большим количеством ячеек способ может работать довольно долго. Особенно если в книге много всего другого (формулы, листы, связи и т.д.).

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

К своему стыду (ладно, вру, мне не стыдно), я не погружался прям глубоко в то, почему так происходит. Для себя я придумал версию, что пробелы бывают разные. Так вот, чтобы разобраться с таким недоразумением, нужно скопировать пробел из самой ячейки, выделить ячейки, которые хотим преобразовать, потом открыть окно поиска и замены, вставить скопированный пробел в поле «Найти», а заменить на пусто.

Всесильный Power Query.

Долго думал, стоит ли включать сюда этот способ, и всё-таки решил включить. Часто в комментариях пишут, что а вот это можно с помощью PQ сделать, а вот то вообще на раз-два. И ведь это правда. PQ — это безумно крутая штука. Почему боюсь про него писать? По одной простой причине: более менее интерфейс PQ устаканился, начиная с 2019 версии. Для 2010 и 2013 вообще надо отдельно скачивать и устанавливать (да, это просто и занимает всего пару минут, но всё же). В 2016 версии сразу из коробки идёт, но выглядит чуть иначе. И вот я сейчас покажу, как это делается, а кто-то потом напишет, что у него этого нет, а это по-другому выглядит, и вообще автор — кАзёл. И всё же 🙂 Собрал все случаи, про которые писал. Дальше:

Желательно преобразовать таблицу в «умную» (не сделаете сами, всё равно потом Excel это сделает за вас)

Далее вкладка Данные — Получить и преобразовать данные — Из таблицы/диапазона

Если настройки PQ не меняли, то автоматически будет применён шаг «Изменённый тип», который всё и сделает

В PQ на вкладке Главная нажимаем Закрыть и загрузить

Получаем на новом листе «умную» таблицу с правильными форматами.

На этом, пожалуй, простыню свою завершу. Возможно, я рассказал не про все способы борьбы с текстовыми числами/датами. В комментариях делитесь своими, с удовольствием про них почитаю. Если спросите, зачем так много способов, то ответ будет так себе. Для разнообразия, конечно 🙂 Когда какой использовать — дело вкуса, привычки и исходных данных. В большинстве случаев все способы взаимозаменяемы.

Как всегда, всем огромное спасибо, кто потратил своё драгоценное время и внимание на прочтение данного материала. Надеюсь, было полезно и что-то из статьи поможет вам в работе.

689 постов 15K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

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

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Умножение на 1 или плюсование с нулём уже на автомате идёт

Минутка бесполезной информации.

«Не такой пробел» — это неразрывный пробел. В ворде он вставляется сочетанием ctrl+shift+пробел. Особенность в том, что не разрывает число на две строки, если строка заканчивается.

В excel эта информация никак не поможет, поэтому способ «скопировать-вставить» — наше все.

Копи-паст такие ячейки в текстовый файл и обратно в эксель через импорт данных! ✌️

7 интересных приёмов при работе со сводной таблицей⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про несколько полезных и интересных (с моей скромной и субъективной точки зрения) трюков при работе со сводными таблицами. Здесь не будет подробного разбора про работу в сводных таблицах: что это, для чего и почему. Материал предназначен для тех, кто уже хоть как-то знаком с этим прекрасным инструментом и знает, что это за зверь такой. Если же вы ещё не работали со сводными таблицами, при этом проводите много времени в Excel и строите разного рода отчёты, аналитику, то я вам настоятельно рекомендую как можно скорее освоить этот поистине чудесный инструмент. Поверьте, вы откроете для себя абсолютно новый мир 🙂

1 — Фильтры для полей сводной, которые по умолчанию фильтровать нельзя.

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

Но если нельзя, но очень хочется, то можно.

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

2 — Изменение порядка элементов в списке.

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

Решение: выбираем ячейку с Баранкиным и вероломно начинаем печатать фамилию Чайниковой. Целиком печатать не нужно, программа сама предложит готовый вариант. Нажимаем Enter. Вуаля! Теперь Чайникова на вершине нашего списка:

Ещё один способ: это обычное копирование и вставка. Выбираем ячейку с Чайниковой, копируем её. Далее выбираем ячейку с Баранкиным (место, куда хотим переместить наше значение), и вставляем.

3 — Использование собственных списков для сортировки.

Прошлый способ хорош, если нужно сделать это однократно. Но если у нас уже есть какой-то устоявшийся порядок, он вот совсем не по алфавиту, а бездушный Excel всегда всё сортирует именно по алфавиту?

Решение: многие знают, что если мы напишем в ячейке «январь» («февраль», «март» и т.д.), потом протащим ячейку за правый нижний угол вниз, то всемогущий Excel автоматически заполнит следующие ячейки месяцами по порядку. Но не только лишь все знают, что такую штуку можно сделать и по своему списку.
Шаг 1. Создаём свой список (с блэкджеком и порядком). Где-нибудь на листе Excel создаём список в том порядке, в котором нам нужно. Далее заходим в Файл — Параметры — Дополнительно — Изменить списки:

В поле «Импорт списка из ячеек:» указываем диапазон, в котором находится наш список, далее нажимаем Импорт. Всё, подготовительная работа завершена:

Шаг 2. Для сортировки данных используем наш список. Теперь можно применить сортировку от А до Я в столбце с менеджерами. И вот ведь неожиданность, сортировка будет не по алфавиту, а согласно нашему списку:

К сожалению, данный приём имеет ограничения в части обновления списка. Если добавится новый сотрудник, то при обновлении сводной сотрудник будет в самом конце списка независимо от того, какая у него там первая буква фамилии. Дальше либо добавлять его в список, либо вручную перетаскивать в нужное место.
Если нужно будет вернуть стандартную православную сортировку по алфавиту для данного списка, необходимо раскрыть значок фильтра, выбрать Дополнительные параметры сортировки — по возрастанию — Дополнительно — снять галочку с «Автоматическая сортировка. » — в поле ниже выбрать «Без вычислений»:

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

4 — Отображение пустых строк.

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

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

Решение — щёлкаем правой кнопкой мыши по любой ячейке того поля, в котором хотим отображать ВСЕ элементы (в том числе и пустые) — далее Параметры поля. — вкладка Разметка и печать — ставим галочку Отображать пустые элементы.

У этого приёма есть один побочный эффект — если речь идёт про работу с датами, то отображаются даты до самого раннего числа и после него (видно на гифке). Избавиться от этого довольно просто: нужно убрать их через фильтр, сняв галочки.

5 — Топ-N сотрудников/месяцев/товаров и так далее.

Вообще, отображение топ-3/5/10 сотрудников или любого другого — это не привилегия фильтра в сводной таблице. Работая с фильтрами в обычных таблицах, у нас есть точно такая же возможность. Но про эту возможность, исходя из моего хоть и небогатого, но всё же опыта, мало кто знает. То ли нафиг никому не нужно, то ли просто пользователи не знают. Как по мне, вещь полезная. Особенно с учётом того, что в сводной таблице фильтр будет обновляться вместе с внесением изменений в источник (и обновления самой сводной, конечно же), чего в обычных таблицах, увы, нет.

Решение — всё просто. Раскрываем фильтр по тому полю сводной, по которому хотим выделить топ-N чего-нибудь — далее Фильтр по значению — Первые 10 — указываем нужные нам настройки.

Правда, без побочных эффектов тут, к сожалению, тоже не обошлось. Если у какого-то сотрудника не будет хватать данных для того, чтобы отобразить топ-3, то по этому сотруднику (или по тому, по чему вы ищете топ-N) будут вообще все данные. Поведение более чем странное, но как есть 🙁

6 — Повтор подписей и промежуточные итоги для отдельных полей, а не для всей таблицы.

Когда у нас с вами в сводной таблице в области строк больше двух полей, то сразу возникает вопрос про промежуточные итоги. И если идти стандартным путём, то есть через вкладку Конструктор — группа Макет — Промежуточные итоги, то там мы можем играться с промежуточными итогами для всей таблицы, а не для конкретного поля. Порой, это неудобно. Как в примере ниже:

Я бы хотел оставить промежуточный итог по сотруднику, а вот по месяцу убрать.
И сразу второй момент. Когда меняем макет сводной на табличный или форму структуры, то возникает вопрос с повторением подписей элементов, чтобы не было пустых ячеек. Если перейти на вкладку Конструктор — группа Макет — раскрыть Макет отчёта и выбрать Повторять все подписи элементов, то это сработает для всей таблицы. А я, например, хочу только по месяцам подписи.

Решение — великая и могучая правая кнопка мыши. Для того, чтобы добавить/убрать промежуточные итоги у определённого поля сводной таблицы, щёлкаем правой кнопкой мыши по любой ячейке этого поля и в контекстном меню находим Промежуточный итог.
Что касается подписей элементов, то щёлкаем правой кнопкой мыши по тому полю, где хотим/не хотим повторять подписи — далее Параметры поля. — на вкладке Разметка и печать находим галочку Повторять все подписи элементов.

7 — Объединение ячеек и выравнивание подписи по центру.

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

Решение — для объединения ячеек и выравнивания подписи по центру щёлкаем по любой ячейке сводной таблицы правой кнопкой мыши (данная настройка будет работать для всех полей сводной, для какого-то конкретного поля настроить возможности нет) — в контекстном меню находим Параметры сводной таблицы — вкладка Макет и формат — находим галочку Объединить и выровнять по центру ячейки с подписями.
А чтобы убрать плюсы/минусы, нужно выбрать любую ячейку сводной — далее вкладка Анализ сводной таблицы (в предыдущих версиях просто Анализ) — группа Показать — иконка Кнопки.

Заключение. На этом, пожалуй, всё. Вообще, всяких разных интересных штук при работе со сводной таблицей огромное множество. Про все и не напишешь. Делитесь в комментариях своими интересными приёмами. Думаю, многим будет интересно и полезно узнать что-то новое (мне так точно). Спасибо всем огромное, кто потратил своё драгоценное время и внимание на прочтение данного поста. Надеюсь, что-то из описанного было для вас полезным и вы сможете использовать это при работе со сводными таблицами.

Excel вечен⁠ ⁠

Диаграмма «спагетти» в Excel⁠ ⁠

Ребятушки, подскажите, можно ли сделать в эксель диаграмму спагетти, типа такой, которая на рисунке?

Нужно отобразить передвижение из одного кабинета в другой

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1⁠ ⁠

Друзья, всем привет.

Сегодня хотел бы поговорить с вами о том, чего в Excel делать не нужно, чтобы в будущем избавить себя от головной боли и перманентных хлопков в нижней части тела. Планировал сначала про 10 вещей написать, но при подготовке материала понял, что в рамках одной статьи слишком уж много получится. Поэтому решил разбить на 2 части.

Важное примечание!

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

1 — Объединение ячеек.

Один из самых страшных грехов при создании таблиц. Объединение ячеек влечёт за собой трудности с сортировкой, фильтрацией, созданием сводных, форматированием, протягиванием формул и т.д. Вот пример:

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

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

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание — по горизонтали — по центру выделения:

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

2 — «Правильная» таблица

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

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

Всё просто — не делайте трёхмерные таблицы. Плоская таблица — вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:

Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.

3 — «Правильные» даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

«-» — дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 — Цветовое оформление таблицы.

Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 — Ручное закрашивание данных

Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

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