000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как объединить значения ячеек до тех пор, пока не найдет пустую ячейку в столбце?

Как объединить ячейки в Excel

В этой статье я покажу вам, как объединить ячейки в Excel. Обычно ячейки объединяются по двум причинам:

  • Чтобы форматировать таблицы :

Как объединить ячейки в Excel

На примере выше мы объединили ячейки для создания заголовка «июн.17».

  • Слияние текста из нескольких ячеек в одну :

Как объединить ячейки в Экселе без потери текста

В приведенном выше примере мы объединили имя, фамилию и отчество людей в трех колонках в одну колонку.

Ниже приводятся пять способов комбинирования.

Ответ 1

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

Массив должен быть введен с помощью комбинации клавиш Ctrl-Shift-Enter после того, как вы его напечатаете или вставите. Ниже приведен столбец A:

Ответ 2

Гораздо быстрее использовать следующую простую формулу

Он предлагает следующие преимущества

  • Это не матричная формула
  • Это не формула, которая может быть изменена

Объяснение:

  • (A:A<>»») возвращает массив
  • 1/(A:A<>»») изменяет этот массив на <1,1. #DIV/0. >.
  • Так как LOOKUP ожидает отсортированного массива в порядке возрастания и принимая во внимание, что если функция LOOKUP не может найти точное соответствие, он выбирает наибольшее значение в lookup_range (в нашем случае <1,1. #DIV/0. >), которое меньше или равно значению (в нашем случае 2 ), формула находит последний 1 в массиве и возвращает соответствующее значение из result_range (третий параметр — A:A ).

И небольшое замечание — ячейки с ошибками не учитываются в формуле (вы можете увидеть это только в том случае, если последняя непустая ячейка содержит ошибку). Если вы хотите принять их во внимание, используйте:

На приведенном ниже изображении показана разница:

enter image description here

Ответ 3

Вот еще одна возможность: =OFFSET($A$1;COUNTA(A:A)-1;0)

Ответ 4

После красноречивого ответа Дага Глэнси я придумал способ сделать то же самое без использования формулы массива. Использовать формулы массивов — не мой стиль, но я стараюсь избегать их, когда это возможно (не по какой-то конкретной причине, это просто мой стиль).

Чтобы найти последнюю непустую строку, используя столбец A в качестве опорного столбца

Чтобы найти последний непустой столбец, используя строку 1 в качестве базовой строки

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

Я пробовал описанные выше методы с Excel 2010, как «изначально», так и в «режиме совместимости» (для старых версий Excel), и они работают. Опять же, с ними вам не нужно делать Ctrl + Shift + Enter. Используя способ работы sumproduct в Excel, мы можем обойти необходимость переноса матричных операций, но сделаем это без матричной формулы. Надеюсь, кто-то сможет оценить красоту, простоту и элегантность этих предлагаемых решений для прудов так же, как и я. Однако я не утверждаю эффективность этих решений. Только то, что они просты, что они красивы, что они служат своей цели и что они достаточно гибкие, чтобы расширить их использование для других целей 🙂

Надеюсь, что вы найдете это полезным.

Все хорошо!

Ответ 5

Я знаю, что вопрос старый, но я не удовлетворен ответами, которые были даны.

LOOKUP, VLOOKUP и HLOOKUP имеют проблемы с производительностью, и их никогда не следует использовать.

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

C OUNT и COUNTA сталкиваются с проблемами, когда данные не являются смежными и непустыми, т.е. у вас есть пробелы, а затем данные возвращаются в соответствующий диапазон

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

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

Любая ссылка на последнюю возможную строку или столбец (например, 65536-я строка в Excel 2003) ненадежна и приводит к дополнительным накладным расходам

Вот что я использую

Когда тип данных смешанный: =max(MATCH(1E+306,[RANGE],1),MATCH(«*»,[RANGE],-1))

Когда я знал, что данные содержат только числа: =MATCH(1E+306, [RANGE],1).

Если известно, что данные содержат только текст: =MATCH(«*»,[RANGE],-1)

Поскольку M ATCH имеет самую низкую стоимость и является энергонезависимым, это лучший выбор для хранения данных при большом объеме информации.

Ответ 6

Это работает в Excel 2003 (и более поздних версиях с некоторыми изменениями, см. ниже). Нажмите Ctrl + Shift + Enter (не просто Enter), чтобы ввести эту формулу как матричную формулу.

Обратите внимание, что в Excel 2003 формула массива не применяется ко всему столбцу. Это дает #NUM! могут возникнуть непредсказуемые результаты! (EDIT: Противоречивая информация от Microsoft: то же самое может относиться или не относиться к Excel 2007, проблема может быть решена в 2010 году).

Я делаю это потому, что применяю формулу массива к диапазону A1:A65535 и уделяю особое внимание последней ячейке, которая в Excel 2003 равна A65536. Последняя ячейка автоматически возвращается к A:A, поэтому нельзя просто сказать A:A или A1:A65536.

Вы можете пропустить часть IF, если вы абсолютно уверены, что A65536 пуст:

Если вы используете Excel 2007 или 2010, номер последней строки будет 1048576, а не 65536, поэтому при необходимости переместите его выше.

Если в середине ваших данных нет пустых ячеек, я бы использовал более простую формулу =INDEX(A:A,COUNTA(A:A)).

Ответ 7

Альтернативное решение без матричной формулы, возможно, более надежное, чем предыдущий ответ с решением (подстрочный индекс a) без матричной формулы, таково

Смотрите этот ответ в качестве примера. Благодарность Брэду и Барри Гудини, которые помогли решить эту проблему.

Возможные причины предпочтения формулы без массива перечислены в:

Официальная страница Microsoft (посмотрите на «Недостатки использования формул массива» ).
Формулы массива могут казаться волшебными, но они также имеют некоторые недостатки:

  • Иногда вы можете забыть нажать CTRL + SHIFT + ENTER. Не забывайте нажимать эту комбинацию клавиш каждый раз при вводе или редактировании матричной формулы.
  • Другие пользователи могут не понять ваши формулы. Формулы массивов относительно плохо документированы, поэтому если другим пользователям необходимо изменять ваши книги, либо избегайте формул массивов, либо убедитесь, что эти пользователи понимают, как их изменять.
  • В зависимости от скорости обработки данных и памяти вашего компьютера формулы больших массивов могут замедлять вычисления.

Ответ 8

При поиске в колонке (А) используйте

Если ваш диапазон A1 : A10, вы можете использовать

Возвращает номер последнего непустого ряда, а indirect() возвращает значение ячейки.

Ответ 9

=INDEX(A:A, COUNTA(A:A), 1), извлекается здесь.

Ответ 10

=MATCH(«*»;A1:A10;-1) для текстовых данных

=MATCH(0;A1:A10;-1) для числовых данных

Ответ 11

Я пробовал все энергонезависимые версии, но ни одна из них не работает. excel 2003/2007update. Если вы используете Excel 2003, вы можете сделать это. Не как массив или стандартная формула. Я получаю либо только пустое значение, либо 0, либо значение ошибки #. Поэтому я прибегаю к нестабильным методам. Это сработало.

@ Джулиан Кроне. Это НЕ работает, если вы используете «;» вместо «,». Вы используете Libre Office, а не MS Excel? LOOKUP настолько раздражает, что я использую его только в крайнем случае.

Ответ 12

Вы должны поместить этот код в модуль VBA. Сохранить. Пользователь определяет эту функцию на основе функций.

Ответ 13

Я использовала опцию HLOOKUP

A1 имеет дату; A2:A8 имеет прогнозы, сделанные в разное время.

Стандартная формула hlookup с поисковым массивом, заданным количеством записей.

Ответ 14

Для текстовых данных :

Для получения числовых данных:

Вы получите относительный индекс последней непустой ячейки в выбранном диапазоне (здесь А1: А10).

Если вы хотите получить это значение, обратитесь к нему через INDIRECT после создания -текстуально — абсолютной ссылки на ячейку, например, :

Ответ 15

У меня была такая же проблема. Формула также работает не хуже: -.

Номер строки 14 — это номер первой строки ряда, который нужно подсчитать.

Ответ 16

Если вы знаете, что между ними не должно быть пустых клеток, это самый быстрый способ.

Просто подсчитайте непустые клетки и обратитесь к соответствующей ячейке.

Его также можно наносить на определенный участок.

Возвращает последнюю непустую клетку в диапазоне O4: O34.

Ответ 17

Для Microsoft Office 2013

Это — последний символ непустой строки:

«Последний» — непустая строка:

Ответ 18

Следующая формула сработала для меня в Office 2010:

= ПРОСМОТР (2; 1/(А1: А100 < > «»); А1: А100)

A1: первая ячейка A100: относится к последней ячейке в сравнении

Ответ 19

Я думаю, что ответ W5ALIVE ближе всего к тому, что я использую для поиска последней строки данных в столбце. Если предположить, что я ищу последнюю строку данных в столбце A, то для более общего поиска я бы использовал следующую формулу:

В первом MATCH находится последняя текстовая ячейка, а во втором MATCH — последняя числовая ячейка. Если в первом MATCH найдены все числовые ячейки или во втором MATCH найдены все текстовые ячейки, IFERROR возвращает ноль.

По сути, это небольшой вариант смешанного текстового и числового решения W5ALIVE.

Во временных испытаниях этот метод оказался значительно быстрее, чем эквивалентные вариации LOOKUP.

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

Метод Sancho.s’, вероятно, самый чистый, но я бы изменил часть, которая находит номер строки:

Разница лишь в том, что «, 1» возвращает первое значение, а «, 0» — полный массив значений (все, кроме одного, которое не нужно). Я по-прежнему предпочитаю ссылаться на ячейку в функции индексирования, другими словами, возвращать значение ячейки с помощью :

Ответ 20

Массивы — очень простой способ решить эту проблему, если вы их не боитесь:

Необходимо нажать CTRL + SHIFT + ENTER, так как это формула массива.

Ответ 21

Простота, которая работает для меня:

Ответ 22

Хорошо, у меня была та же проблема, что и у меня, и я попробовал оба лучших ответа. Но я получаю только ошибки формулы. Оказалось, что мне нужно заменить «,» на «;», чтобы формулы работали. Я использую XL 2007.

Ответ 23

Я обнаружил, что отслеживание версий (объясняется добавлением буквы v в начало номера) хорошо работает в Xcelsius (SAP Dashboards).

Объединение ячеек в Эксель

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

Форматирование объединенных областей в Excel:

  1. Отметьте область. Смежные столбцы и строки должны быть выделены. Лучше всего предварительно скопировать информацию, написанную в выделенных ячейках, и переместить ее в соседнее место в документе, иначе содержимое исчезнет. Информация будет записана только в левом верхнем блоке выделенной группы.

как пользоваться ексель

Изображение 2. Комбинация клеток.

  1. Найдите нужную кнопку. В навигационном меню под вкладкой «Главная» найдите кнопку, отмеченную на скриншоте №2, и отобразите выпадающий список. Выберите «Объединить и центрировать». Если соответствующая кнопка недоступна, необходимо закрыть режим редактирования с помощью Enter.
  2. Примечания. Чтобы изменить положение текста в объединенном фрейме, используйте свойства выравнивания на вкладке Главная.

Объединяем две текстовые ячейки в excel в одну через перенос строки

Sub MergeAllData()
Dim delim As String, newdata As String
Dim rng As Range
If TypeName(Selection) <> «Range» Or Selection.Count <= 1 Then Exit Sub
delim = vbLf
newdata = «»
For Each rng In Selection
newdata = newdata & rng.Value & delim
Next rng
Application.DisplayAlerts = False
Selection.Merge
Selection = Left(newdata, Len(newdata) — Len(delim))
Application.DisplayAlerts = True
End Sub

И поэтому у нас будет две процедуры, одна ниже другой. Они будут расположены в алфавитном порядке в списке макросов; запомните имя процедуры (Sub)

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

Именно так работает сегодняшний пример. Возможности языка VBA огромны, и с его помощью для Excel вы можете значительно расширить возможности работы, для себя. Удачи в изучении Excel! Пока!

Применение оператора СЦЕПИТЬ

Функция SHIFT относится к группе текстовых операторов Excel. Ее основная функция заключается в объединении содержимого нескольких ячеек вместе с отдельными символами. В Excel 2016 этот оператор был заменен функцией CEPE. Для обратной совместимости оператор CEPY также оставлен на месте и может использоваться так же, как и раньше.

В данном случае синтаксис следующий:

Аргументами могут быть как текст, так и ссылки на содержащие его ячейки. Можно иметь от 1 до 255 аргументов.

Способ 1: объединение данных в ячейках

В Excel обычное объединение ячеек может привести к потере данных. Сохраняются только данные, расположенные в верхнем левом элементе. С помощью функции SHIFT в Excel можно объединить содержимое двух или более ячеек, не нарушая их порядок.

    Выделяем ячейку, в которой планируем разместить объединенные данные. Кликаем на кнопку «Вставить функцию». Она имеет вид пиктограммы и размещена слева от строки формул.

Переход в Мастер функций в Microsoft Excel

Мастер функций в Microsoft Excel

Поместите курсор в первое поле. Затем выделите ссылку листа, содержащего данные, которые вы хотите объединить. Как только координаты появятся в поле, проделайте то же самое со вторым полем. Выберите другую ячейку соответственно. Такую же операцию выполняют до тех пор, пока в окне аргументов функции не будут введены координаты всех объединяемых ячеек. Затем нажмите на кнопку «OK».

Аргументы функции СЦЕПИТЬ в программе Microsoft Excel

Результат функции СЦЕПИТЬ в Microsoft Excel

Урок: Мастер создания функций Excel

Способ 2: применение функции с пробелом

Эта проблема может быть решена путем вставки пробелов между аргументами оператора.

  1. Выполняем задачу по тому же алгоритму, который описан выше.
  2. Двойным щелчком левой кнопки мыши по ячейке с формулой активируем её для редактирования.

Активация ячейки для редактирования функции СЦЕПИТЬ в Microsoft Excel

Изменения внесены в Microsoft Excel

Пробелы в функции СЦЕПИТЬ в Microsoft Excel установлены

Похоже, что вместо пробелов с кавычками между словами теперь остаются пустые пробелы.

Способ 3: добавление пробела через окно аргументов

Приведенный выше способ разбиения на ячейки будет работать, если нужно преобразовать не так много значений. Однако если есть много ячеек, которые нужно объединить, будет трудно сделать это быстро. Проблема усугубляется, если эти ячейки не находятся в массиве. Можно значительно упростить расположение пространства, используя возможность вставки через окно аргументов.

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

Ячейка с пробелом в Microsoft Excel

Добавление пустого аргумента СЦЕПИТЬ в Microsoft Excel

Аргументы функции СЦЕПИТЬ в Microsoft Excel

Как видите, теперь в целевой ячейке есть связанная запись, содержащая содержимое всех элементов, но с пробелами между каждым словом.

Результат обработки данных функцией СЦЕПИТЬ в Microsoft Excel

Обратите внимание! Как вы можете видеть, приведенный выше метод значительно ускоряет процесс правильного объединения данных в ячейки. Однако имейте в виду, что и у этого варианта есть свои подводные камни. Очень важно, чтобы элемент, содержащий пространство, в конечном итоге не содержал никаких данных, или чтобы он не был перемещен.

Способ 4: объединение колонок

С помощью функции CLUT можно быстро объединить данные из нескольких столбцов в один.

    С ячейками первой строки объединяемых колонок проделываем на выбор те действия, которые указаны во втором и третьем способе применения аргумента. Правда, если вы решили воспользоваться способом с пустой ячейкой, то ссылку на неё нужно будет сделать абсолютной. Для этого, перед каждым знаком координат по горизонтали и вертикали этой ячейки ставим знак доллара ($). Естественно, что лучше всего это сделать в самом начале, чтобы в другие поля, где содержится этот адрес, пользователь мог копировать его, как содержащий постоянные абсолютные ссылки. В остальных полях оставляем относительные ссылки. Как всегда, после выполнения процедуры, жмем на кнопку «OK».

Абсолютные ссылки в аргументах функции СЦЕПИТЬ в Microsoft Excel

Маркер заполнения в Microsoft Excel

Столбцы объединены функцией СЦЕПИТЬ в Microsoft Excel

Урок: Как объединить колонки в Exel

Способ 5: добавление дополнительных символов

Функция CLUT также позволяет добавлять на вход дополнительные символы и выражения, которые не входили в исходный диапазон. Кроме того, с помощью этой функции можно вводить другие операторы.

    Выполняем действия по добавлению значений в окно аргументов функции любым из способов, который был приведен выше. В одно из полей (при необходимости их может быть и несколько) добавляем любой текстовый материал, который пользователь считает нужным добавить. Данный текст обязательно должен быть заключен в кавычки. Жмем на кнопку «OK».

Добавление текстового материала с помощью функции СЦЕПИТЬ в Microsoft Excel

Текстовый материал добавлен с помощью функции СЦЕПИТЬ в Microsoft Excel

Использование SHIFT в Excel — единственный метод объединения ячеек без потерь. Кроме того, с ее помощью можно объединять целые столбцы, добавлять текстовые значения и выполнять некоторые другие операции. Знание того, как использовать эту функцию, облегчит программистам решение многих задач.

Замена числовых, логических значений или значений даты и времени

Использование ленты редактора запросов

На ленте Редактора запросов, на вкладке « преобразовать » выберите Замена значений.

Контекстное меню редактора запросов может использоваться следующим образом:

Щелкнув правой кнопкой мыши ячейку с числовыми значениями или значениями даты/времени, можно заменить значения.

Чтобы заменить числовые, логические значения или значения даты и времени:

Значения для поиска и замены вводятся в диалоговом окне Заменить значения.

Нажмите кнопку ОК.

Если вы загружаете, редактируете или создаете запрос с помощью Power Query, появляется редактор запросов. Здесь показано видео редактора запросов, который появляется после изменения запроса в рабочей книге Excel. В разделе Retrieve External Data на вкладке ленты Power Query выберите From Other Sources > Blank Query, чтобы открыть редактор запросов без загрузки или изменения существующих запросов в книге. В видео редактор запросов отображается одним способом.

голоса
Рейтинг статьи
Читайте так же:
Как объединить столбцы с помощью Alt + Enter в Excel?
Ссылка на основную публикацию
Adblock
detector