Excel как получить значение объединенной ячейки
Значение из объединённой ячейки при ссылке на не верхнюю-левую ячейку.
Ячейки 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.
Друзья, решил вот поделиться довольно-таки полезной, с моей точки зрения, формулой. Кстати, если я не ошибаюсь, это первая тема в "Есть готовое решение", в которой нет макросов.
Иногда так случается, что мне дают таблицу с объединенными ячейками и слезно просят посчитать итоговые суммы, ничего при этом в самой таблице не меняя. Так, дескать, красивше и начальству нравится больше. Причем итоги должны быть динамическими, формулы должны "протягиваться" за уголок и макросов там быть ну совсем никак не должно. Сначала я пользовался всяческими извращенными способами, типа скрытых строк/столбцов, выноса данных на скрытый лист и т.п. Потом мне это надоело, да и не всегда при изменении вида или размера таблицы безобразия такие работали правильно. Что родилось в итоге - можно посмотреть в приложенных файлах.
Файл "Primer_1" - примеры формул для строк, столбцов и таблицы.
Файл "Primer_2" - как все это можно на практике применить.
Друзья, решил вот поделиться довольно-таки полезной, с моей точки зрения, формулой. Кстати, если я не ошибаюсь, это первая тема в "Есть готовое решение", в которой нет макросов.
Иногда так случается, что мне дают таблицу с объединенными ячейками и слезно просят посчитать итоговые суммы, ничего при этом в самой таблице не меняя. Так, дескать, красивше и начальству нравится больше. Причем итоги должны быть динамическими, формулы должны "протягиваться" за уголок и макросов там быть ну совсем никак не должно. Сначала я пользовался всяческими извращенными способами, типа скрытых строк/столбцов, выноса данных на скрытый лист и т.п. Потом мне это надоело, да и не всегда при изменении вида или размера таблицы безобразия такие работали правильно. Что родилось в итоге - можно посмотреть в приложенных файлах.
Файл "Primer_1" - примеры формул для строк, столбцов и таблицы.
Файл "Primer_2" - как все это можно на практике применить. _Boroda_
Иногда так случается, что мне дают таблицу с объединенными ячейками и слезно просят посчитать итоговые суммы, ничего при этом в самой таблице не меняя. Так, дескать, красивше и начальству нравится больше. Причем итоги должны быть динамическими, формулы должны "протягиваться" за уголок и макросов там быть ну совсем никак не должно. Сначала я пользовался всяческими извращенными способами, типа скрытых строк/столбцов, выноса данных на скрытый лист и т.п. Потом мне это надоело, да и не всегда при изменении вида или размера таблицы безобразия такие работали правильно. Что родилось в итоге - можно посмотреть в приложенных файлах.
Файл "Primer_1" - примеры формул для строк, столбцов и таблицы.
Файл "Primer_2" - как все это можно на практике применить. Автор - _Boroda_
Дата добавления - 14.01.2012 в 04:33
Задача в следующем:
Вставляем формулу =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, не поможет, так как это только "первые 18 строчек". Далее еще 12300 таких же строчек (по 18 строк характеристик и одному значению в объедененной ячейке для них).
Измененный пример приложил
VEKTORVSFREEMAN, не поможет, так как это только "первые 18 строчек". Далее еще 12300 таких же строчек (по 18 строк характеристик и одному значению в объедененной ячейке для них).
Измененный пример приложил Nicko
Предположу, что таких объединенных у Вас много. Тогда гвозди не помогут - на каждую объединенную ячейку не настучишься.
Предлагаю так
Предположу, что таких объединенных у Вас много. Тогда гвозди не помогут - на каждую объединенную ячейку не настучишься.
Предлагаю так
Именно то, что я и хотел
Именно то, что я и хотел
Спасибо вам! Автор - Nicko
Дата добавления - 12.03.2015 в 14:28
"Опыт - это то, что получаешь, не получив того, что хотел"
Вообще-то возможно поместить во все эти объединённые ячейки значения. Можно даже разные. Можно даже формулы, ссылающиеся на разные ячейки. Но это ручная работа, для большого количества будет трудоёмко. Но например заготовить одну многоразовую форму можно.
Вообще-то возможно поместить во все эти объединённые ячейки значения. Можно даже разные. Можно даже формулы, ссылающиеся на разные ячейки. Но это ручная работа, для большого количества будет трудоёмко. Но например заготовить одну многоразовую форму можно. 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
Требуется помощь, чтобы получить значение объединенной ячейки из «Итоговой цены по прейскуранту». Как показано на изображении выше, это объединенная ячейка, начиная с строки с 13 по 18. Я использую приведенный ниже код, чтобы получить значение, но все равно получаю его как null.
Пожалуйста, помогите мне получить значение объединенной ячейки.
Как правило, для объединенных ячеек вам нужно выбрать самую верхнюю левую ячейку для реального значения.
Это J-столбец. Итак, обращаясь к изображению, вы имеете в виду, что значение должно быть получено из J13. Пробовал это уже, но все равно не удалось!
Вы уверены, что это одна объединенная ячейка? Судя по изображению, фактическое значение кажется слишком далеким от $ знака. Может быть, фактическое значение находится в столбце K ? (хотя это предположение, поскольку мы не можем видеть имена столбцов)
Как упоминалось в комментарии, просто обратитесь к самой верхней левой ячейке, например, если диапазон от A2 до C4 объединен, тогда формула = A2 вернет содержимое объединенной ячейки, то же самое применимо к макросам.
Всего 1 столбец J + 6 строк с 13 по 18. Могу ли я получить код для извлечения значения из J13 . J18? Я попытался получить J13, но все равно не смог получить значение.
Извините, какую ошибку вы получили, не уверен, что вы имеете в виду, вы не смогли получить значение, как упоминалось в @Vityata, вы можете выбрать свою объединенную ячейку, чтобы увидеть эту возвращаемую ячейку, в другой ячейке на том же листе просто введите = J13, если это ничего не возвращает, перезапишите значение в объединенном диапазоне просто в качестве теста, чтобы увидеть, что возвращает формула.
Я хочу получить значение объединенной ячейки с диапазоном от D3 до H3, используя библиотеку openpyxl. Насколько я понимаю, большинство библиотек считывают данные из самой 1-й ячейки. Таким образом, в нем присутствует объединенный контент, но я получаю значение none, когда читаю его.
Ниже приведен мой код:
Значение в ячейке для D3 до H3:
Я даже пытался распечатать все значения на листе, но, кроме целых значений, все значения возвращали None.
Ниже приведен измененный код:
Файл Excel имеет следующее содержимое:
Без доступа к исходным файлам трудно что-либо сказать, но могут возникнуть проблемы, если где-либо используются гиперссылки. openpyxl удаляет все ячейки из объединенного диапазона, кроме верхней левой ячейки.
Как только единственный ответ неверен (в openpyxl больше нет функции cell_from_range), я предлагаю альтернативный способ. Я попробовал, и это сработало для моего случая:
Работает отлично, но начиная с версии 2.5 merged_cell_ranges устарел, см. openpyxl.readthedocs.io/en/stable/… Однако до сих пор не узнал, как работает замена.
Я написал это на основе последнего исходного кода Openpyxl:
Вот приближение функции, которую я использую для этого:
Единственный действительно опасный момент — это то, где я извлекаю список ячеек в пределах диапазона для поиска. Это возвращает генератор, поэтому я приводил его к списку ( in очевидно, потому что он не работает с генераторами), что дает кортеж, содержащий один элемент списка, который я извлекаю, используя 0-индекс.
Для моих целей это достаточно быстро — я использую его, перебирая ячейки, которые хочу протестировать. Если вы хотите сделать это более производительным, возможно, стоит инвертировать цикл, повторяя диапазоны слияния в качестве внешнего цикла, поэтому вам нужно выполнить это преобразование только один раз.
Интересно видеть, что кто-то смог использовать API! :-) Он был разработан для простоты: if idx in sheet.merged_cells как быстрая проверка, была ли ячейка объединена или нет. Это использует генераторы для гибкости.
Я подумал об этом, но свойство merged_cells повторяет все объединенные диапазоны, извлекает координаты отдельных ячеек, а затем объединяет их все вместе, что кажется мне довольно затратным. По сути, я украл, гм, перепрофилировал этот код из библиотеки, за исключением того, что я разбиваю каждый диапазон слияния только один раз.
Да, это немного другое требование: нужно знать, была ли ячейка объединена или нет. Если есть необходимость получить «лидера» объединенного диапазона, то может иметь смысл каким-то образом сохранить его вместе с диапазоном. Все еще жду дополнительной информации о том, что спецификация означает по этому поводу.
Читайте также: