Excel функция ячейка: ЯЧЕЙКА (функция ЯЧЕЙКА) — Служба поддержки Майкрософт

Видимое значение ячейки в реальное

Хитрости »

2 Февраль 2016       Дмитрий       17051 просмотров

Часто из всем известной 1С отчеты выгружаются в Excel. Что не удивительно, ведь многие используют 1С как базу ведения всевозможных данных, а анализ предпочитают производить в Excel. И это удобно, это работает. Но часто при получении файла из 1С форматы ячеек изменены так, что отображается в ячейках одно значение, а на деле там значение совершенно иное:

Чтобы не возникло недопонимания, что это такое на картинках выше. Например, если в ячейку записать число 1077, то оно и отобразится так же. Однако его визуальное отображение в ячейках можно изменить: выделяем ячейку -правая кнопка мыши —Формат ячеек(Format Cells) -вкладка Число(Number). Далее в списке слева выбрать Дополнительный(Special) и установить Почтовый индекс. Тогда в ячейке визуально будет отображаться 001077, в то время как реально в ячейке будет оставаться все то же число 1077. Тоже и с датами. Реально в ячейке число, а визуально дата в одном из форматов из категории Дата. Подробнее про то, почему так происходит можно прочесть в статье: Как Excel воспринимает данные?
И как это всегда бывает — порой просто необходимо работать не с тем значением, которое реально в ячейке, а именно с теми, которые отображаются в ячейках. Яркий пример такой необходимости — это сцепление данных двух ячеек, в одной из которых записана дата. Например, в A1 записана дата «06.02.2016», а в B1 текст вроде «Отчет по магазину за » и необходимо сцепить текст из B1 с датой из A1. Если применить просто функцию СЦЕПИТЬ(CONCATENATE) или по простому =B1&A1, то результатом будет такой текст: Отчет по магазину за 42406.
Если формат лишь один — можно стандартно попробовать побороть при помощи функции ТЕКСТ(TEXT). Например, в ячейках столбца А записаны даты в формате 31 января 2016г. Тогда формулу можно записать так:
=ТЕКСТ(A2;»[$-F800]ДДДД, ММММ ДД, ГГГГ»)
=TEXT(A2,»[$-F800]dddd, MMMM yy, yyyy»)
На примере той же СЦЕПИТЬ(CONCATENATE):
=СЦЕПИТЬ(B1;ТЕКСТ(A1;»[$-F800]ДДДД, ММММ ДД, ГГГГ»))
=CONCATENATE(B1,TEXT(A1,»[$-F800]dddd, MMMM yy, yyyy»))

Сам вид формата для использования в функции ТЕКСТ можно подсмотреть непосредственно в форматах ячеек: правая кнопка мыши на ячейке —Формат ячеек(Format Cells) -вкладка Число(Number) -(все форматы)(custom format). Там в поле Тип будет как раз приведен применяемый код формата. Можно его просто скопировать оттуда и вставить в функцию ТЕКСТ.
Но если форматы в ячейках различаются и записаны в разнобой…Стандартно этого никак не сделать, кроме как каждую ячейку руками перебивать. Но если прибегнуть к помощи Visual Basic for Applications(VBA), то можно написать простую функцию пользователя(Что такое функция пользователя(UDF)) и применить её:

Function VisualVal_Text(rc As Range)
    VisualVal_Text = rc.Text
End Function

Для применения надо внимательно прочитать про создание функций пользователя. После этого в ячейку останется записать:
=VisualVal_Text(A1)
и раскопировать ячейку на весь столбец. После этого можно заменить результат функции значениями(Как удалить в ячейке формулу, оставив значения) и все готово. Но и в этой функции есть недостаток. Если в ячейке отображается значение, которое не помещается в границы ячейки, то оно может быть обрезано или вместо значения будут решетки. Например, если дата в указанном формате не помещается в ячейку — вместо значений будут решетки #######:

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

 Function VisualVal(rc As Range)
    VisualVal = Application.Text(rc.Value, rc.NumberFormat)
End Function

Используется и записывается в ячейку так же, как и предыдущая:
=VisualVal(A1)
Эта функция без всяких танцев с бубном вернет отображаемое форматом ячейки значение.

И вариант применения функции вместе с функцией СЦЕПИТЬ:
=СЦЕПИТЬ(B1;VisualVal_Text(A1))
=CONCATENATE(B1,VisualVal_Text(A1))
=СЦЕПИТЬ(B1;VisualVal(A1))
=CONCATENATE(B1,VisualVal(A1))
Как видно не надо задумываться о том какой применить формат — будет записано так же, как оно отображается в ячейке.

Все варианты решений можно посмотреть в примере:

  Tips_Macro_CellValToVisual.xls (48,5 KiB, 1 306 скачиваний)

Так же см. :
Как удалить в ячейке формулу, оставив значения?

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам

Accessapple watchMultexPower Query и Power BIVBA управление кодамиБесплатные надстройкиДата и времяЗапискиИПНадстройкиПечатьПолитика КонфиденциальностиПочтаПрограммыРабота с приложениямиРазработка приложенийРосстатТренинги и вебинарыФинансовыеФорматированиеФункции Excelакции MulTExссылкистатистика

VBA Excel. Ячейки (обращение, запись, чтение, очистка)

Обращение к ячейке на листе Excel из кода VBA по адресу, индексу и имени. Чтение информации из ячейки. Очистка значения ячейки. Метод ClearContents объекта Range.

Обращение к ячейке по адресу

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.

Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:

Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».

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

Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.

Обращение к ячейке по индексу

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

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

Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.

По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):

Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2").

Обращение к ячейке по имени

Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.

Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого").

Запись информации в ячейку

Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

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

Чтение информации из ячейки

Считать информацию из ячейки в переменную можно также при помощи оператора присваивания «=»:

Точно также можно обмениваться информацией между ячейками:

Очистка значения ячейки

Очищается ячейка от значения с помощью метода ClearContents. Кроме того, можно присвоить ячейке значение нуля. пустой строки или Empty:

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Видео Как использовать информационные функции Excel CELL и INFO

Примеры в этих видеороликах показывают, как использовать информационные функции Excel,
сотовый и информация. Также см. примеры функций ADDRESS.

Видео: использование функции CELL

Видео: использование функции INFO

Получение файлов примеров

Другие руководства по функциям

Введение

Функция CELL может возвращать информацию о форматировании ячеек
содержание и расположение.

Функция INFO может отображать такие сведения, как версия Excel, перерасчет
режим и количество листов во всех открытых книгах.

Чтобы вернуть адрес ячейки в виде текста на основе номера строки и столбца, перейдите на страницу функции ADDRESS.

Видео: Используйте функцию ЯЧЕЙКА

Используйте функцию ЯЧЕЙКА в Excel, чтобы получить подробную информацию о
формат ячейки, расположение и содержимое. Функция CELL может показать
такие детали, как ширина столбца, имя рабочего листа и числовой формат.

Посмотрите это короткое видео, чтобы увидеть примеры использования
КЛЕТОЧНАЯ функция.

Синтаксис CELL

Функция CELL имеет следующий синтаксис:

• CELL(info_type,reference)
  • info_type является одним из следующих значений
    • адрес… Ссылка на первую ячейку в ссылке в виде текста.
    • col… Столбец # ячейки в ссылке.
    • цвет…1 = цвет для отрицательных значений; в противном случае 0 (ноль).
    • содержимое. .. Значение верхней левой ячейки в ссылке
    • coord…Абсолютная ссылка первой ячейки в ссылке
    • имя файла…Имя файла и полный путь

    Формат

    • … Формат номера ячейки
    • круглых скобок… 1 = круглые скобки для положительных или всех значений; иначе 0.
    • префикс …»префикс метки» ячейки (выравнивание)
    • защита…0 = не заблокировано, 1 = заблокировано.
    • ряд… Строка № ячейки
    • type…тип данных в ячейке (пусто, текст, другое)
    • ширина… Ширина столбца ячейки

Видео: используйте функцию INFO

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

Посмотрите это короткое видео, чтобы увидеть примеры использования
ИНФО функция.

Синтаксис INFO

Функция INFO имеет следующий синтаксис:

• INFO(type_text)
  • type_text — один из следующих элементов, указывающий, какую информацию вы хотите.
  • «каталог» Путь к текущему каталогу
  • «numfile» Количество активных листов в открытых книгах.
  • «origin» Абсолютная ссылка на верхнюю левую видимую ячейку
  • «osversion» Текущая версия операционной системы в виде текста.
  • «recalc» Текущий режим пересчета; «Автоматический» или «Ручной».
  • «Выпуск» версии Microsoft Excel, как текст.
  • «система» Имя операционной среды: «pcdos» или «mac»

Получить образцы файлов

1. Получить КЛЕТКУ
   файл примера функции. Файл заархивирован и находится в файле xlsx.
   формат
2. Получить ИНФОРМАЦИЮ
   файл примера функции. Файл заархивирован и находится в файле xlsx.
   формат

Учебные пособия по функциям

Функция АДРЕС

Функция ВПР

Функции ИНДЕКС / ПОИСКПОЗ

СЧЕТ / СЧЁТЕСЛИ

Функция ДВССЫЛ

30003

Последнее обновление: 13 июля 2021 г., 16:00

Функция Excel CELL | Learn Online

Главная/ Формулы/ Функция CELL в Excel – Возврат информации о ячейке

Очень мощная функция Excel, о которой многие не знают, – это функция CELL. Используйте функцию Excel CELL, чтобы найти информацию о ячейке. Это может включать формат номера ячейки, номер строки или то, заблокирована ли она или защищена.

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

Функция Excel CELL

Функция CELL — это информационная функция, для работы которой требуется два элемента информации.

 =CELL(  info_type  ,  ссылка  ) 

info_type — Тип информации, которую вы хотите вернуть о ячейке.

ссылка — ячейка, для которой вы хотите вернуть информацию. Если этот параметр опущен, функция Cell будет извлекать информацию из последней измененной ячейки.

Какую информацию вы можете получить о ячейке

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

info_type	Returns
“address”	Retrieves the cell’s reference as a text value
“col”	The column number of the cell
“color”	Возвращает 1, если ячейка отформатирована в цвете для отрицательных значений, в противном случае возвращает 0
«содержимое»	Получает значение в ячейке, а не формулу
«координата»	Возвращает абсолютную ссылку на ячейку файл, содержащий ячейку. Если файл еще не был сохранен, возвращается пустой текст («»)
«формат»	Числовой формат ячейки, возвращаемый как текстовое значение
«круглая скобка» 9) если текст выровнен по центру, обратная косая черта (\), если текст выровнен по заполнению и пустой текст («»), если он содержит что-либо еще
«защита»	Возвращает 0, если ячейка не заблокирована и 1 если он заблокирован
«строка»	Номер строки ячейки
«тип»	Возвращает тип данных в ячейке в виде текстового значения. Возвращает «b», если ячейка пуста, «l», если она содержит текстовую константу, и «v», если она содержит значение
«ширина»	Ширина столбца ячейки, округленная до целого числа

Примеры функции ЯЧЕЙКИ

Давайте посмотрим на некоторые примеры работы функции ЯЧЕЙКА Excel на основе приведенных ниже данных.

=ЯЧЕЙКА(«адрес», A2)

$A$2

=ЯЧЕЙКА(«формат», A2)

C2 (текстовое значение возвращается для числового формата £#,##0. Читайте также: 0 Содержание файла hosts: Содержимое файла hosts и его изменение 0 Как зайти на жесткий диск через биос: как установить жесткий диск в биосе 0 Как обновления запустить: Как обновить Windows 10/11 до посл. версии (+ как установить нужный патч вручную, например, KB5001391…) Разное Советы Старое Ещё Медицинский центр «Добрый доктор» в Минске: услуги, врачи, отзывы Развитие и уход за 3-недельным новорожденным: что нужно знать родителям Развитие ребенка в 3 месяца: важные этапы и советы родителям Омфалит у грудных детей: причины, симптомы и лечение синдрома мокнущего пупка Развитие речи у детей 3-4 лет: особенности, игры и занятия