Excel как получить значение объединенной ячейки
Значение из объединённой ячейки при ссылке на не верхнюю-левую ячейку.
Весь MS Office, программирование на Visual Basic for Applications и MS VBЯчейки A1:B3 объединены. В этой объединённой ячейке находится значение 1.
Если в C4 ввести формулу "=A1" то получим результат 1. Если формулу в C4 скопировать на область [noparse]C4 6[/noparse], то во всех ячейках, кроме самой C4, результат будет 0. Это понятно, т.к. формулы скопировались со смещением по ссылке. А хотелось бы получать значение из верхней-левой, т.е. 1.
Как обычными формулами без VBA получить значение объединённой ячейки, если ссылка не на левую-верхнюю ячейку из этой объединённой?
Абсолютная ссылка ($A$1) тут не подойдёт, ибо формулу надо скопировать на несколько объединённых областей, где значения верхней-левой ячеек разные.
Через VBA это решается просто.
И в область [noparse]C4 6[/noparse] ввести формулу "=V(A1)"
Но надо без макросов.
В функции ПОЛУЧИТЬ.ЯЧЕЙКУ типа информации о вхождении или нет ячейки в объединённую область тоже не нашёл.
не совсем понятно, а почему не подойдетесли следовать Вашим условиям, то формула =$A$1 прекрасно "размножается" на диапазон C4 : D6 "растягиванием" и во всех ячейках этого диапазона имеем значение 1. (специально проверил, а вдруг ).
и что такое несколько объединённых областей, где значения верхней-левой ячеек разные "Дайте людям рыбы, и вы накормите их на весь день;
научите их ловить рыбу - и вы накормите их на всю жизнь".
Нет, никакая абсолютная ссылка не пойдёт. Ибо нужно одинаковую формулу скопировать на большой диапазон, в каждой ячейке диапазона ссылки на РАЗНЫЕ объединёные ячейки в которых, соответственно, РАЗНЫЕ данные. И размер (количество входящих одинарных ячеек) каждой объединённой ячейки заранее неизвестно.
Для пояснения см. файл.
В столбце A - ряд объединённых ячеек с данными
В столбце B - вид формулы
В столбце C - результат формулы
В столбце D - результат функции VBA - который и есть правильный.
Т.е. надо получить результат, как в D, но без VBA.
tolikt, ну что же. С примером оно конечно понятнее будет.Тогда действительно вопрос, как получить значение ячейки которой нет?
Я, конечно, не знаю полностью условий Вашей задачи, но может присвоить объединенным ячейкам имена? "Дайте людям рыбы, и вы накормите их на весь день;
научите их ловить рыбу - и вы накормите их на всю жизнь".
Ну, ячейка-то как бы есть. Значение в ней - пусто.
Идея получения нужного значения лежит на поверхности:
Если значение в ячейке пусто, то брать значение из ячейки над пустой. Если и она пустая, то выше и так далее вверх. В VBA данный цикл можно организовать легко. Впрочем, там есть ещё более простая функция MergeArea. А как всё это организовать без VBA, а только с помощью своих формул.
Пробовал поиграться с ПОИСКПОЗ, ЧСТРОК и другими, но не получается.
В функции ПОЛУЧИТЬ.ЯЧЕЙКУ нет такого типа информации о ячейке, как объединена она или нет. Хотя она тоже не совсем функция листа.
Составная функция (для ячейки C5) типа =ЕСЛИ(A5<>"";A5;C4) тоже не прокатит, т. к. на самом деле формула сложная и в ячейке C4 уже будет какое-то вычисленное значение, а не нужное исходное, находящееся в объединённой ячейке, в которую входит ячейка A5.
Задача в следующем:
Вставляем формулу =A2 (где A2,A3. и до A19 это объединенные ячейки и в них только одно значение)
Формула видит значение только в первой ячейке A2. Во всех последующих она выдает значение "0"
Как решить такую задачу?
Файл с примером прикреплен
Задача в следующем:
Вставляем формулу =A2 (где A2,A3. и до A19 это объединенные ячейки и в них только одно значение)
Формула видит значение только в первой ячейке A2. Во всех последующих она выдает значение "0"
Как решить такую задачу?
Файл с примером прикреплен Nicko
Вставляем формулу =A2 (где A2,A3. и до A19 это объединенные ячейки и в них только одно значение)
Формула видит значение только в первой ячейке A2. Во всех последующих она выдает значение "0"
Как решить такую задачу?
Файл с примером прикреплен Автор - Nicko
Дата добавления - 12.03.2015 в 14:10
прибейте гвоздиком $ Автор - VEKTORVSFREEMAN
Дата добавления - 12.03.2015 в 14:14 VEKTORVSFREEMAN, не поможет, так как это только "первые 18 строчек". Далее еще 12300 таких же строчек (по 18 строк характеристик и одному значению в объедененной ячейке для них).
Измененный пример приложил VEKTORVSFREEMAN, не поможет, так как это только "первые 18 строчек". Далее еще 12300 таких же строчек (по 18 строк характеристик и одному значению в объедененной ячейке для них).
Измененный пример приложил Nicko Предположу, что таких объединенных у Вас много. Тогда гвозди не помогут - на каждую объединенную ячейку не настучишься.
Предлагаю так
Предположу, что таких объединенных у Вас много. Тогда гвозди не помогут - на каждую объединенную ячейку не настучишься.
Предлагаю так
Автор - _Boroda_
Дата добавления - 12.03.2015 в 14:22 Автор - Pelena
Дата добавления - 12.03.2015 в 14:22
Именно то, что я и хотел
Именно то, что я и хотел
Спасибо вам! Автор - Nicko
Дата добавления - 12.03.2015 в 14:28
"Опыт - это то, что получаешь, не получив того, что хотел"
[p.s.]наверно Автор - krosav4ig
Дата добавления - 13.03.2015 в 02:59 Вообще-то возможно поместить во все эти объединённые ячейки значения. Можно даже разные. Можно даже формулы, ссылающиеся на разные ячейки. Но это ручная работа, для большого количества будет трудоёмко. Но например заготовить одну многоразовую форму можно. Вообще-то возможно поместить во все эти объединённые ячейки значения. Можно даже разные. Можно даже формулы, ссылающиеся на разные ячейки. Но это ручная работа, для большого количества будет трудоёмко. Но например заготовить одну многоразовую форму можно. Hugo Я пытался вставить предложенные тут формулы, но они не помогли или я что-то делаю неправильно. Проблема похожая и необходимо объединенную ячейку разделить, а при разделении каждая страна поднимается наверх. Есть ли возможность как-то автоматизировать этот процесс. Новичок в Excel Я пытался вставить предложенные тут формулы, но они не помогли или я что-то делаю неправильно. Проблема похожая и необходимо объединенную ячейку разделить, а при разделении каждая страна поднимается наверх. Есть ли возможность как-то автоматизировать этот процесс. Новичок в Excel Hjq
Большое спасибо))) Автор - Hjq
Дата добавления - 03.11.2017 в 17:30
Так как новичок в Excel, не перестаю удивляться возможностям этой программы. Хочу попросить у вас помощи форумчани.
У меня есть файл, таких на работе встречается много и хотелось бы как-то автоматизировать процесс, ну или хотя бы облегчить его.
Во вложении вставлю пример.
У меня есть ряд следующих вопросов:
1) Устанавливая фильтр по странам можно ли автоматически создать листы с наименованием каждой страны представленной в фильтре в алфавитном порядке. В файле есть пример, но делал я это вручную.
2) Можно ли создать такой макрос/формулу что бы выбирая страну из фильтра можно было бы автоматически перенести информацию с колонок CDEFG в разбитые по странам листы, с последующим удалением дубликатов. (я пробовал создавать макросы, но они выполняют только то количество что я выделял, т.е. не важно какая страна, он тупо выделяет только то количество колонок, которое я выделял записывая макрос)
PS
Прошу прощения, если задал глупые вопросы или прошу невозможного, ибо, как сказал выше, новичок. Некоторые функции это программы удивляют и я думаю, что может есть формулы или способы как облегчить мой труд и повысить производительность. (Пришлось уменьшить количество стран и данных ибо не более 100 кб, а у меня по 1 и по 7 мб в основном)
Заранее Благодарю за помощь.
Так как новичок в Excel, не перестаю удивляться возможностям этой программы. Хочу попросить у вас помощи форумчани.
У меня есть файл, таких на работе встречается много и хотелось бы как-то автоматизировать процесс, ну или хотя бы облегчить его.
Во вложении вставлю пример.
У меня есть ряд следующих вопросов:
1) Устанавливая фильтр по странам можно ли автоматически создать листы с наименованием каждой страны представленной в фильтре в алфавитном порядке. В файле есть пример, но делал я это вручную.
2) Можно ли создать такой макрос/формулу что бы выбирая страну из фильтра можно было бы автоматически перенести информацию с колонок CDEFG в разбитые по странам листы, с последующим удалением дубликатов. (я пробовал создавать макросы, но они выполняют только то количество что я выделял, т.е. не важно какая страна, он тупо выделяет только то количество колонок, которое я выделял записывая макрос)
PS
Прошу прощения, если задал глупые вопросы или прошу невозможного, ибо, как сказал выше, новичок. Некоторые функции это программы удивляют и я думаю, что может есть формулы или способы как облегчить мой труд и повысить производительность. (Пришлось уменьшить количество стран и данных ибо не более 100 кб, а у меня по 1 и по 7 мб в основном)
Заранее Благодарю за помощь. Hjq
PS
Прошу прощения, если задал глупые вопросы или прошу невозможного, ибо, как сказал выше, новичок. Некоторые функции это программы удивляют и я думаю, что может есть формулы или способы как облегчить мой труд и повысить производительность. (Пришлось уменьшить количество стран и данных ибо не более 100 кб, а у меня по 1 и по 7 мб в основном)
Заранее Благодарю за помощь. Автор - Hjq
Дата добавления - 05.11.2017 в 17:01
По окончании этого урока вы сможете:
- Объединять несколько ячеек в одну
- Находить все объединённые ячейки
- Задавать перенос текста по словам
1. Объединение ячеек
На Уроке 54. Гриф «Утверждение» я рассказывала о нестандартной таблице, то есть таблица, в которой число ячеек в одной строчке не равно числу ячеек в другой строчке (или число ячеек в одном столбце не равно числу ячеек в другом столбце. Достаточно распространен случай общего заголовка для всей таблицы.
Шаг 1. Вставляем сверху новую строчку (Excel 21):
Шаг 2. Вводим название всей таблицы «Сводная ведомость»:
Попробуйте выровнять название по центру таблицы – у вас ничего не получиться!
Шаг 3. Объединяем несколько ячеек в одну (выделяем диапазон A1:D1 → лента Главная → группа команд Выравнивание → команда Объединить и поместить в центре):
Команда «Объединить и поместить в центре» имеет несколько вариантов объединения ячеек:
Чтобы воспользоваться каким-нибудь из них, нужно открыть список, нажав на кнопку выпадающего меню справа от команды.
1. «Объединить и поместить в центре» несколько выделенных ячеек объединяются в одну, а текст помещается по центру образованной ячейки (только что сделали).
2. «Объединить по строкам» ‒ выделенный диапазон ячеек, состоящий из нескольких строк и столбцов объединяются по центру. Например, нужно объединить диапазоны ячеек А1:1 и А2:D2. Для этого выделяет ячейки А1:D2 → выбираем команду Объединить по строкам:
3. «Объединить ячейки» ‒ несколько выделенных ячеек объединяются в одну, при этом выравнивание текста остается прежним. Данная команда такая же, как и «Объединить и поместить в центре», только не располагает текст по центру.
4. «Отменить объединение ячеек» ‒ отменяет любое выделение ячеек. Хотя для того, чтобы отменить объединение ячеек, достаточно еще раз щелкнуть по команде «Объединить и поместить в центре».
Но, если мы решим объединить две или несколько ячеек с содержимым, то ждет неприятный сюрприз.
Шаг 4. Выделяем две ячейки с содержимым → лента Главная → группа команд Выравнивание → команда Объединить и поместить в центре → команда Объединить ячейки):
Сразу появляется предупреждение «В объединенной ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны.»:
Поэтому будьте осторожны при выполнении операции «Объединение ячеек», которые содержат данные.
2. Поиск объединенных ячеек
Редко, но возникает необходимость поиска объединенных ячеек. Я создала ещё одну объединенную ячейку с набором символов.
Шаг 1. Лента Главная → группа команд Редактирование → команда Найти и заменить → команда Найти из выпадающего меню:
Появилось диалоговое окно «Найти и заменить».
Шаг 2. Команда Формат из диалогового окна «Найти и заменить» → режим «Объединение ячеек» из диалогового окна Найти формат → ОК:
Шаг 3. Обращаем внимание на поле рядом с командой «Формат» ‒ там появилось слово «Образец*» → команда Найти все:
Диалоговое окно «Найти и заменить» раскрывается, и мы видим в нижней части диалогового окна список всех объединенных ячеек и подсказку, что найдено 3 объединенных ячейки, что соответствует действительности. Щелкая по каждому пункту списка, мы выделяем соответствующую объединенную ячейку.
Если вам нужно перейти к другому типу поиска, то не забудьте отменить режим «Найти по формату.
Шаг 4. Лента Главная → группа команд Редактирование → команда Найти и заменить → команда формат → команда Очистить формат поиска из выпадающего меню:
3. Перенос текста по словам
В случаях, когда содержимое ячейки делает её слишком длинной, то следует разместить текст в несколько рядов в одной ячейке.
Шаг 1. Выделяем необходимый диапазон → лента Главная → группа команд Выравнивание → команда кнопка Перенос текста:
А вот и результат:
Шаг 2. Отменить режим «Перенос текста» можно повторным щелчком по этой команде.
| Совет от меня | Приступая к работе с новой таблицей, установите сразу же режим «Перенос текста» |
Шаг 3. Выделяем всю таблицу (щелчок ЛМ на перекрестье имен столбцов и строчек) → лента Главная → группа команд Выравнивание → команда Переносить текст:
По окончании этого урока вы сможете:
- Объединять несколько ячеек в одну
- Находить все объединённые ячейки
- Задавать перенос текста по словам
1. Объединение ячеек
На Уроке 54. Гриф «Утверждение» я рассказывала о нестандартной таблице, то есть таблица, в которой число ячеек в одной строчке не равно числу ячеек в другой строчке (или число ячеек в одном столбце не равно числу ячеек в другом столбце. Достаточно распространен случай общего заголовка для всей таблицы.
Шаг 1. Вставляем сверху новую строчку (Excel 21):
Шаг 2. Вводим название всей таблицы «Сводная ведомость»:
Попробуйте выровнять название по центру таблицы – у вас ничего не получиться!
Шаг 3. Объединяем несколько ячеек в одну (выделяем диапазон A1:D1 → лента Главная → группа команд Выравнивание → команда Объединить и поместить в центре):
Команда «Объединить и поместить в центре» имеет несколько вариантов объединения ячеек:
Чтобы воспользоваться каким-нибудь из них, нужно открыть список, нажав на кнопку выпадающего меню справа от команды.
1. «Объединить и поместить в центре» несколько выделенных ячеек объединяются в одну, а текст помещается по центру образованной ячейки (только что сделали).
2. «Объединить по строкам» ‒ выделенный диапазон ячеек, состоящий из нескольких строк и столбцов объединяются по центру. Например, нужно объединить диапазоны ячеек А1:1 и А2:D2. Для этого выделяет ячейки А1:D2 → выбираем команду Объединить по строкам:
3. «Объединить ячейки» ‒ несколько выделенных ячеек объединяются в одну, при этом выравнивание текста остается прежним. Данная команда такая же, как и «Объединить и поместить в центре», только не располагает текст по центру.
4. «Отменить объединение ячеек» ‒ отменяет любое выделение ячеек. Хотя для того, чтобы отменить объединение ячеек, достаточно еще раз щелкнуть по команде «Объединить и поместить в центре».
Но, если мы решим объединить две или несколько ячеек с содержимым, то ждет неприятный сюрприз.
Шаг 4. Выделяем две ячейки с содержимым → лента Главная → группа команд Выравнивание → команда Объединить и поместить в центре → команда Объединить ячейки):
Сразу появляется предупреждение «В объединенной ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны.»:
Поэтому будьте осторожны при выполнении операции «Объединение ячеек», которые содержат данные.
2. Поиск объединенных ячеек
Редко, но возникает необходимость поиска объединенных ячеек. Я создала ещё одну объединенную ячейку с набором символов.
Шаг 1. Лента Главная → группа команд Редактирование → команда Найти и заменить → команда Найти из выпадающего меню:
Появилось диалоговое окно «Найти и заменить».
Шаг 2. Команда Формат из диалогового окна «Найти и заменить» → режим «Объединение ячеек» из диалогового окна Найти формат → ОК:
Шаг 3. Обращаем внимание на поле рядом с командой «Формат» ‒ там появилось слово «Образец*» → команда Найти все:
Диалоговое окно «Найти и заменить» раскрывается, и мы видим в нижней части диалогового окна список всех объединенных ячеек и подсказку, что найдено 3 объединенных ячейки, что соответствует действительности. Щелкая по каждому пункту списка, мы выделяем соответствующую объединенную ячейку.
Если вам нужно перейти к другому типу поиска, то не забудьте отменить режим «Найти по формату.
Шаг 4. Лента Главная → группа команд Редактирование → команда Найти и заменить → команда формат → команда Очистить формат поиска из выпадающего меню:
3. Перенос текста по словам
В случаях, когда содержимое ячейки делает её слишком длинной, то следует разместить текст в несколько рядов в одной ячейке.
Шаг 1. Выделяем необходимый диапазон → лента Главная → группа команд Выравнивание → команда кнопка Перенос текста:
А вот и результат:
Шаг 2. Отменить режим «Перенос текста» можно повторным щелчком по этой команде.
| Совет от меня | Приступая к работе с новой таблицей, установите сразу же режим «Перенос текста» |
Шаг 3. Выделяем всю таблицу (щелчок ЛМ на перекрестье имен столбцов и строчек) → лента Главная → группа команд Выравнивание → команда Переносить текст:
Читайте также: