зміст
Якщо текстовий формат був встановлений для будь-яких комірок на аркуші (це міг зробити користувач або програма під час завантаження даних в Excel), то числа, введені пізніше в ці комірки, Excel починає вважати текстом. Іноді такі комірки позначені зеленим індикатором, який ви, швидше за все, бачили:
А іноді такий показник не з'являється (що набагато гірше).
Загалом, поява чисел як тексту у ваших даних зазвичай призводить до багатьох дуже сумних наслідків:
- сортування перестає нормально працювати – «псевдочисла» видавлюються та не впорядковуються в очікуваному порядку:
- функції типу VLOOKUP (ВПР) не знаходять потрібних значень, тому що для них число і те саме число-як-текст різні:
- при фільтрації помилково вибираються псевдочисла
- багато інших функцій Excel також перестають працювати належним чином:
- і т.п.
Особливо смішно, що не допомагає природне бажання просто змінити формат комірки на числовий. Ті. ви буквально вибираєте комірки, клацаєте їх правою кнопкою миші та вибираєте Формат комірки (Формат клітинок), змініть формат на чисельний (кількість), стиснути OK — і нічого не відбувається! Зовсім!
Можливо, «це не баг, а фіча», звичайно, але нам від цього не легше. Тож давайте розглянемо кілька способів виправити ситуацію – один із них вам точно допоможе.
Спосіб 1. Зелений кут індикатора
Якщо ви бачите зелений кут індикатора на комірці з числом у текстовому форматі, то вважайте, що вам пощастило. Ви можете просто виділити всі комірки з даними і натиснути на спливаючу жовту іконку зі знаком оклику, а потім вибрати команду Перетворити на число (Перетворити на число):
Усі числа у вибраному діапазоні буде перетворено на повні числа.
Якщо зелених куточків немає взагалі, то перевірте, чи не відключені вони в налаштуваннях Excel (Файл – Параметри – Формули – Числа, відформатовані як текст або перед якими стоїть апостроф).
Спосіб 2: Повторний вхід
Якщо клітинок небагато, то можна змінити їх формат на числовий, а потім повторно ввести дані, щоб зміна формату вступила в силу. Найпростіше це зробити, вставши на клітинку і послідовно натиснувши клавіші F2 (увійти в режим редагування, у комірці починає блимати курсор), а потім
Що натомість? Створіть віртуальну версію себе у
. Також замість F2 можна просто двічі клацнути по клітинці лівою кнопкою миші.
Само собою зрозуміло, що якщо стільників багато, то цей спосіб, звичайно, не підійде.
Спосіб 3. Формула
Швидко перетворити псевдочисла в звичайні можна, якщо біля даних зробити додатковий стовпець з елементарною формулою:
Подвійний мінус, в даному випадку, означає, по суті, множення на -1 вдвічі. Мінус на мінус дасть плюс і значення в комірці не зміниться, але сам факт виконання математичної операції перемикає формат даних на потрібний нам числовий.
Звичайно, замість множення на 1 можна використовувати будь-яку іншу нешкідливу математичну операцію: ділення на 1 або додавання і віднімання нуля. Ефект буде той самий.
Спосіб 4: Спеціальна вставка
Цей метод використовувався в старих версіях Excel, коли сучасні ефективні менеджери пішли під стіл зеленого куточка-індикатора ще не було в принципі (він з'явився лише в 2003 році). Алгоритм такий:
- введіть 1 у будь-яку порожню клітинку
- скопіюйте його
- виділіть комірки з числами в текстовому форматі та змініть їх формат на числовий (нічого не станеться)
- клацніть правою кнопкою миші клітинки з псевдочислами та виберіть команду Вставити спеціально (Спеціальна вставка) або скористайтеся комбінацією клавіш Ctrl + Alt + V
- у вікні, що відкриється, виберіть опцію Значення (Значення) и Розмножуватися (Помножити)
По суті, ми робимо те ж саме, що і в попередньому способі – множимо вміст комірок на одиницю – але не за допомогою формул, а безпосередньо з буфера.
Спосіб 5. Текст за колонками
Якщо псевдочисла, які потрібно перетворити, також записані з неправильними десятковими або тисячними роздільниками, можна використати інший підхід. Виберіть вихідний діапазон із даними та натисніть кнопку Текст по колонках (Текст до колонок) таб дані (Дата). Насправді цей інструмент призначений для поділу закріпленого тексту на стовпці, але в даному випадку ми використовуємо його з іншою метою.
Пропустіть перші два кроки, натиснувши на кнопку МАЙБУТНІ (Далі), а на третьому скористайтеся кнопкою Додатково (Додатково). Відкриється діалогове вікно, у якому ви можете встановити символи-роздільники, наявні в нашому тексті:
Натиснувши на обробка Excel перетворить наш текст на звичайні числа.
Спосіб 6. Макрос
Якщо такі перетворення доводиться робити часто, то є сенс автоматизувати цей процес за допомогою простого макросу. Натисніть Alt+F11 або відкрийте вкладку розробник (розробник) І натисніть кнопку Візуальний Бейсік. У вікні редактора, що з’явиться, додайте новий модуль через меню Вставка – модуль і скопіюйте туди наступний код:
Sub Convert_Text_to_Numbers() Selection.NumberFormat = "Загальне" Selection.Value = Selection.Value End Sub
Тепер після вибору діапазону ви завжди можете відкрити вкладку Розробник – макроси (Розробник — Макроси), виберіть у списку наш макрос, натисніть кнопку прогін (Запуск) – і миттєво перетворювати псевдочисла в повноцінні.
Ви також можете додати цей макрос до своєї особистої книги макросів для подальшого використання в будь-якому файлі.
PS
Така ж історія відбувається і з фініками. Деякі дати також можуть розпізнаватися Excel як текст, тому групування та сортування не працюватимуть. Рішення такі ж, як і для чисел, тільки формат потрібно замінити на дату-час замість числового.
- Поділ липкого тексту на колонки
- Розрахунки без формул спеціальним склеюванням
- Перетворіть текст на числа за допомогою надбудови PLEX