Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel
Друзья, всем привет. В комментариях, да и на занятиях, довольно часто поднимается тема преобразования чисел, сохранённых как текст, в рабочий числовой формат, поэтому решил про это написать. Как обычно, здесь не будет никаких откровений и чего-то нового. Так или иначе, всё то, о чём напишу, уже давно есть в интернете. Просто решил собрать всё в одном месте. Может, кому-то будет полезно (я на это очень надеюсь).
Небольшое вступление. Чего рассматривать не будем, и почему такое вообще происходит.
Сразу оговорюсь, что не буду здесь рассматривать случаи, когда в ячейках кромешный ад и вакханалия вроде "10 руб", "33 попугая" и так далее. Хотя один способ может и с такими ячейками помочь. Будем разбирать православные ячейки, в которых только число, но, по тем или иным причинам, оно сохранено как текст.
Причины могут быть разными, но чаще всего такая ситуация случается, когда мы импортируем данные в Excel из какой-нибудь другой корпоративной программы или копируем из интернета/другого источника. Выгрузил данные в Excel, пытаешься что-то просуммировать, а на выходе получаешь шиш с маслом:
Excel любезно даёт понять, что число сохранено как текст, добавляя в такие ячейки зелёный треугольник в левый верхний угол (не касается дат, сохранённых как текст, такие ячейки никак не выделяются). Интересный момент: если суммировать ячейки вручную, то результат будет нормальным.
Вся загвоздка в том, что если пойти путём обычной смены формата на числовой (Главная - Число - в выпадающем списке выбрать Числовой), результата это не даст. Придётся взять бубен и немного потанцевать с ним.
И так, что же можно сделать.
Важно! Прежде чем пробовать какой-то из способов, убедитесь, что у вас в принципе правильные разделители указаны. Если система использует запятую как разделитель групп разрядов, а у вас точки везде, нужно просто через поиск и замену поменять точку на запятую (и наоборот, если в качестве разделителя система использует точку).
Способ 1. Использовать инструмент "Текст по столбцам" (text to columns) . Выделяем все "проклятые" ячейки, идём на вкладку Данные - Работа с данными - Текст по столбцам, и на самом первом шаге нажимаем Готово.
![Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel Microsoft Excel, Таблицы Excel, Гифка, Длиннопост](https://cs13.pikabu.ru/post_img/2023/05/08/6/1683538057129068708.jpg)
Способ 2. Замена разделителя на аналогичный. Честно скажу, когда рассказываю про этот способ, почти все говорят "Да какого..?! Да почему!? Да что с этой программой не так?!". Да чего уж, признаюсь, что без улыбки на лице про него сам не могу рассказывать. Если в ячейках дробные числа, то преобразовать можно с помощью банальной замены запятой на... Барабанная дробь... ЗАПЯТУЮ! Выделяем данные, запускаем поиск и замену (CTRL+ H), ищем запятую, меняем на запятую. Этот способ также подходит для дат, которые сохранены как текст, только вместо запятой заменять нужно разделитель, который указан в ячейках (точка, слэш, дефис).
![Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel Microsoft Excel, Таблицы Excel, Гифка, Длиннопост](https://cs13.pikabu.ru/post_img/2023/05/08/6/1683538646145849299.jpg)
Способ 3. С помощью специальной вставки. Довольно интересный способ, так как знакомит нас со специальной вставкой - очень полезным инструментом. Смысл в том, что для преобразования текстовых чисел в нормальные числа с ними нужно произвести какое-то математическое действие. Но нужна такая операция, которая не меняет самого числа, а это, например, умножение на единицу (есть более экзотические, прибавить или вычесть ноль, но их рассматривать не будем). Алгоритм следующий:
В произвольную ячейку пишем единицу (1)
Копируем эту ячейку (Ctrl + C)
Выделяем диапазон с "кривыми" числами
По любой выделенной ячейке щёлкаем правой кнопкой мыши (либо Ctrl + Alt + V)
В контекстном меню выбираем Специальная вставка (Paste special)
В следующем окне выбираем "Умножить". Если в таблице есть какое-то оформление, то ещё желательно выбрать "Значения", чтобы это самое оформление не слетело
Жмём Ок.
![Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel Microsoft Excel, Таблицы Excel, Гифка, Длиннопост](https://cs14.pikabu.ru/post_img/2023/05/08/6/1683539629167516730.jpg)
Способ 4. Функция ЗНАЧЕН (VALUE). Если планируете делать некий шаблон, в который будете копировать текстовые числа, а на выходе получать нормальные, то можно воспользоваться функцией, которая как раз и занимается преобразованием. Если работаете с датами, то нужна функция ДАТАЗНАЧ (DATEVALUE).
![Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel Microsoft Excel, Таблицы Excel, Гифка, Длиннопост](https://cs14.pikabu.ru/post_img/2023/05/08/7/1683540209140869861.jpg)
Способ 5. Бинарное отрицание. Хотите быть не как все? Хотите, чтобы коллеги подходили к вам с вопросом "А что это такое тут у тебя формуле?". Тогда этот способ для вас! Бинарное отрицание, если рассматривать его в контексте нашего вопроса, умножает число на -1, а потом ещё раз на -1. То есть мы производим математическую операцию, которая не меняет самого числа. Нужно просто перед ссылкой на ячейку поставить два знака минус (-). Вполне можно использовать при создании шаблона вместо функции.
![Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel Microsoft Excel, Таблицы Excel, Гифка, Длиннопост](https://cs13.pikabu.ru/post_img/2023/05/08/7/1683545088184456332.jpg)
Способ 6. Excel спешит на помощь. Вообще, если нажать на смарт-тэг (знак "дорожные работы") с ошибкой, то программа сама предложит преобразовать текст в число:
То есть можно выделить диапазон с такими вот ячейками, потом нажать на смарт-тэг, выбрать "Преобразовать в число", и всё сработает как надо. Но должен предупредить, что с большим количеством ячеек способ может работать довольно долго. Особенно если в книге много всего другого (формулы, листы, связи и т.д.).
Немного другая история. Иногда, когда выгружаем данные откуда-то в Excel, в числовых ячейках попадается пробел, как разделитель групп разрядов. И в большинстве случаев все способы, про которые я писал выше, справляются с преобразованием. Но порой что-то идёт не так. И ничего не срабатывает. Отчаявшись, ты пытаешься заменить пробел на пусто, но Excel, подлец, пишет, что пробелов в ячейках не найдено. Но как, вот же он, бездушная ты слепая программа:
К своему стыду (ладно, вру, мне не стыдно), я не погружался прям глубоко в то, почему так происходит. Для себя я придумал версию, что пробелы бывают разные. Так вот, чтобы разобраться с таким недоразумением, нужно скопировать пробел из самой ячейки, выделить ячейки, которые хотим преобразовать, потом открыть окно поиска и замены, вставить скопированный пробел в поле "Найти", а заменить на пусто.
![Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel Microsoft Excel, Таблицы Excel, Гифка, Длиннопост](https://cs14.pikabu.ru/post_img/2023/05/08/7/1683542415166480891.jpg)
Всесильный Power Query.
Долго думал, стоит ли включать сюда этот способ, и всё-таки решил включить. Часто в комментариях пишут, что а вот это можно с помощью PQ сделать, а вот то вообще на раз-два. И ведь это правда. PQ - это безумно крутая штука. Почему боюсь про него писать? По одной простой причине: более менее интерфейс PQ устаканился, начиная с 2019 версии. Для 2010 и 2013 вообще надо отдельно скачивать и устанавливать (да, это просто и занимает всего пару минут, но всё же). В 2016 версии сразу из коробки идёт, но выглядит чуть иначе. И вот я сейчас покажу, как это делается, а кто-то потом напишет, что у него этого нет, а это по-другому выглядит, и вообще автор - кАзёл. И всё же :) Собрал все случаи, про которые писал. Дальше:
Желательно преобразовать таблицу в "умную" (не сделаете сами, всё равно потом Excel это сделает за вас)
Далее вкладка Данные - Получить и преобразовать данные - Из таблицы/диапазона
Если настройки PQ не меняли, то автоматически будет применён шаг "Изменённый тип", который всё и сделает
В PQ на вкладке Главная нажимаем Закрыть и загрузить
Получаем на новом листе "умную" таблицу с правильными форматами.
![Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel Microsoft Excel, Таблицы Excel, Гифка, Длиннопост](https://cs14.pikabu.ru/post_img/2023/05/08/7/1683544355144448908.jpg)
Итог.
На этом, пожалуй, простыню свою завершу. Возможно, я рассказал не про все способы борьбы с текстовыми числами/датами. В комментариях делитесь своими, с удовольствием про них почитаю. Если спросите, зачем так много способов, то ответ будет так себе. Для разнообразия, конечно :) Когда какой использовать - дело вкуса, привычки и исходных данных. В большинстве случаев все способы взаимозаменяемы.
Как всегда, всем огромное спасибо, кто потратил своё драгоценное время и внимание на прочтение данного материала. Надеюсь, было полезно и что-то из статьи поможет вам в работе.
Как отключить окно visual basic в excel
Мне прислали экселевский файл и при нажатии на любую ячейку выскакивает окно с Visual Basic? Как эту хрень отключить? Работать невозможно.
Работа VBA не в MS Office
Добрый вечер. Данный пост посвящён вот чему - в пабликах стали активно уверять, что "макросы в любом случае придется переписать" "ни один хоть российский, хоть зарубежный офис корректно не откроет док с макросами." Упорно так убеждать...
Однако как быть с тем, что я беру файл с макросом (например описанном в моём посте , если что там и файлик есть) и без каких либо доп.действий пробу. открыть в LO. И... Внезапно всё нормально открывается, обсчитывается и перестраивается.
Может макрос простоват? Ок, берём набор макросов расчёта свойств воды и водяного пара согласно формуляции IF97 на 3000 строк кода (примерно) и... и опять всё работает. Мало того, я забыл что эти макросы являются подгружаемыми при запуске LO... Т.е. уже совсем другой уровень.
Видео процесса: (не хочет вставляться видео, пишет "Невозможно получить информацию о видео, скорее всего оно удалено автором". Поэтому ссылка. )
Как итог по моему опыту:
Будет ли всё работать без трабл? По опыту - нет. Всё не будет. Однако будет большая часть - на видео видно, что более 3000 строк кода правки не потребовали от слова совсем...
Потребуется ли значительная правка кода? При использовании функционала Excel - да. Мне помог форум помощи LO Но в значительном количестве случаев будет достаточно вставки Option VBASupport 1 перед кодом ( и то она вставляется автоматом). Однако корректировка кода (с количеством строк измеряемым сотнями тысяч) гораздо менее трудозатратна чем полное переписывание и отладка на новом языке.
Пока писал текстовку таки вспомнил где слышал "VBA - это пропиетарщина..." и "и один хоть российский, хоть зарубежный офис корректно не откроет док с макросами" - это звучало на вэбинаре МойОфис около года назад, когда проводил сравнение и слушал всякое. За год не изменилось ничего... Грубо работаете. Если так топите за МойОфис - повторите то, что я показал в серии "Excel Дорога оцифровки" Там минимум (что то на уровне букваря) из того с чем мы сейчас работаем и пользуемся. Дальше уж я как нибудь сам... Только вот пока вы и график в МойОфис построить не можете. Так, намётки...
Прошу направить на нужный метод
Есть имена некоторого кол-ва людей, есть продукция которую они собирают, наименование и количество продукции вводится в таблицу методом Ctrl+V (выделено жёлтым)
Далее на листе 2 эти данные считаются и делаются более компактно. Суть в повторах, что бы они сводились воедино, складывались. Т.е. что-бы на втором листе не было 2 строки с яблоками 1 одного человека. Так же что-бы добавлялось что-то новое, если оно появится на первой странице.
И нужно мне это не в обычном экселе а в гугловском (онлайн). Буду благодарен если направите меня как это замутить. Вроде не сложно, но не знаю за что цепляться.
Помогите с созданием таблицы
Приветствую! По работе добавилось куча бумажной работы, которую хочется перевести в вид электронной таблицы. Попытался сделать это самостоятельно, однако никогда ранее не имея дел с экселем - ничего не получается.
Имеется маркировка детали, формата
12-40,у100,1кв,12а
15-21,ш6,нв,1
3-100,Б11ш3,11
Хочется: ввод маркировки в ячейку, после ввода - маркировка разбивается на 4 столбца по запятым, вносится в отдельную таблицу, из ячейки введенное стирается автоматически, вводится новая маркировка, также разбивается и уходит в таблицу.
Первые 2 части маркировки - неизменны, вторая половина - состояние и положение могут изменяться, поэтому при вводе повтора - предыдущее значение нужно актуализировать
Примерно так мне это представляется: ввожу данные в ячейку а1, они разбиваются, добавляются в таблицу справа, ячейка а1 очищается, вводятся следующая маркировка.
В случае совпадения по первым 2 ячейкам в таблице - старая строка удаляется, добавляется новая актуальная.
Возможно ли это реализовать?
Поиграем в бизнесменов?
Одна вакансия, два кандидата. Сможете выбрать лучшего? И так пять раз.
Связи в модели данных Power Pivot
Пикабутяне, нужна ваша помощь)
Есть три таблицы
1. Плановые заказы
2. Внеплановые заказы
3. Изменение статусов заказов по числам (заказ в отработке, заказ ждёт отгрузки, заказ выполнен)
На основании этого всего нужно составить отчет по выполнению плана
Столбцы в отчете:
1. Плановое кол-во заказов
2. Фактическое количество заказов
3. Выполнение, %
4. Выполнение в % с учетом внеплановых заказов
Как прокинуть связи между таблицами, чтобы можно было смотреть выполнение плана на дату? Например, на вчерашний день или на прошлую пятницу
У меня получается так, что я могу посмотреть выполнение плана на дату, но туда не попадают внеплановые заказы, а нужно чтобы попадали