Как получить значение ячейки в excel
Функция CELL возвращает запрошенную информацию об указанной ячейке, такую как местоположение ячейки, содержимое, форматирование и т. д.
Синтаксис
=CELL( info_type , [reference] )
аргументы
- info_type (обязательно): Текстовое значение, указывающее, какой тип информации о ячейке должен быть возвращен. Для получения дополнительной информации см. info_type таблица значений внизу.
- ссылка (необязательно): Ячейка для получения информации:
- Ссылка должна быть предоставлена в виде одной ячейки;
- Если указан диапазон ячеек, CELL получит информацию о верхней левой ячейке диапазона;
- Если опущено, будет возвращена информация об активной ячейке.
значения info_type
В таблице ниже перечислены все возможные значения, принимаемые функцией ЯЧЕЙКА, которые можно использовать в качестве info_type аргумент.
- Одинарная кавычка (‘), если содержимое ячейки выровнено по левому краю;
- Двойная кавычка («»), если содержимое ячейки выровнено по правому краю;
- Символ вставки (^), если содержимое ячейки расположено по центру;
- Обратная косая черта (\), если содержимое ячейки выровнено по заливке;
- Пустой текст (), если префикс метки другой
- «b» (пробел) для пустой ячейки;
- «l» (метка) для текстовой константы;
- «v» (значение) для всего остального
- Ширина столбца ячейки, округленная до ближайшего целого числа;
- Логическое значение: TRUE, если ширина столбца установлена по умолчанию, или FALSE в противном случае.
Примечание: Значения «цвет», «имя файла», «формат», «круглые скобки», «префикс», «защита» и «ширина» не поддерживаются в Excel в Интернете, Excel Mobile и Excel Starter.
Коды формата CELL
В таблице ниже показаны текстовые коды, которые соответствуют определенным числовым форматам ячейки, когда info_type установлен на «формат».
Возвращаемый код формата Соответствующий числовой формат G Общие F0 0 ,0 #,##0 F2 Условия возврата товара ,2 #,##0.00 C0 $ #, ## 0 _); ($ #, ## 0) С0- $#,##0_);[Красный]($#,##0) C2 $ #, ## 0.00 _); ($ #, ## 0.00) С2- $#,##0.00_);[Красный]($#,##0.00) P0 0% P2 0.00% S2 0.00E + 00 G # ?/? или # ??/?? D4 м / д / гг или м / д / гг ч: мм или мм / дд / гг D1 д-ммм-гг или дд-ммм-гг D2 д-ммм или дд-ммм D3 ммм-гг D5 мм / дд D7 ч: мм AM / PM D6 ч: мм: сс AM / PM D9 ч: мм D8 ч: мм: сс Примечание: Приведенные выше числовые форматы являются встроенными форматами. «-» будет добавлен в конце значений кода, если ячейка отформатирована в цвете для отрицательных значений. Если ячейка отформатирована со скобками для положительных или всех значений, в конце кода будет возвращено «()».
Возвращаемое значение
Функция CELL возвращает запрошенную информацию.
Примечания к функциям
- info_type должен быть заключен в двойные кавычки («»), если он непосредственно введен в формулу CELL. Если вы не вводите аргумент, а ссылаетесь на него, двойные кавычки не нужны.
- ссылка является необязательным для некоторых info_type ценности. Однако рекомендуется использовать такой адрес, как A1, чтобы избежать непредвиденных результатов.
- Вы должны пересчитать рабочий лист (нажмите F9), чтобы обновить результаты функции ЯЧЕЙКА, если позже вы примените другой формат к ячейке, на которую указывает ссылка.
- CELL вернет #СТОИМОСТЬ! ошибка если info_type не является одним из признанных типов.
- CELL вернет # ИМЯ? ошибка, если любой из аргументов является текстовым значением, не заключенным в двойные кавычки.
Пример
Чтобы получить информацию о ячейке B3 в соответствии с типами информации, как показано в таблице ниже, скопируйте или введите приведенную ниже формулу в верхней ячейке, нажмите Enter чтобы получить результат, а затем перетащите маркер заполнения (в правом нижнем углу ячейки результата) вниз, чтобы применить формулу к ячейкам ниже.
=ЯЧЕЙКА( C3 , 3 млрд долларов )
√ Примечание: Знаки доллара ($) выше обозначают абсолютные ссылки, что означает, что ссылка в формуле не изменится при перемещении или копировании формулы в другие ячейки. Тем не менее, знаки доллара не добавляются к info_type так как вы хотите, чтобы он был динамичным.
Также вы можете войти в info_type непосредственно в формуле, как показано ниже. Убедитесь, что он заключен в двойные кавычки:
=ЯЧЕЙКА( «адрес» , 3 млрд долларов )
Связанные функции
Функция Excel TYPE возвращает число, указывающее тип данных значения.
Функция Excel ERROR.TYPE возвращает число, соответствующее определенному значению ошибки. Если ошибки нет, ERROR.TYPE возвращает ошибку #Н/Д.
Функция Excel ROW возвращает номер строки ссылки.
Функция COLUMN возвращает номер столбца, в котором отображается формула, или номер столбца для данной ссылки.
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
Как получить значение ячейки в excel
= Мир MS Excel/Статьи об Excel
- 1
- 2
- 3
Функции листа. Функции баз данных [1] Функции листа. Функции даты и времени [5] Функции листа. Функции инженерные [1] Функции листа. Функции информационные [2] Функции листа. Функции логические [10] Функции листа. Функции математические [4] Функции листа. Функции статистические [1] Функции листа. Функции текстовые [1] Функции листа. Функции финансовые [1] Функции листа. Функции ссылок и массивов [2] Функции листа. Функции аналитические [1] Функции листа. Функции определённые пользователем [1] Функция диаграмм [1] Макрофункции [37] ПОЛУЧИТЬ.ЯЧЕЙКУ( ном_типа ; ссылка )
Ном_типа — число, определяющее, какой тип информации о ячейке вы хотите получить. Следующий список показывает возможные значения аргумента и соответствующие результаты.
1 Абсолютную ссылку верхней левой ячейки в аргументе ссылка в виде текста в текущем стиле рабочего пространства.
2 Номер строки верхней ячейки в аргументе ссылка.
3 Номер столбца самой верхней ячейки в аргументе ссылка.
4 То же, что и ТИП(ссылка).
5 Содержимое аргумента ссылка.
6 Формула в аргументе ссылка в виде текста, стиль которого А1 или R1C1 — в зависимости от параметров рабочего пространства.
7 Номер формата ячейки (например, «М/Д/ГГ» или «Основной»).
8 Число, показывающее горизонтальное выравнивание ячейки:52 Символ префикса ячейки (или выравнивание текста) или пустой текст (««), если ячейка не содержит текста.
53 Содержимое ячейки, если она в данных момент выведена на экран в виде текста, включающего любые дополнительные цифры или символы, являющиеся результатом форматирования ячейки.
54 Возвращает имя сводной таблицы, содержащей активную ячейку.
55 Возвращает положение ячейки внутри сводной таблицы.
56 Возвращает имя поля, содержащего ссылку на активную ячейку, если оно находится внутри сводной таблицы.
57 Если все символы ячейки или только первый символ форматированы с надстрочным шрифтом, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
58 Возвращает стиль шрифта в виде текста всех символов ячейки или только первого символа, как показано в диалоговом окне Формат ячеек на вкладке «Шрифт». Например, «полужирный курсив».59 Возвращает цифру для стиля «подчеркивание»:
60 Если все символы ячейки или только первый символ форматированы с подстрочным шрифтом, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
61 Возвращается имя элемента сводной таблицы для активной ячейки в виде текста.
62 Возвращает имя рабочей книги и текущего листа в форме «[Книга1]лист1».
63 Заполняет цветом ячейку (фон).
64 Возвращает узор фона ячейки.
65 Возвращает значение ИСТИНА, если включен параметр выравнивания доб_отступ (только для Microsoft Excel версии Far East); иначе возвращает ЛОЖЬ.
66 Возвращает имя рабочей книги, содержащей ячейку в форме BOOK1.XLS.Ссылка — ячейка или интервал ячеек, из которых вы хотите получить информацию.
Если ссылка является интервалом ячеек, в ней используется ячейка в верхнем левом углу первого интервала. Если аргумент опущен, предполагается активная ячейка.
Следующая макроформула возвращает значение ИСТИНА, если ячейка B4 на листе Лист1 выделена полужирным шрифтом:
Информацию, возвращенную функцией ПОЛУЧИТЬ.ЯЧЕЙКУ можно использовать для определения действия. Следующая макроформула выполняет пользовательскую функцию с именем BoldCell, если формула ПОЛУЧИТЬ.ЯЧЕЙКУ возвращает значение ЛОЖЬ:
Функция ЯЧЕЙКА (CELL)
Например, если перед выполнением вычислений с ячейкой необходимо удостовериться в том, что она содержит числовое значение, а не текст, можно использовать следующую формулу:
Эта формула вычисляет произведение A1*2, только если в ячейке A1 содержится числовое значение, и возвращает значение 0, если в ячейке A1 содержится текст или она пустая.Синтаксис
Аргументы
Тип_сведений Возвращаемое значение «адрес» Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки. «столбец» Номер столбца ячейки в аргументе «ссылка». «цвет» 1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях — 0 (ноль). «содержимое» Значение левой верхней ячейки в ссылке; не формула. «имяфайла» Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка («»). «формат» Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-«. Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()». «скобки» 1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях — 0. «префикс» Текстовое значение, соответствующее префиксу метки ячейки. Одиночная кавычка (‘) соответствует тексту, выровненному влево, двойная кавычка («) — тексту, выровненному вправо, знак крышки (^) — тексту, выровненному по центру, обратная косая черта () — тексту, распределенному по всей ширине ячейки, а пустой текст («») — любому другому содержимому ячейки. «защита» 0, если ячейка разблокирована, и 1, если ячейка заблокирована. «строка» Номер строки ячейки в аргументе «ссылка». «тип» Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» — текстовой константе в ячейке, «v» — любому другому содержимому. «ширина» Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера. Внимание! В Excel Web App нет поддержки аргумента «формат».
Коды форматов функции ЯЧЕЙКА
Формат Возвращаемое значение Общий «G» 0 «F0» # ##0 «,0» 0,00 «F2» # ##0,00 «,2» $# ##0_);($# ##0) «C0» $# ##0_);[Красный]($# ##0) «C0-« $# ##0,00_);($# ##0,00) «C2» $# ##0,00_);[Красный]($# ##0,00) «C2-« 0% «P0» 0,00% «P2» 0,00E+00 «S2» # ?/? или # ??/?? «G» д.м.гг или д.М.гг Ч:мм, или дд.ММ.гг «D4» д-МММ-гг или дд-МММ-гг «D1» д-МММ или дд-МММ «D2» МММ-гг «D3» дд.ММ «D5» Ч:мм AM/PM «D7» Ч:мм:сс AM/PM «D6» Ч:мм «D9» Ч:мм:сс «D8» Внимание! Если аргумент «тип_сведений» функции ЯЧЕЙКА имеет значение «формат», а формат ячейки был изменен, для обновления значения функции ЯЧЕЙКА необходимо пересчитать лист.
VBA в Excel Объект Excel.Range и программная работа с ячейками средствами VBA
Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист. Если в Word вы могли для ввода данных использовать как объект Range, так и объект Selection, то в Excel все сводится к объекту Range:
- если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
- если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
- если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.
В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:
- самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего). Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:
Dim oRange As Range
Set oRange = Worksheets(«Лист1»).Range(«A1»)
А на диапазон ячеек с A1 по D10 — так:
Dim oRange As Range
Set oRange = Worksheets(«Лист1»).Range(«A1:D10»)
С применением свойства Range самого объекта Range нужно быть очень осторожным. Дело в том, что Excel создает на основе объекта Range виртуальный лист со своей собственной нумерацией. Поэтому такой код:
Set oRange1 = Worksheets(«Лист1»).Range(«C1»)
Set oRange2 = oRange1.Range(«B1»)
пропишет значение 20 не в ячейку B1, как можно было понять из кода, а в ячейку D1 (то есть B1 по отношению к виртуальному листу, начинающемуся с C1).
- второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.). Например, для получения ссылки на ячейку D1 можно использовать код вида:
Dim oRange As Range
Set oRange = Worksheets(«Лист1»).Cells(1, 4)
Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:
Set oRange = Range(Cells(1, 1), Cells(5, 3))
- третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.
Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:
oRange.Value = «Мое значение»
Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много (и для комфортной работы в Excel их нужно знать). Ниже представлены некоторые самые употребимые свойства:
- Address — позволяет вернуть адрес текущего диапазона, например, для предыдущего примера вернется $A$1:$C$5. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.
На практике встречается множество ситуаций, когда адрес ячейки нужно разобрать на части и вернуть из него имя столбца или номер строки. Это очень просто сделать при помощи строковых функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так:
sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, «$») — 2))
sRowNumber = Mid(oRange.Address, (InStr(2, oRange.Address, «$») + 1))
На первый взгляд кажется сложным, но на самом деле все очень просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().
- AllowEdit — это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь править данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.
- Areas — свойство исключительно важное. Дело в том, что, как уже говорилось, объект Range может состоять из несмежных наборов ячеек. Многие методы применительно к таким диапазонам ведут себя совершенно непредсказуемо или просто возвращают ошибки. Свойство Areas позволяет разбить подобные нестандартные диапазоны на набор стандартных. Созданные таким образом объекты Range будут помещены в коллекцию Areas. Это свойство можно использовать и для проверки «нестандартности» диапазона:
If Selection.Areas.Count > 1 Then
Debug.Print «Диапазон с несмежными областями»
- Borders — возможность получить ссылку на коллекцию Borders, при помощи которой можно управлять рамками для нашего диапазона.
- Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона:
Dim oRange, oRange2 As Range
Set oRange = Range(Cells(2, 2), Cells(5, 3))
Set oRange2 = oRange.Cells(1, 1) ‘Вместо A1 получаем ссылку на B2
Debug.Print oRange2.Address ‘Так оно и есть
Точно такие же особенности у свойств Row и Rows, Column и Columns.
- Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. Например, чтобы ввести текст в ячейку A1 и изменить цвет первой буквы, можно воспользоваться кодом
Dim oRange As Range
Set oRange = Range(«A1»)
oRange.Value = «Мой текст»
oRange.Characters(1, 1).Font.Color = vbRed
Если же вам просто нужно изменить значение, то лучше воспользоваться свойством Value — как в третьей строке примера.
- Count — возвращает количество ячеек в диапазоне. Может использоваться для проверок.
- CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника (когда сколько будет этих данных, нам изначально неизвестно). Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка). Например, чтобы выделить всю непустую область вокруг активной ячейки, можно воспользоваться кодом
- Dependents — позволяет получить объект Range (скорее всего, включающий несмежные области) которые зависят от ячеек исходного диапазона. Работает только для текущего листа — ссылки во внешних листах этим свойством не отслеживаются. Например, чтобы выделить все ячейки, зависимые от активной, можно использовать код
- Worksheets(«Лист1»).Activate
- ActiveCell.Dependents.Select
Чтобы просмотреть обратную зависимость, можно использовать свойство Precedents. Чтобы просмотреть только первый уровень зависимостей, можно использовать свойства DirectDependents и DirectPrecedents.