Excel как выдать значение ячейки

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

​Смотрите также​​=АДРЕС(ПОИСКПОЗ(F4&МАКС(($A$2:$A$15=F4)*$B$2:$B$15);$A$2:$A$15&$B$2:$B$15;0)+1;2) — формула​ самом конце. Повторюсь:​​Ромич​ «»​Так выглядит абсолютный тип​​ записью;​​ которое соответствует номеру​ файла и название​ левой верхней ячейки​ диапазона C1:C11.​ противном случае функция​​Если в качестве аргумента​​ чтобы видеть все​​ ИНДЕКС является константной​​ возвращаемый функцией​

​– если TRUE​Вчера в марафоне​ массива.​​ неважно в каком​​: ну,дык, если в​Ромич​ ссылок по строкам​«Курсы» — название листа,​ столбца, в котором​ листа.​ в ссылке.​216​ ИНДЕКС возвращает значение​ «ссылка» используется несмежный​

​ данные.​ массива, используйте форму​​ADDRESS​​ (ИСТИНА) или вообще​30 функций Excel за​Zorge​ столбце мы кликнем​ А2 вводить новое​: а если не​ и столбцам при​

Функция 20: ADDRESS (АДРЕС)

​ на котором находится​​ расположена искомая ячейка;​​=CELL(«filename»,B2)​filename​=СУММ(B2:ИНДЕКС(A2:C6;5;2))​ ошибки #ССЫЛКА!. Если​ диапазон, его необходимо​Данные​ массива.​(АДРЕС), как аргумент​​ не указано, функция​​ 30 дней​​: Извините, что пропал.​​ на гиперссылку -​ значение,то предыдущее стоит​ в соседнюю ячейку?​ использовании стиля R1C1.​

Как можно использовать функцию ADDRESS (АДРЕС)?

​ таблица с искомой​​[тип_ссылки] – число из​​=ЯЧЕЙКА(«имяфайла»;B2)​(имяфайла) – имя​Сумма значений из диапазона,​ аргументы «номер_строки» и​ заключить в скобки​

• ​Данные​ИНДЕКС(массив; номер_строки; [номер_столбца])​ для​
• ​ возвращает ссылку в​мы находили элементы​По хорошему, адрес​
• ​ она всегда приведёт​ стереть?и,соответственно, адреса этого​

Синтаксис ADDRESS (АДРЕС)

​Юрий М​​Ромич​​ ячейкой.​ диапазона от 1​

​Из полученного результата при​
​ файла и полный​

• ​ начинающегося с ячейки​​ «номер_столбца» опущены, функция​Если каждая область в​​Яблоки​​Аргументы функции ИНДЕКС описаны​INDIRECT​ стиле​ массива при помощи​ ячейки мне нужен,​ к нужному адресу.​​ имени тоже стираются?или​​: Тогда не Offset(0,​​: Народ, прошу прощения​​Для расчета стоимости в​​ до 4, соответствующее​​ помощи других функций​
• ​ путь.​​ B2 и заканчивающегося​ ИНДЕКС возвращает область​ ссылке содержит только​Лимоны​ ниже.​​(ДВССЫЛ). Мы изучим​​A1​ функции​​ для того, чтобы​​Zorge​
• ​ я что-то не​​ 1)​​ за назойливость, но​ рублях используем формулу:​ одному из типов​ Вы можете извлечь​format​ пересечением пятой строки​

Ловушки ADDRESS (АДРЕС)

​ в аргументе «ссылка»,​​ одну строку или​​Бананы​Массив​ функцию​, если FALSE (ЛОЖЬ),​MATCH​ можно было выбрать​: И снова добрый​​ так понял?​​Владимир​ вот интересно стало,​ =B3*ДВССЫЛ(D3).​ возвращаемой ссылки на​

Пример 1: Получаем адрес ячейки по номеру строки и столбца

​ название листа. В​​(формат) – числовой​​ и второго столбца​ заданную аргументом «номер_области».​ один столбец, аргумент​Груши​    — обязательный аргумент. Диапазон​INDIRECT​ то в стиле​(ПОИСКПОЗ) и обнаружили,​ значение из другого​ день, товарищи бойцы.​​Юрий М​​: Пока я телился…​

​ можно ли по​
​Функция ДВССЫЛ необходима для​

Абсолютная или относительная

​ ячейку:​ формуле ниже, используя​​ формат ячейки.​​ диапазона A2:A6, т. е.​Результатом вычисления функции ИНДЕКС​ «номер_строки» или «номер_столбца»​

​Формула​ ячеек или константа​(ДВССЫЛ) позже в​R1C1​​ что она отлично​​ столбца, соответствующее той​​И снова проблема,​​: Не так. Ваш​

​=АДРЕС(МАКС(ЕСЛИ(Лист1!$A$1:$G$20=A2;(СТРОКА(Лист1!$1:$20))));МАКС(ЕСЛИ(Лист1!$A$1:$G$20=A2;СТОЛБЕЦ(Лист1!$A:$G)));4)​
​ имени(значению) получать адрес,​

A1 или R1C1

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

​.​
​ работает в команде​

Название листа

​ самой максимальной величине.​ выеденного яйца не​ последний файл. Ранее​Ромич​ а лучше даже​ хранимого в ячейке,​​ например — $A$4​​MID​

​(скобки) – возвращает​
​ диапазона B2:B6.​

Пример 2: Находим значение ячейки, используя номер строки и столбца

​ интерпретируется в качестве​​ Например, для ссылки​​Результат​Если массив содержит только​30 функций Excel за​sheet​ с другими функциями,​Вопрос мне показался​ стоящая.​ для АБВ нашлись​​: вместо 1 -​​ ссылку на эту​ на которую указывает​​абсолютная только на строку,​​(ПСТР) и​ 1, если форматированием​​2,42​​ таковой другими функциями.​ на единственную строку​​=ИНДЕКС(A2:B3;2;2)​ одну строку или​​ 30 дней​

​_text​
​ такими как​

​ простым потому, что​​Имею массив данных,​​ три адреса. Затем​ число шагов до​​ ячейку? Ну,например, на​​ ссылка. В результате​ например — A$4;​FIND​​ ячейки предусмотрено отображение​​К началу страницы​ В зависимости от​​ нужно использовать формулу​​Значение ячейки на пересечении​ один столбец, соответствующий​

​.​
​(имя_листа) – имя​

​VLOOKUP​​ в VB, если​​ из которых отбираю​ в А3 ввели​ нужного столбца?​ одном листе есть​

​ вычислений для остальных​
​абсолютная только на столбец,​

​(НАЙТИ), находим квадратные​​ положительных или всех​В 4-ый день марафона​ формулы значение, возвращаемое​

Пример 3: Возвращаем адрес ячейки с максимальным значением

​ ИНДЕКС(ссылка,,номер_столбца).​ второй строки и​ аргумент «номер_строки» или​=INDIRECT(ADDRESS(C2,C3))​​ листа может быть​​(ВПР) и​ я назначаю переменной​

​ уникальные значения из​​ новое имя. В​​Юрий М​ ячейка(А2) со значением​ товаров получим следующую​

​ например - $A4;​
​ скобки и возвращаем​

​ чисел в круглых​30 функций Excel за​​ функцией ИНДЕКС, может​​Номер_строки​ второго столбца в​​ «номер_столбца» не является​​=ДВССЫЛ(АДРЕС(C2;C3))​ указано, если Вы​​INDEX​​ какую-либо ячейку я​ повторяющихся и формулой​

​ В3 отобразился новый​
​: Точно: (0, 5)​

​ 123. А во​ таблицу:​
​относительная на всю ячейку,​
​ 32 символа, которые​

​ скобках; во всех​

office-guru.ru

ИНДЕКС (функция ИНДЕКС)

​ 30 дней​ использоваться как ссылка​    — обязательный аргумент. Номер​ диапазоне A2:B3.​

​ обязательным.​Функция​

• ​ желаете видеть его​(ИНДЕКС).​ могу посмотреть и​ МАКС нахожу из​ адрес. В В4​

• ​ на пять столбцов​ втором листе при​Пример 2. В таблице​ например A4.​

Форма массива

Описание

​ следуют за ними​ остальных случаях возвращает​мы получали детальные​ или как значение.​

​ строки в диапазоне,​Груши​Если массив содержит больше​INDIRECT​

Синтаксис

​ в возвращаемом функцией​

​20-й день нашего марафона​ её адрес и​

• ​ них наибольшие. Это​​ — осталось значение​ правее в той​ вводе этого значения,​

  • ​ содержатся данные о​[a1] – логическое значение,​ (длина названия листа​ 0 (ноль).​ сведения о рабочей​ Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2))​

  • ​ заданном аргументом «ссылка»,​=ИНДЕКС(A2:B3;2;1)​ одной строки и​(ДВССЫЛ) может работать​ результате.​ мы посвятим изучению​ её значение вне​ к тому, что​ для ячейки А2.​ же строке (ноль).​

• ​ например, в столбец​​ цене товаров, отсортированные​ определяющее один из​ ограничена 31 символом).​prefix​ среде, используя функцию​ эквивалентна формуле ЯЧЕЙКА(«ширина»;B1).​ из которого требуется​

• ​Значение ячейки на пересечении​​ одного столбца, а​ и без функции​Функция​ функции​ зависимости от того​ уникальных значений много​ Если мы его​

Замечания

• ​Ромич​ ИМЯ, в соседнем​ в порядке возрастания​ двух типов ссылок:​=MID(CELL(«filename»,C3),FIND(«]»,CELL(«filename»,C3))+1,32)​(префикс) – текстовое​INFO​

• ​ Функция ЯЧЕЙКА использует​ возвратить ссылку.​ второй строки и​ из аргументов «номер_строки»​ADDRESS​ADDRESS​ADDRESS​ сам ли я​ и к каждому​ очистим — список​: Юрий, огромное спасибо!​ столбце получаем ссылку​ стоимости. Необходимо получить​ A1 либо R1C1;​=ПСТР(ЯЧЕЙКА(«имяфайла»;C3);НАЙТИ(«]»;ЯЧЕЙКА(«имяфайла»;C3))+1;32)​ значение, соответствующее префиксу​(ИНФОРМ), – такие​ значение, возвращаемое функцией​

  ​Номер_столбца​​ первого столбца в​ и «номер_столбца» задан​(АДРЕС). Вот как​

• ​(АДРЕС) возвращает лишь​(АДРЕС). Она возвращает​ назначаю эту ячейку​ из них выбирается​ адресов для А2​ как и остальным​ на Лист1!А2​

Примеры

Пример 1

​ ссылки на ячейки​[имя_листа] – текстовое значение,​Вместо того, чтобы вводить​ метки ячейки (показывает​ как версия Excel​ ИНДЕКС, как ссылку.​

​    — необязательный аргумент. Номер​ диапазоне A2:B3.​ только один, функция​ можно, используя оператор​ адрес ячейки в​ адрес ячейки в​ или аона является​ максимальное из первоначального​ будет неполным. Поэтому​ форумчанам — с​KuklP​ с минимальной и​ которое определяет имя​

Пример 2

​ заданном аргументом «ссылка»,​В этом примере функция​ состоящий из целой​&​ Если Вам нужно​ номер строки и​ За сим прилагаю​

​Собственно вопрос: Каким​ строку все адреса​ excel и проще,​ Ща наши киты​ соответственно.​ Excel. Используется для​(тип_сведений) в функцию​(защита) — 0​ посвятим изучению функции​ 2*ИНДЕКС(A1:B2;1;2), преобразует значение,​ из которого требуется​ ИНДЕКС используется в​

​ более обширным.​​ адрес ячейки, данные​ Тогда и путаницы​и еще в​ Индекс порулит… Неисповедимы​ вид:​Примечания:​(ЯЧЕЙКА) в виде​ заблокирована, 1 =​(ЯЧЕЙКА), которая сообщит​ в число в​Номер_области​ поиска значений двух​Номер_строки​R1C1​

​ аргумента функции​

Ссылочная форма

Описание

​ Можно ли сделать​Благодарю за оперативность.​ из которой мне​ не будет никакой.Теперь​ завершение. можно ли​ пути формулистов:-) Только​Для получения ссылки на​Ссылки типа R1C1 используются​ текстовой строки, Вы​

Синтаксис

​ заблокирована.​

​ информацию о форматировании​ ячейке B1.​

• ​    — необязательный аргумент. Диапазон​​ заданных ячеек в​     — обязательный аргумент.​и в результате​

  • ​INDIRECT​ то же самое​vikttur​ показывает формула? Адрес,​

  • ​ понятно объяснил?​ сделать получаемый адрес​ Ким жаловался, что​ ячейку с минимальной​ для цифрового обозначения​ можете указать ссылку​row​ ячейки, её содержимом​Примечание:​ в аргументе «ссылка»,​

• ​ массиве с диапазоном​​ Выбирает строку в​ получить значение ячейки:​(ДВССЫЛ) или примените​ с помощью других​: Например, номер договора:​

• ​ Индекс и Ячейка​​Ромич​ гиперссылкой?​ формулисты не в​ стоимостью товара используем​ столбцов и строк.​

• ​ на ячейку, содержащую​​(строка) – номер​ и расположении. Она​  Функция ЯЧЕЙКА недоступна​ из которого требуется​ 2 x 2.​ массиве, из которой​=INDIRECT(«R»&C2&»C»&C3,FALSE)​ одну из альтернативных​ функций?​=ИНДЕКС($D$2:$D$15;ПОИСКПОЗ(МАКС(СМЕЩ(B1;ПОИСКПОЗ(F4;$A$2:$A$15;0);0;СЧЁТЕСЛИ($A$2:$A$15;F4)));$B$2:$B$15;))​ действуют диаметрально проивоположным​: нет.Имя по задумке​Юрий М​ фаворе.​ формулу:​ Для возвращения ссылок​ корректные значения. В​ строки ячейки.​ работает аналогично функции​ в Excel Web​ возвратить значение ячейки​В этом случае нужно​ требуется возвратить значение.​=ДВССЫЛ(«R»&C2&»C»&C3;ЛОЖЬ)​ формул, показанных в​Давайте обратимся к сведениям​СМЕЩ() лучше заменить​ образом.​ вводится ТОЛЬКО в​: в завершение. можно​Ки-им! Ваш выход.​Функция АДРЕС принимает следующие​

​ такого типа в​ этом примере в​type​INFO​ App.​ на пересечении строки​ выбрать любые две​ Если аргумент «номер_строки»​Функция​

Замечания

• ​ примере 2.​ по функции​ на ИНДЕКС (сообщение​Заранее благодарю и​ ячейку А2. Ввел,посмотрел(в​ ли сделать получаемый​ Я за Вас​ параметры:​ качестве параметра a1​ ячейке B4 находится​(тип) – тип​(ИНФОРМ), т.е. имеет​Скопируйте образец данных из​ и столбца, заданных​ ячейки по вертикали,​ опущен, аргумент «номер_столбца»​

• ​INDEX​При помощи функции​ADDRESS​ 29.07.2011, 18:52)​ удачной пятницы​ оригинальном файле еще​ адрес гиперссылкой?{/post}{/quote}​

• ​ переживаю.​число, соответствующее номеру строки​ должно быть явно​ выпадающий список, а​ данных в ячейке​ список значений, которые​ следующей таблицы и​ аргументами «номер_строки» и​ например A1:A2, вставить​ является обязательным.​(ИНДЕКС) также может​ADDRESS​

• ​(АДРЕС) и изучим​Если возможны совпадения​Михаил С.​ туча параметров выводится),​Насчёт завершения -​Ромич​ с минимальным значением​ указано логическое значение​ вместо аргумента​ (пустая, текст, другое).​ можно ввести в​ вставьте их в​ «номер_столбца». Первая введенная​ в первую ячейку​Номер_столбца​ вернуть значение ячейки,​(АДРЕС) Вы можете​ примеры работы с​ максимумов для разных​: Вроде уже около​

​ стер. Если надо,ввел​​ сомнительно: начиналось с​: впр не покатит​ цены (функция МИН​

Примеры

​ ЛОЖЬ или соответствующее​info_type​width​ функцию, но содержит​ ячейку A1 нового​ или выделенная область​ формулу =ИНДЕКС({1;2:3;4};0;2) и​     — необязательный аргумент.​ если указан номер​ получить адрес ячейки​ ней. Если у​ названий, диапазон ИНДЕКС($D$2:$D$15;…​ 40 ответов, а​

​ Если аргумент «номер_столбца»​

support.office.com

30 функций Excel за 30 дней: ЯЧЕЙКА (CELL)

​1:5000​​ Вы введёте только​ в комментариях.​​: Спасибо всем огромное.​ Вас придумывать будет?​ столбце В отображались​​ вопросов больше нет​​KuklP​ содержащей минимальное значение​ пункта меню «Стиль​

​(ссылка) ссылается на​ следить, используя функцию​​ функции​​ а затем — клавишу​ ИНДЕКС используется область 1. ​ ее во вторую​ опущен, аргумент «номер_строки»​– это первые​​ эти два аргумента,​​Функция​Заработало.​vikttur​ адреса в промежутках​ Смотрите с​: Ромич, честно каюсь​

​ цены. К полученному​ ссылок R1C1», который​ ячейку B2.​​CELL​​CELL​ ВВОД. При необходимости​ Все указанные здесь​ ячейку.​ является обязательным.​ 5000 строк листа​

Функция 11: CELL (ЯЧЕЙКА)

​ результатом будет абсолютный​​ADDRESS​​k1ava​: И Вам удачных​ А2:К200 и АВ2:АХ200,​ гиперссылкой​

Как можно использовать функцию CELL (ЯЧЕЙКА)?

​ — я не​​ значению добавлено 2,​​ находится в «Файл​Когда выбрано значение​(ЯЧЕЙКА):​

• ​(ЯЧЕЙКА). Если у​
• ​ измените ширину столбцов,​
• ​ области должны находиться​

Синтаксис CELL (ЯЧЕЙКА)

​Формула​​Если используются оба аргумента​​ Excel.​ адрес, записанный в​

​(АДРЕС) возвращает ссылку​
​: К примеру в​

​ выходных. Может, за​​ а в столбце​Guest​ формулист. Как у​

• ​ поскольку ПОИСКПОЗ осуществляет​​ – Параметры –​protect​Если аргумент​​ Вас есть что​​ чтобы видеть все​ на одном листе. ​
• ​Описание​​ — и «номер_строки»,​В этом примере мы​ стиле ссылок​​ на ячейку в​​ столбце A в​
• ​ два дня справитесь​​ С — адреса​: Прекрайне благодарен!​ них в избушке​ поиск относительно диапазона​ Формулы – Работа​(защита): результат равен​reference​
• ​ добавить к нашим​​ данные.​ Если указать области,​Результат​
• ​ и «номер_столбца», —​​ найдём ячейку с​A1​ виде текста, основываясь​
• ​ одном ряду значение​​ с задачей :)​ промежутка L2:AA200)? Ну​
• ​Владимир​​ побываю — в​ выбранных ячеек.​ с Формулами».​ 1, если ячейка​(ссылка) опущен, то​ примерам и сведениям,​Фрукты​ находящиеся на разных​
• ​=ИНДЕКС({1;2​​ функция ИНДЕКС возвращает​ максимальным значением и​.​ на номере строки​
• ​ одной ячейки x​​Или все-таки пример​ или что-то в​: Как понять это​ шоке! Они там​
• ​2 – номер столбца,​​Если требуется ссылка на​ заблокирована, или 0​
• ​ результат возвращается для​​ пожалуйста, делитесь этим​Цена​ листах, произойдет ошибка​
• ​:​​ значение ячейки на​ используем функцию​

Ловушки функции CELL (ЯЧЕЙКА)

​=ADDRESS($C$2,$C$3)​ и столбца. Она​ , значение другой​​ покажете?​​ этом роде​

• ​ слово «прекрайне»? Бесконечно…​​ такое мутят, что​​ в котором находится​ ячейку, которая находится​ (ноль), если не​ последней изменённой ячейки.​ в комментариях.​Количество​ #ЗНАЧ!  Если необходимо​3;4};0;2)​ пересечении указанных строки​ADDRESS​=АДРЕС($C$2;$C$3)​ может возвращать абсолютный​​ y.​​Михаил С.​
• ​Юрий М​​ …или крайность, в​​ не пойму сам:​ искомая ячейка.​ в другом листе​ заблокирована.​
• ​ Чтобы быть уверенным,​​Функция​​Яблоки​​ использовать диапазоны, находящиеся​​Значение ячейки на пересечении​ и столбца.​(АДРЕС), чтобы получить​Если не указывать значение​

Пример 1: Формат числа в ячейке

​ или относительный адрес​​Задача в том​​: И зачем Вам​: Если нужно контролировать​​ хорошем смысле слова…​​ то ли я​Аналогичным способом получаем ссылку​ данного документа Excel,​=CELL(B4,B2)​​ что результат получился​​CELL​0,69 ₽​​ на разных листах,​​ первой строки и​

​Если указать в качестве​
​ её адрес.​

Пример 2: Название листа

​ аргумента​​ в стиле ссылок​​ что бы дать​​ именно адрес?​​ только одну ячейку​Юрий М​ дурак(а это скорее!),​ на ячейку с​

​ полезно использовать параметр​
​=ЯЧЕЙКА(B4;B2)​

​ именно тот, что​(ЯЧЕЙКА) показывает информацию​40​ рекомендуется применить функцию​ второго столбца в​ аргумента «номер_строки» или​​Функция​​abs_num​​A1​​ еще одной ячейки​Zorge​ — зачем тогда​: Через край, наверное…​ то ли они​ максимальной ценой товара.​

​ [имя_листа], который принимает​
​Когда выбрано значение​

Пример 3: Подстановка аргумента info_type (тип_сведений) из выпадающего списка

​ Вам нужен, ссылку​ о форматировании, содержании​​Бананы​​ ИНДЕКС в форме​​ массиве. Массив содержит​​ «номер_столбца» значение 0​MAX​(тип_ссылки) в формуле,​или​ значение z​: Благодарю за оперативность.​ обрабатываем диапазон? Сформулируйте​Ромич​ иноПланетяне. Ждем наших​​ В результате получим:​​ текстовое значение, соответствующее​width​ желательно всегда указывать.​​ и расположении ячейки​​0,34 ₽​ массива и использовать​

​ значения 1 и​​ (ноль), функция ИНДЕКС​​(МАКС) находит максимальное​ то результатом будет​R1C1​Такое возможно в​Я так понимаю,​

​ уже условие ЗАНОВО​
​: См. значение префикса​

​ уважаемых Формулистов. Хотя​​Пример 3. В таблице​​ названию требуемого листа,​(ширина), результат показывает​ Вы можете сослаться​ по заданной ссылке.​38​ другую функцию для​ 2 в первой​ возвратит массив значений​

​ число в столбце​ абсолютная ссылка.​
​. К тому же​
​ exel?​

​ что даже не​

office-guru.ru

Примеры функции АДРЕС для получения адреса ячейки листа Excel

​ и ОДНОЗНАЧНО.​ ПРЕ-​ … макросом можно.​ содержится ячейка, данные​ например «Лист7».​ ширину столбца в​ даже ячейку, в​Функция​Лимоны​

​ вычисления диапазона, составляющего​ строке и значения​ для целого столбца​ C.​Чтобы увидеть адрес в​ в результат может​Если возможно, то​ в данных суть,​

Функция АДРЕС в Excel: описание особенностей синтаксиса

​По второму вопросу​В общем, «через​

​ Ща меня ногами​

​ из которой используются​​ формате целого числа.​

​ которой содержится сама​

• ​CELL​0,55 ₽​ массив.  Например, определить​ 3 и 4​
• ​ или целой строки​=MAX(C3:C8)​ виде относительной ссылки,​ быть включено имя​
• ​ как реализовать.​ а в возможности/невозможности​ — зачем распределять​ край» — это​ формулисты запинают:-).​ в другом программном​

1. ​Пример 1. В таблице​ Единицей измерения в​
2. ​ функция​(ЯЧЕЙКА) может сообщить​
3. ​15​ используемый диапазон можно​
4. ​ во второй строке.​ соответственно. Чтобы использовать​

• ​=МАКС(C3:C8)​ можно подставить в​ листа.​Спасибо заранее за​
• ​ выполнения.​ найденные значения-адреса по​ близко​П.С. 11979 -​ продукте. Для обеспечения​

​ Excel содержится ячейка,​

1. ​ этом случае является​CELL​ о ячейке информацию​Апельсины​ с помощью функции​2​ значения, возвращенные как​Далее в игру вступает​ качестве аргумента​Функция​
2. ​ любые ответы…​Вот примерно такой​ разным столбцам (это​Ромич​ это две пары?​ совместимости необходимо предоставить​ отображающая динамически изменяемые​ ширина одного символа​(ЯЧЕЙКА).​
3. ​ следующего содержания:​0,25 ₽​ ВЫБОР.​Значение ячейки на пересечении​ массив, введите функцию​ функция​abs_num​ADDRESS​Haken​

​ пример получился.​

Примеры использования функции АДРЕС в Excel

​ сложнее), если в​: в завершение. можно​ Никогда не играл​ ссылку на нее​ данные в зависимости​ при стандартном размере​При работе с функцией​Числовой формат ячейки.​25​Например, если аргумент «ссылка»​ второй строки и​ ИНДЕКС как формула​

​ADDRESS​(тип_ссылки) значение​(АДРЕС) может возвратить​

​: Встаете на ячейку,​Нужен этот адрес,​ ячейках ГИПЕРССЫЛКИ? Неважно​ ли сделать получаемый​ в игры, это​

​ в виде R1C1.​ от определенных условий.​ шрифта.​CELL​Название листа.​Груши​ определяет ячейки (A1:B4,D1:E4,G1:H4),​ второго столбца в​ массива в горизонтальный​

​(АДРЕС) в сочетании​

• ​4​ адрес ячейки или​ пишете​
• ​ для работы с​ из какого столбца​
• ​ адрес гиперссылкой?{/post}{/quote}​ уже здесь подхватил.​
• ​Исходная таблица имеет следующий​ Для работы с​Урок подготовлен для Вас​
• ​(ЯЧЕЙКА), иногда необходимо​Выравнивание или ширину столбца.​0,59 ₽​ номер области 1​

​ массиве, указанном выше.​ диапазон ячеек для​ с​

​.​ работать в сочетании​=A1+A2​ первоначальным массивом. Например,​ сработает гиперссылка?​Насчёт завершения -​ От «Казанский».​ вид:​

Как получить адрес ссылки на ячейку Excel?

​ актуальными данными в​ командой сайта office-guru.ru​ делать пересчёт листа,​Функция​40​ соответствует диапазону A1:B4,​4​ строки и в​MATCH​

​=ADDRESS($C$2,$C$3,4)​ с другими функциями,​

​ну или другую​ если яблоки и​Ромич​ сомнительно: начиналось с​

​Юрий М​Для получения ссылки на​

• ​ таблице, которая находится​Источник: http://blog.contextures.com/archives/2011/01/12/30-excel-functions-in-30-days-11-cell/​ чтобы обновить результат,​CELL​Миндаль​ номер области 2​Примечание:​ вертикальный — для столбца.​(ПОИСКПОЗ), которая находит​=АДРЕС($C$2;$C$3;4)​ чтобы:​ формулу, по которой​ груши от разных​
• ​: Хорошо,сейчас попробую.​ соседнего столбца. Пока​: Вот так, например.​

​ ячейку B6 используем​ на другом листе​Перевел: Антон Андронов​ который возвращает функция.​

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

​(ЯЧЕЙКА) имеет вот​2,80 ₽​ — диапазону D1:E4,​ Это формула массива, которую​ Чтобы ввести формулу​ номер строки, и​Чтобы задать стиль ссылок​Получить адрес ячейки, зная​

​ должно считаться значение​ поставщиков.​

​Есть таблица, при​ накопите вопросы.{/post}{/quote}​kim​

​ следующую формулу: =АДРЕС(6;2;1;0).​

• ​ документа требуется получить​Автор: Антон Андронов​
• ​Если в качестве аргумента​ такой синтаксис:​10​
• ​ а номер области​ нужно вводить с​
• ​ массива, нажмите сочетание​COLUMN​

​R1C1​

​ номер строки и​ третьей ячейки…​MCH​ вводе в первую​Юрий, а что​

​: Дык автор конкретно​Аргументы функции:​ ссылку на данную​Функция АДРЕС возвращает адрес​info_type​CELL(info_type,reference)​Кешью​ 3 — диапазону​ помощью клавиш CTRL+SHIFT+ВВОД.​ клавиш CTRL+SHIFT+ВВОД.​(СТОЛБЕЦ), которая определяет​, вместо принятого по​ столбца.​

​Serge​: И зачем Вам​ ячейку которой Имени​ нужно дописать в​

exceltable.com

поиск адреса ячейки по значению

​ формулой вроде не​​6 – номер строки​ ячейку.​ определенной ячейки (текстовое​(тип_сведений) выбрано значение​ЯЧЕЙКА(тип_сведений;ссылка)​3,55 ₽​ G1:H4.​ Excel автоматически заключит​Примечание:​ номер столбца.​ умолчанию стиля​Найти значение ячейки, зная​: k1ava, а теперь​ всетаки адрес? что​ мы должны получить​ этом макросе,чтобы он​ просил…​

​ искомой ячейки;​​На листе «Курсы» создана​ значение), на которую​filename​info_type​16​После того как с​ формулу в фигурные​  В Excel Web​
​=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))​A1​ номер строки и​

​ можно ещё раз​​ Вы с ним​ адреса расположения(гиперссылки) на​ выдавал не один​Ну да ладно,​2 – номер столбца,​

​ таблица с актуальными​​ указывают номера столбца​(имяфайла), а книга​(тип_сведений) — это​Арахис​ помощью аргументов «ссылка»​ скобки {}. Если​ App невозможно создавать​=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))​, Вы должны указать​ столбца.​ и по-русски?​ будете делать?​ схеме.​ адрес, а несколько,списком(если,например,имя​ вот формульный вариант​ в котором содержится​
​ курсами валют:​ и строки. К​ Excel ещё не​ один из вариантов​1,25 ₽​ и «номер_области» выбран​

​ вы попытаетесь ввести​​ формулы массива.​

​Урок подготовлен для Вас​​ значение FALSE (ЛОЖЬ)​Возвратить адрес ячейки с​Понятно и с​
​Михаил С.​По поводу второй​

​ встречается неоднократно).​​Ромич​ ячейка;​На отдельном листе «Цены»​ примеру, в результате​

​ сохранена, то результатом​​ аргумента:​20​ диапазон, с помощью​ их вручную, Excel​Аргументы «номер_строки» и «номер_столбца»​ командой сайта office-guru.ru​ для аргумента​ самым большим значением.​ примером в Excel?​
​: Или так​ части. Дело в​Юрий М​: Юрий, а можно​
​1 – тип ссылки​ создана таблица с​ выполнения функции =АДРЕС(5;7)​
​ будет пустая строка.​
​address​Грецкие орехи​

​ аргументов «номер_строки» и​​ отобразит формулу как​ должны указывать на​

​Источник: http://blog.contextures.com/archives/2011/01/21/30-excel-functions-in-30-days-20-address/​​а1​Функция​

​Guest​​Михаил С.​
​ том, что как​

​: А каким образом​​ поподробнее об этом​ (абсолютная);​ товарами, отображающая стоимость​

​ будет выведено значение​​Со значением​(адрес) – ссылка​1,75 ₽​ «номер_столбца» выбирается конкретная​

​ в долларах США​​ $G$5.​format​ на первую ячейку​
​12​ ячейка: номер строки​ вводе формулы не​ массива; в противном​

​Автор: Антон Андронов​​=ADDRESS($C$2,$C$3,1,FALSE)​(АДРЕС) имеет вот​Надо что бы​ файл сохранил …​

​ какого столбца работает​​ этот список?​

​ чему?​​ стиль R1C1.​ (USD):​Примечание: наличие символов «$»​(формат) Вы можете​

​ в аргументе​​Формула​

​ 1 соответствует первой​​ нажать клавиши CTRL+SHIFT+ВВОД,​ случае функция ИНДЕКС​
​Функция ИНДЕКС возвращает значение​=АДРЕС($C$2;$C$3;1;ЛОЖЬ)​ такой синтаксис:​

​ или ссылку на​​Последний аргумент – это​ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])​ Питания ставилось только​

​CELL​​(ссылка) в текстовом​Результат​ столбца 1 —​ #ЗНАЧ!​ #ССЫЛКА!.​ значение из таблицы​ имя листа. Если​АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])​ если в полях​
​: Нелетучая формула извлечения​ это не суть​

​А еще был​​ отслеживает изменения в​ Поиск значения в​ таблицы, находящейся на​ том, что ссылка​(ЯЧЕЙКА), чтобы показать​ формате.​=ИНДЕКС(A2:C6;2;3)​

​ его первому столбцу​​К началу страницы​В этих примерах функция​ или диапазона.​ Вам необходимо это​abs_num​ «должность» и «График»​ МАКС:​ важно. Есть другой​ бы рад, если​ диапазоне А2:А1000, запускает​ диапазоне таблицы Excel​ листе «Курсы», в​ на данную ячейку​ числовой формат ячейки.​col​Пересечение второй строки и​ и т. д.​Возвращает ссылку на ячейку,​ ИНДЕКС используется для​Функцию ИНДЕКС можно использовать​

​ и «Пятидневка», в​​И все-таки, для​ слишком запарно​ в тексте программы​ поиск. Если находит,​ строкам.​ о курсе валюты​
​ есть не меняется​ B7 имеет формат​ столбца ячейки в​ диапазоне A2:C6, т. е.​ ИНДЕКС, указывает на​ указанной строки и​ находящейся на пересечении​Если вы хотите возвращать​

​ в качестве аргумента​​1​
​ противном случае поле​ чего адрес? Поверьте​Юрий М​ комментарии)​ то в соседнюю​Примечание: при использовании стиля​
​ USD. Для этого​ при копировании данных.​General​ аргументе​ содержимое ячейки C3.​ пересечение строки «номер_строки»​ указанного столбца. Если​ заданных строки и​ значение указанной ячейки​sheet_text​или вообще не​

​ остается пустым.​​ опыту: АДРЕС() практически​
​: Уточняемся:​Юрий М​ ячейку записывает адрес​
​ R1C1 запись абсолютной​ введем следующую формулу:​Функция АДРЕС имеет следующую​
​(Общий), то результатом​reference​
​38​
​ и столбца «номер_столбца».​ ссылка составлена из​ столбца.​ или массива ячеек,​(имя_листа).​ указано, то функция​Guest​ не используется, эта​1. Значение всегда​

planetaexcel.ru

Узнать адрес ячейки по отобранному формулой значению

​ массива.​​=АДРЕС($C$2;$C$3;1;ИСТИНА;»Ex02″)​ ($A$1). Чтобы получить​ Прикрепленные файлы post_114193.jpg​ но в тексте,​
​ в ячейку А2.​ введём новое имя?​

​Кстати, нужно добавить​​ отличать абсолютные и​3 – номер строки,​Первые два аргумента данной​:​
​(цвет) – возвращается​ второго столбца во​

​ «номер_столбца» значение 0​​ из них требуется​ вставьте их в​

​ использовать.​​ ячейку A1 нового​ на указанные ячейки,​ADDRESS​ используйте значение​

​kim​​ в дальнейшем можно,​

​ адресов отображать в​​ строке вправо? Тогда​ после неудачного поиска:​ квадратные скобки «[]».​

​ нажмите клавишу F2,​​ или массив, заданного​
​ как действующую ссылку.​

planetaexcel.ru

Как автоматически задать значение ячейки основываясь на данных других ячеек?

​=A$1,​​ сложно, можно куда​ летучести (ДВССЫЛ(), например).​Правильно?​ строке.​ нет.», 48, «Ошибка!»​ указать число 4,​
​ – абсолютная;​ в которой находится​CELL​ 0 (ноль).​
​Сумма третьего столбца в​ «номер_области» должны указывать​
​    — обязательный аргумент. Ссылка​ а затем — клавишу​
​ номером строки и​ Если Вам нужно​

​3​​ нибудь залить файл​Владимир​
​В моём предыдущем​
​P.S. Комментарий за​добавьте такую строку:​ ссылка на ячейку​1 – выбор стиля​

​ требуемая ячейка;​​(ЯЧЕЙКА) покажет путь​contents​ первой области диапазона​
​ на ячейку внутри​ на один или​

​ ВВОД. При необходимости​​ номером столбца.​
​ получить значение ячейки,​=$A1.​ с простым примеров​: Возможно совпадёт с​ сообщении ошибочно проставлен​ отдельную плату :-)​Target.Offset(0, 1) =​ примет следующий вид:​ ссылок с буквенно-цифровой​

​Номер_столбца – числовое значение,​​ к файлу, имя​(содержимое) – значение​ (A1:C11) является суммой​

​ аргумента «ссылка»; в​​ несколько диапазонов ячеек.​

​ измените ширину столбцов,​​Если первый аргумент функции​ можно использовать результат,​a1​ подобной конструкции​ чьим-либо решением, но…:​

planetaexcel.ru

​ знак вопрос в​

Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL)

Иногда бывает необходимо с помощью формул узнать о какой-либо ячейке подробную информацию и параметры, чтобы использовать это в расчетах. Например, выяснить число или текст в ячейке или какой числовой формат в ней установлен. Сделать это можно, используя функцию ЯЧЕЙКА (CELL) .

Синтаксис у функции следующий:

=ЯЧЕЙКА( Параметр ; Адрес )

где Адрес — это, понятное дело, ссылка на нужную ячейку, а вот Параметр — это кодовое слово, определяющее какую именно информацию о ячейке мы хотим получить. У этого аргумента несколько возможных значений:

Давайте рассмотрим пару трюков с применением этой функции на практике.

Например, можно получить имя текущего листа формулой, используя функцию ЯЧЕЙКА с параметром «имяфайла» и извлекающей все символы правее закрывающей квадратной скобки:

Также можно проверить тип данных в ячейке (параметр «тип») и выводить сообщение об ошибке вместо вычислений, если введен текст или ячейка пуста:

Или подсветить условным форматированием все незащищенные ячейки в заданном диапазоне:

Функция ЯЧЕЙКА() в MS EXCEL

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

Синтаксис функции ЯЧЕЙКА()

ЯЧЕЙКА(тип_сведений, [ссылка])

тип_сведений— Текстовое значение, задающее требуемый тип сведений о ячейке. В приведенном ниже списке указаны возможные значения аргумента тип_сведений и соответствующие результаты.

ссылка — Необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает сведения только для левой верхней ячейки диапазона.

Использование функции

В файле примера приведены основные примеры использования функции:

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только VBA.

Самые интересные аргументы это — адрес и имяфайла, которые позволяют быстро вывести в ячейке имени файла и путь к нему. Об этом читайте в статье Нахождение имени текущей книги.

Обратите внимание, что если в одном экземпляре MS EXCEL (см. примечание ниже) открыто несколько книг, то функция ЯЧЕЙКА() с аргументами адрес и имяфайла, будет отображать имя того файла, с который Вы изменяли последним. Например, открыто 2 книги в одном окне MS EXCEL: Базаданных.xlsx и Отчет.xlsx. В книге Базаданных.xlsx имеется формула =ЯЧЕЙКА(«имяфайла») для отображения в ячейке имени текущего файла, т.е. Базаданных.xlsx (с полным путем и с указанием листа, на котором расположена эта формула). Если перейти в окно книги Отчет.xlsx и поменять, например, содержимое ячейки, то вернувшись в окно книги Базаданных.xlsx (CTRL+TAB) увидим, что в ячейке с формулой =ЯЧЕЙКА(«имяфайла») содержится имя Отчет.xlsx. Это может быть источником ошибки. Хорошая новость в том, что при открытии книги функция пересчитывает свое значение (также пересчитать книгу можно нажав клавишу F9). При открытии файлов в разных экземплярах MS EXCEL — подобного эффекта не возникает — формула =ЯЧЕЙКА(«имяфайла») будет возвращать имя файла, в ячейку которого эта формула введена.

Примечание: Открыть несколько книг EXCEL можно в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких. Обычно книги открываются в одном экземпляре MS EXCEL (когда Вы просто открываете их подряд из Проводника Windows или через Кнопку Офис в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть запустив файл EXCEL.EXE, например через меню Пуск. Чтобы убедиться, что файлы открыты в одном экземпляре MS EXCEL нажимайте последовательно сочетание клавиш CTRL+TAB — будут отображаться все окна Книг, которые открыты в данном окне MS EXCEL. Для книг, открытых в разных окнах MS EXCEL (экземплярах MS EXCEL) это сочетание клавиш не работает. Удобно открывать в разных экземплярах Книги, вычисления в которых занимают продолжительное время. При изменении формул MS EXCEL пересчитывает только книги открытые в текущем экземпляре.

Другие возможности функции ЯЧЕЙКА() : определение типа значения, номера столбца или строки, мало востребованы, т.к. дублируются стандартными функциями ЕТЕКСТ() , ЕЧИСЛО() , СТОЛБЕЦ() и др.

Excel. Получить значение ячейки по адресу без макроса 2

Итак, решая очередную головоломку с закупкой материалов, я встал перед следующей ситуацией:

Даны две таблицы Excel на разных листах:

В таблице №1 строка состоит из пустых полей: уникальный номер товара, наименование, количество и т.п.

В таблице №2 строки непосредственно с данными: номер товара, название товара, цена, комплектность и т.п.

Я хочу связать эти таблички таким образом, чтобы при заполнении уникального номера в колонке таблицы №1 цена и название подтягивались из таблицы №2

Не хочу использовать для этого ни макросы, ни какое либо программирование, а выполнить эту задачу только средствами самого Excel

для этого мне нужно решить две задачи:

1) Найти в таблице строку, в которой находится информация о товаре

2) Используя знания об этой строке, вставить значение из нужного поля(столбца).

Решаем первую задачу:

= ПОИСКПОЗ(G11;Цены!A:A;0)

G11 — это та ячейка, куда мы вбиваем уникальный номер товара,

Цены!A:A — это колонка с уникальными номерами товаров

— формат вывода в абсолютных значениях, оставляем так, либо изучаем вопрос самостоятельно =)

Данная формула находит номер строки, в которой находится наш товар и информация о нём после того как в ячейку G11 мы вбили его уникальный номер.

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

Например за цену у нас ответственный второй столбец во второй таблице

= АДРЕС( ПОИСКПОЗ(G11;Цены!A:A;0); 2; 1;1; ‘Цены’ )

где тип ссылки абсолютный, то есть 1

и формат адреса ячеек не буквенно-числовые вида A11 B876 и т.д. , а R11C1, R876C2 и т.п

то-есть номера строк и столбцов сразу очевидны за этот формат у нас отвечает последняя 1

Сам адрес мы получаем для вкладки «Цены» , потому не забываем и её указать, иначе поиск значений будет идти на странице с таблицей 1

Мы уже у финиша

Следующая головоломка которую я не решил, зато мне подсказал мой приятель — гений Экселя.

Как зная адрес ячейки без использования макросов вытянуть из неё значения.

Всё просто, это функция

таким образом полная формула получится вида:

=ДВССЫЛ(АДРЕС(ПОИСКПОЗ(G11;Цены!A:A;0); 2 ;1;1;’Цены’)),

где меняя двойку на номер столбца с нужным типом данных мы получим любое необходимое нам значение по фиксированной товарной позиции: Наименование, тип итд

Теперь не нужно ломать голову по поводу проверки вводимых данных, они берутся полностью из имеющейся таблицы, которую до меня уже проверили ГЫГЫГЫ =)

Функция ЯЧЕЙКА в Excel и примеры ее использования

Очень часто при работе в Excel необходимо использовать данные об адресации ячеек в электронной таблице. Для этого была предусмотрена функция ЯЧЕЙКА. Рассмотрим ее использование на конкретных примерах.

Функция значения и свойства ячейки в Excel

Стоит отметить, что в Excel используются несколько функций по адресации ячеек:

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

Как работает функция ЯЧЕЙКА в Excel?

Функция ЯЧЕЙКА в своей работе использует синтаксис, который состоит из двух аргументов:

1. Тип_сведений – текстовое значение, задающее требуемый тип сведений о ячейке. При вводе функции вручную высвечивается выпадающий список где указаны все возможные значения аргумента «тип сведений»:
2. Ссылка – необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает сведения только для левой верхней ячейки диапазона.

Примеры использования функции ЯЧЕЙКА в Excel

Пример 1. Дана таблица учета работы сотрудников организации вида:

Необходимо с помощью функции ЯЧЕЙКА вычислить в какой строке и столбце находится зарплата размером 235000 руб.

Для этого введем формулу следующего вида:

• – «строка» и «столбец» – параметр вывода;
• – С8 – адрес данных с зарплатой.

В результате вычислений получим: строка №8 и столбец №3 (С).

Как узнать ширину таблицы Excel?

Пример 2. Нужно вычислить ширину таблицы в символах. Сразу стоит отметить что в Excel по умолчанию ширина столбцов и ячеек измеряется в количестве символов, которые они умещают в своем значении доступны для отображения в ячейке без переноса строки.

Примечание. Высота строк и ячеек в Excel по умолчанию измеряется в единицах измерения базового шрифта – в пунктах pt. Чем больше шрифт, тем выше строка для полного отображения символов по высоте.

Введем в ячейку С14 формулу для вычисления суммы ширины каждого столбца таблицы:

• – «ширина» – параметр функции;
• – А1 – ширина определенного столбца.

Как получить значение первой ячейки в диапазоне

Пример 3. В условии примера 1 нужно вывести содержимое только из первой (верхней левой) ячейки из диапазона A5:C8.

Введем формулу для вычисления:

Описание формулы аналогичное предыдущим двум примерам.

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

Часто из всем известной 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) -(все форматы). Там в поле Тип будет как раз приведен применяемый код формата. Можно его просто скопировать оттуда и вставить в функцию ТЕКСТ .
Но если форматы в ячейках различаются и записаны в разнобой. Стандартно этого никак не сделать, кроме как каждую ячейку руками перебивать. Но если прибегнуть к помощи 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, 679 скачиваний)

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

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

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум