Как вставить формулу в объединенные ячейки в excel
Обновлено: 04.07.2024
Здравствуйте, великие умы и мастера MS Office!
Столкнулся со следующей проблемой при создании прикреплённого файла:
Формулы вытаскивают данные из листа "Исходные данные", перенося их на лист "ЖСР". Лист "ЖСР" в каждой строке имеет одновременно и нормальные, и объединенные ячейки. Формулы в строках 1 и 2 прописаны вручную и, вроде, прекрасно выполняют свою задачу. НО! При попытке протянуть формулы на весь лист "ЖСР" умная программа категорически не воспринимает ручной труд и с упорством искусственного интеллекта заполняет данные, пропуская каждую вторую строку из исходного документа.
В листе "Исходные данные" в колонке 57 указаны "wp" в хронологическом порядке. Для каждого из "wp" есть своя информация, которая в первой строке листа "ЖСР" заполняется по формулам. При попытке протянуть формулу ниже должен получиться следующий результат: строка 1,2 - wp-18 и его характеристики, строка 3,4 - wp-02 и его характеристики, строка 4,5 - wp-04 и его характеристики и т.д. На деле при протягивании отображается wp-18, а потом wp-04, пропуская wp-02.
Какой ритуальный танец поможет устранить эту неприятность, не прибегая к макросам?
Здравствуйте, великие умы и мастера MS Office!
Столкнулся со следующей проблемой при создании прикреплённого файла:
Формулы вытаскивают данные из листа "Исходные данные", перенося их на лист "ЖСР". Лист "ЖСР" в каждой строке имеет одновременно и нормальные, и объединенные ячейки. Формулы в строках 1 и 2 прописаны вручную и, вроде, прекрасно выполняют свою задачу. НО! При попытке протянуть формулы на весь лист "ЖСР" умная программа категорически не воспринимает ручной труд и с упорством искусственного интеллекта заполняет данные, пропуская каждую вторую строку из исходного документа.
В листе "Исходные данные" в колонке 57 указаны "wp" в хронологическом порядке. Для каждого из "wp" есть своя информация, которая в первой строке листа "ЖСР" заполняется по формулам. При попытке протянуть формулу ниже должен получиться следующий результат: строка 1,2 - wp-18 и его характеристики, строка 3,4 - wp-02 и его характеристики, строка 4,5 - wp-04 и его характеристики и т.д. На деле при протягивании отображается wp-18, а потом wp-04, пропуская wp-02.
Какой ритуальный танец поможет устранить эту неприятность, не прибегая к макросам? Alex1018
Столкнулся со следующей проблемой при создании прикреплённого файла:
Формулы вытаскивают данные из листа "Исходные данные", перенося их на лист "ЖСР". Лист "ЖСР" в каждой строке имеет одновременно и нормальные, и объединенные ячейки. Формулы в строках 1 и 2 прописаны вручную и, вроде, прекрасно выполняют свою задачу. НО! При попытке протянуть формулы на весь лист "ЖСР" умная программа категорически не воспринимает ручной труд и с упорством искусственного интеллекта заполняет данные, пропуская каждую вторую строку из исходного документа.
В листе "Исходные данные" в колонке 57 указаны "wp" в хронологическом порядке. Для каждого из "wp" есть своя информация, которая в первой строке листа "ЖСР" заполняется по формулам. При попытке протянуть формулу ниже должен получиться следующий результат: строка 1,2 - wp-18 и его характеристики, строка 3,4 - wp-02 и его характеристики, строка 4,5 - wp-04 и его характеристики и т.д. На деле при протягивании отображается wp-18, а потом wp-04, пропуская wp-02.
Какой ритуальный танец поможет устранить эту неприятность, не прибегая к макросам? Автор - Alex1018
Дата добавления - 24.08.2020 в 17:39
Задача в следующем:
Вставляем формулу =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
Сначала расскажем что такое объединенные ячейки в Excel и как их сделать. Тут нет ничего сложно, достаточно выделить две или более ячеек и выбрать команду на ленте Главная -> Выравнивание -> Объединить и поместить в центре.
Важно отметить, что Excel при объединении ячеек, которые содержат значения, оставит значение только той ячейки, которая находится в верхнем левом углу выбранного диапазона. Остальные же значения будут удалены. Однако, с помощью нашей надстройки, данное недоразумение можно избежать с помощью специальной команды.
Вы можете объединять ячейки как по горизонтали, так и по вертикали. Также можно объединить области, содержащие боле одной строки и столбца.
Способы объединения ячеек
Если внимательней посмотреть на команду объединения ячеек, то можно увидеть выпадающее меню, которое в свою очередь содержит следующие дополнительные действия:
- Объединить по строкам - позволяет выбрать диапазон ячеек, который содержит несколько строк. В этом случае Excel будет создавать объединенные ячейки по одной на каждой строке
- Объединить ячейки - объединяет выделенные ячейки в одну, но при этом не выравнивает текст в ячейке.
- Отменить объединение ячеек - позволяет разбить объединенную ячейку на несколько.
Какие проблемы возникают при использовании объединенных ячеек
Как уже говорили, пользоваться объединенными ячейками стоит аккуратно, так как они ограничивают функционал Excel и могут в дальнейшем принести неприятности. Если все-таки решили пользоваться объединенными ячейками, всегда помните о следующих моментах:
- Если диапазон содержит объединенные ячейки, то пользоваться сортировкой и фильтрацией в этом диапазоне будет невозможно.
- Также невозможно будет преобразовать такой диапазон в таблицу (форматировать как таблицу).
- Также можно забыть об автоматическом выравнивании ширины или высоты ячейки. Например если имеется объединенная ячейка A1:B1, то выравнять ширину столбца A уже не получится.
- Если Вы пользуетесь горячими клавишами для навигации, например переходите в начало и конец таблицы путем сочетания клавиш Ctrl + стрелка вверх и вниз, то переход не удастся, и курсор будет "упираться" в объединенные ячейки.
- Если вы выделяете столбцы (или строки) с помощью горячих клавиш Ctrl (Shift) +Пробел, то при наличии объединенных ячеек, вы не сможете выделить 1 столбец (или строку).
Как найти все объединенные ячейки и разъединить их
Чтобы разъединить все ячейки и удалить объединенные, достаточно выполнить следующие действия:
- Выделить все ячейки на листе. Это можно сделать путем нажатия сочетания клавиш Ctrl + A или кликнуть на черный треугольник между заголовками строк и столбцов на листе.
- Нажать на команду Главная -> Выравнивание -> Объединить и поместить в центре если она выделена. Если же она не выделена, значит выбранный лист не содержит объединенных ячеек.
Если же необходимо найти все объединенные ячейки в книге, то это можно сделать через панель поиска. Для этого выполните следующие шаги:
Альтернативы использования объединенных ячеек
Как правило объединенные ячейки применяют для целей выравнивания текста. Однако мало кто знает, что текст можно выровнять и без объединения ячеек и потери функционала. Для этого можно использовать обычное форматирование ячеек:
- Выделите диапазон ячеек, который хотите отцентрировать. При этом сам текст должен содержаться в левой верхней ячейке.
- Выберите команду формат ячеек или нажмите сочетание клавиш Ctrl + 1.
- В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
- В выпадающем списке выравнивания по горизонтали выберите значение по центру выделения и нажмите OK.
Еще один способ избежать объединения ячеек - это использовать надписи. В них также можно вставлять текст и выбирать направление текста по вертикали. Способ конечно также накладывает много ограничений, но в каких-то случаях имеет место быть.
Обычно ячейки объединяют, чтобы оптимизировать внешний вид рабочего листа. Например, на рисунке 1 изображен лист с четырьмя множествами объединенных ячеек: B2:H2, I2:O2, A4:A8, A9:A13. Текст в объединенных ячейках в столбце А записывается по вертикали.
Рис. 1. На листе четыре множества объединенных ячеек
Не забывайте, что в объединенных ячейках может содержаться только одна единица информации, например значение, текст или формула. Если вы пытаетесь объединить диапазон ячеек, в котором содержится более одной непустой ячейки, Excel выдает предупреждение, что сохранится лишь информация из одной ячейки, которая расположена ближе всего к левому верхнему углу диапазона.
Чтобы вновь разбить ячейки, выделите объединенную область и снова нажмите Объединить и поместить в центре.
Другие операции, связанные с объединением ячеек
Обратите внимание: кнопка Объединить и поместить в центре находится в раскрывающемся меню. Щелкнув на стрелке, вы увидите три дополнительных команды.
Если в Excel требуется отображать длинный текст, удобно разбивать его на строки в объединенных ячейках. Чтобы добиться этого, выделите объединенные ячейки и выполните команду Главная → Выравнивание → Перенести текст. Пользуйтесь элементами управления для выравнивания по горизонтали и вертикали в группе Главная → Выравнивание, чтобы откорректировать положение текста.
На рис. 2 изображен лист, на котором объединена 171 ячейка (19 строк в 9 столбцах). К тексту в объединенной ячейке применен параметр Перенести текст.
Рис. 2. Здесь 171 ячейка объединены в одну
Потенциальные проблемы, которые могут возникнуть с объединенными ячейками
Многие пользователи Excel испытывают пещерную ненависть к объединению ячеек. Они стараются не пользоваться этой возможностью и убеждают всех остальных также от нее отказаться. Но если вы понимаете ограничения такого подхода и потенциальные проблемы, связанные с ним, то совсем не стоит полностью отказываться от объединения ячеек.
Вот несколько вещей, о которых стоит помнить:
- Нельзя использовать объединенные ячейки в таблице, создаваемой методом Вставка → Таблицы → Таблица. Это понятно, поскольку табличная информация должна быть согласована по строкам и столбцам. При объединении ячеек такая согласованность будет нарушена.
- Как правило, можно дважды щелкнуть по заголовку столбца или строки, чтобы подогнать данные в строке или столбце, но такая возможность отсутствует, если в строке или столбце есть объединенные ячейки. Вместо этого потребуется откорректировать ширину столбца или высоту строки вручную.
- Объединенные ячейки также могут влиять на сортировку и фильтрацию. Это еще одна причина, по которой объединение ячеек в таблицах не допускается. Если у вас есть диапазон данных, который нужно отсортировать или отфильтровать, то не пользуйтесь объединением ячеек.
- Наконец, объединенные ячейки могут вызывать проблемы, связанные с макросами VBA. Например, при объединении ячеек в диапазоне A1:D1, команда VBA наподобие следующей выделит четыре столбца (вряд ли программист хотел этого): Columns("B:B").Select .
Как найти все объединенные ячейки на листе
Чтобы узнать, есть ли на листе объединенные ячейки, выполните следующие действия.
Если на этапе 3 команда Объединить и поместить в центре не выделена, это означает, что на листе нет объединенных ячеек. Если выполнить эту команду, когда выделены все ячейки, то все 17 179 869 184 ячейки рабочего листа будут объединены в одну.
Рис. 3. Поиск всех объединенных ячеек на рабочем листе
Какие существуют альтернативы для объединения ячеек
В некоторых случаях можно воспользоваться командой Excel Объединить по строкам в качестве альтернативы объединению ячеек. Эта команда удобна для центирирования текста стразу в нескольких столбцах. Выполните следующие действия.
- Введите текст, который следует центрировать в ячейке.
- Выделите ячейку с текстом и дополнительные ячейки рядом с ней.
- Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
- В окне Формат ячеек щелкните вкладку Выравнивание.
- В разделе Выравнивание выберите раскрывающийся список По горизонтали и укажите в нем вариант По центру выделения.
- Нажмите OK, чтобы закрыть диалоговое окно Формат ячеек.
Текст будет выровнен по центру в выделенном диапазоне.
Еще одна альтернатива объединению ячеек заключается в использованию надписей. Это особенно удобно при работе с текстом, который должен отображаться вертикально. На рисунке 4 приведен пример надписи, в которой выведен вертикальный текст.
Рис. 4. Применение надписи в качестве альтернативы изменению ячеек
Чтобы добавить надпись, выполните команду Вставка → Текст → Надпись, отрисуйте надпись на листе, а затем введите текст. Пользуйтесь инструментами форматирования текста вкладки Главная, чтобы корректировать текст, а также инструментами контекстной вкладки Средства рисования → Формат, чтобы вносить изменения, например можно скрыть контур надписи.
Читайте также: