Часто из всем известной 1С отчеты выгружаются в Excel. Что не удивительно, ведь многие используют 1С как базу ведения всевозможных данных, а анализ предпочитают производить в Excel. И это удобно, это работает. Но часто при получении файла из 1С форматы ячеек изменены так, что отображается в ячейках одно значение, а на деле там значение совершенно иное:
Чтобы не возникло недопонимания, что это такое на картинках выше. Например, если в ячейку записать число
И как это всегда бывает - порой просто необходимо работать не с тем значением, которое реально в ячейке, а именно с теми, которые отображаются в ячейках. Яркий пример такой необходимости - это сцепление данных двух ячеек, в одной из которых записана дата. Например, в
Если формат лишь один - можно стандартно попробовать побороть при помощи функции ТЕКСТ(TEXT). Например, в ячейках столбца А записаны даты в формате 31 января 2016г. Тогда формулу можно записать так:
На примере той же СЦЕПИТЬ
Сам вид формата для использования в функции
Но если форматы в ячейках различаются и записаны в разнобой...Стандартно этого никак не сделать, кроме как каждую ячейку руками перебивать. Но если прибегнуть к помощи Visual Basic for Applications(VBA), то можно написать простую функцию пользователя(Что такое функция пользователя(UDF)) и применить её:
Function VisualVal_Text(rc As Range) VisualVal_Text = rc.Text End Function |
Для применения надо внимательно прочитать про создание функций пользователя. После этого в ячейку останется записать:
и раскопировать ячейку на весь столбец. После этого можно заменить результат функции значениями(Как удалить в ячейке формулу, оставив значения) и все готово. Но и в этой функции есть недостаток. Если в ячейке отображается значение, которое не помещается в границы ячейки, то оно может быть обрезано или вместо значения будут решетки. Например, если дата в указанном формате не помещается в ячейку - вместо значений будут решетки #######:
и функция
Function VisualVal(rc As Range) VisualVal = Application.Text(rc.Value, rc.NumberFormat) End Function |
Используется и записывается в ячейку так же, как и предыдущая:
Эта функция без всяких танцев с бубном вернет отображаемое форматом ячейки значение.
И вариант применения функции вместе с функцией СЦЕПИТЬ:
=СЦЕПИТЬ(
Как видно не надо задумываться о том какой применить формат - будет записано так же, как оно отображается в ячейке.
Все варианты решений можно
Tips_Macro_CellValToVisual.xls (48,5 КиБ, 1 489 скачиваний)
Так же см.:
Как удалить в ячейке формулу, оставив значения?