Excel: как убрать проблему с текстом

Excel: как убрать проблему с текстом

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

Екатерина Левченко, бухгалтер, г. Харьков

Отвечает

Действительно, при переносе информации между Excel и другими программами часто возникает проблема преобразования данных, доставляя немало хлопот в самый неподходящий момент. Но прежде чем предложить способ ее решения, думаю, имеет смысл описать задачу подробнее. Итак, на рис. 1 показан фрагмент отчета, импортированного в Excel из программы «1C:Бухгалтерия». Это не что иное, как хорошо знакомая каждому бухгалтеру карточка счета «

1) выделяем колонку «А», щелкаем на выделенном блоке правой кнопкой мыши;

2) из контекстного меню выбираем «Формат ячеек…». Появится одноименное окно «Формат ячеек», как на рис. 2;

3) переходим на закладку «Число», в списке «Числовые форматы:» выбираем вариант «Общий»;

4) в окне «Формат ячеек» нажимаем «ОК»;

5) выделяем несколько ячеек с датами. Например, блок «A6:A8»;

6) смотрим на статусную строку. Суммы по этим ячейкам в статусной строке нет. Это означает, что изменение формата не привело к решению проблемы.

Для того чтобы ячейки стали действительно числовыми, нужно их пересчитать. Но обычного нажатия клавиши «F9» здесь недостаточно. Нам придется последовательно переходить по всем(!) проблемным ячейкам и нажимать вначале клавишу «F2» (войти в режим редактирования), а затем «Enter». Обработанные таким образом ячейки Excel преобразует в общий формат. После этого с ними можно будет работать как с обычными числами. Когда количество данных невелико, такая работа не займет много времени. Но если перед нами несколько тысяч записей, на ее обработку потребуются часы… В то же время решить проблему можно очень просто:

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

2) затем копируем формулы в буфер обмена и вставляем на нужное место в виде значений.

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

Итак, наша цель — в таблице на рис. 1 преобразовать колонку с датами в числовой формат. Делаем так:

1) щелкаем на ячейке «J6». Это будет первая рабочая ячейка;

2) вводим в нее формулу «=A6+0». В ячейке появится значение «40183». Это числовое представление даты «05/01/2010». Этот результат нужно скопировать на всю высоту базы данных;

3) переходим на колонку, которая полностью заполнена значениями. В примере на рис. 3 это будет ячейка «A6»;

4) последовательно нажимаем клавиши «End», затем «$» (стрелка вниз). Активная ячейка тут же встанет на последнюю заполненную строку таблицы. На рис. 4 это ячейка «A747»;

5) перемещаем активную ячейку вправо до колонки «J» на ячейку «J745»;

6) вводим в нее любой символ (число). Например, «1»;

7) последовательно нажимаем «End» затем «#». Активная ячейка моментально перейдет на «J6»;

8) копируем эту ячейку в буфер обмена («Ctrl+C»);

9) удерживая клавишу «Shift», последовательно нажимаем «End», затем «$». На листе будет выделен блок для вставки на всю высоту таблицы (от ячейки «J6» до «J745»);

10) вставляем данные из буфера («Ctrl+V»). Можно просто нажать «Enter». Данные в числовом формате мы получили, переносим их в колонку «А»;

11) выделяем блок «J6:J745», копируем его в буфер обмена («Ctrl+C»);

12) становимся на ячейку «А6», вызываем меню «Правка → Специальная вставка…». Появится окно, как на рис. 4;

13) в нем выбираем вариант «Значения» (рис. 3) и нажимаем «ОК». Данные мы перенесли. Остается отформатировать выделенный блок;

14) не снимая выделения, щелкаем на блоке «A6:A745» правой кнопкой мыши, из контекстного меню выбираем «Формат ячеек…»;

15) на закладке «Число» выбираем вариант «Дата». Теперь в колонке «A» даты представлены в числовом формате. Чтобы проверить это, выделяем блок «A6:A8». В строке состояния видим значение суммы для выделенных ячеек;

16) удаляем колонку «J» — она нам больше не понадобится.