Excel диапазон с разрывом
Обновлено: 07.07.2024
Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:
- ссылки в формулах отчетов, которые ссылаются на нашу таблицу
- исходные диапазоны сводных таблиц, которые построены по нашей таблице
- исходные диапазоны диаграмм, построенных по нашей таблице
- диапазоны для выпадающих списков, которые используют нашу таблицу в качестве источника данных
Все это в сумме не даст вам скучать ;)
Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.
Способ 1. Умная таблица
Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):
Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .
Теперь можно использовать динамические ссылки на нашу «умную таблицу»:
Такие ссылки замечательно работают в формулах, например:
=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»
=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)
Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:
Если выделить фрагмент такой таблицы (например, первых два столбца) и создать диаграмму любого типа, то при дописывании новых строк они автоматически будут добавляться к диаграмме.
При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:
Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.
Способ 2. Динамический именованный диапазон
Если превращение ваших данных в умную таблицу по каким-либо причинам нежелательно, то можно воспользоваться чуть более сложным, но гораздо более незаметным и универсальным методом – создать в Excel динамический именованный диапазон, ссылающийся на нашу таблицу. Потом, как и в случае с умной таблицей, можно будет свободно использовать имя созданного диапазона в любых формулах, отчетах, диаграммах и т.д. Для начала рассмотрим простой пример:
Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.
Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.
Ищем последнюю ячейку с помощью ПОИСКПОЗ
ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.
Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!
Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:
Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.
Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» - последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:
Формируем ссылку с помощью ИНДЕКС
Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:
ИНДЕКС(диапазон; номер_строки; номер_столбца)
Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.
Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.
И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:
=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255) ;A2:A100))
Создаем именованный диапазон
Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :
Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.
Народ подскажите. Надо выделить в Excel программно несколько строк, но не подряд, а с разрывами – типа зажав ctrl.
Записав макрос в Excel получил такой код
COM toRange = activeSheet.Range(“7:10,13:16”);
Вываливается с ошибкой (неизвестно)
Хотя activeSheet.Range(«7:10»); - прокатывает
Как по-другому выделить несколько диапазонов?
Попробуй для разделения диапазонов вместо запятой указать точку с запятой. Как вариант, вместо строки передавать вариантный массив. Если найду пример передачи из Аксы, выложу (в свое время в 1С выкручивались именно с вариантным массивом, насчет аксы не помню, есть ли пример).Добрый день, попробуйте почитать вот этот пост Еще проблема с Excel при построении диаграмм
может натолкнёт на мысли
__________________It's just my Unhopelessnessabilityerism
Trying to debug my mind А какой символ у вас стоит в качестве list separator в Regional and Lanquage Option?
Что возвращает этот код:
Точка с запятой.. Я и ее и запятую пробовал.. Должно работать думаете? Вай шайтан, заработало, точка с запято, почему-то не с первого раза, видимо поняла что упираться безсмыслено, всем спасибо! Вай шайтан, заработало, точка с запято, почему-то не с первого раза, видимо поняла что упираться безсмыслено, всем спасибо!
Да, эт я понял.
PS Я понял почму у меня не работало - корректно работает тольо с короткими строками, насколько кроткими не проверял, но вот на такой уже сыпется
а щассе было так возможно.. придется дробить видимо..
Дозвольте со своим пятаком влезть. Существует более человеческий способ объединения диапазонов - методом Union объекта Excel.Application. Его выгодное преимущество - он не зависит от капризов региональных настроек:корректно работает тольо с короткими строками, насколько кроткими не проверял, но вот на такой уже сыпется
"6:10;12:16;18:22;24:28;30:34;36:40;42:46;48:52;54:58;60:64;66:70;72:76; 78:82;84:88;90:94;96:100;102:106;108:112;114:118;120:124;126:130;132:136;138:142;144:148; 150:154;156:160;162:166;168:172;174:178;180:184;186:190;192:196;198:202;204:208;210:214;216:220;222:226;228:232;234:238;240:244"
На ум приходит ограничение для функций Excel в 30 аргументов. Наверное, тут действуют те же принципы. А в этой строке их 40.
Про 30 аргументов можно увидеть, например, в окне отладки Excel, введя:
при этом в качестве подсказки высветится список аргументов, последний из которых будет Arg30.
P.S. Впрочем, пишут, что в Excel 2007 "Максимальное число аргументов функции увеличилось с 30 до 255."
Да,цивилизованный метод рулит
PS правда поскорости сливает.. жость..
лан, пища есть- бум переваривать..
Кстати, а зачем нужно программно выполнять такую задачу? Мы в свое время делали это для построения диаграмм. Какие еще задачи требуют этого? Задался целю копировать формат секции отчета сразу на диапазон ячеек (именно форматирование - специальой вставкой) Т.е. данные в отчет выводятся скопом через ADO или буфер обмена, а стиль накладывается сверху. Поскольку секция строк прерывается секцией групп - зпоминаю все диапозоны в отчете и разом к ним применяю формат секции.
Вобщем пока что остановился на варианте - строка через точку с запятой, но по 30 значений (дапазонов) в цикле форматирую. Последний раз редактировалось Gustav; 19.12.2008 в 09:15 . Причина: первоначально из-за опечатки было написано ДДЕ (вместо ADO) Задался целю копировать формат секции отчета сразу на диапазон ячеек (именно форматирование - специальой вставкой) Т.е. данные в отчет выводятся скопом через ADO или буфер обмена, а стиль накладывается сверху. Поскольку секция строк прерывается секцией групп - зпоминаю все диапозоны в отчете и разом к ним применяю формат секции. Так а скопировать секцию группы+секцию данных на целократный большой диапазон? Сформатировать строку 5, строки 6:10, а затем скопировать строки 5:10 в строки 11:244. В секциях одинаковое количество строк? Последний раз редактировалось Gustav; 19.12.2008 в 09:25 .
Иван, вот еще вариант из старого чулана. Думаю, должен понравиться
Если ты можешь использовать какую-то свободную колонку для простановки метки, которая будет означать, что это строка данных, то можно попробовать так (эту служебную колонку потом можно удалить):
1. Выбираешь пустую колонку. В VBA-примере ниже - это колонка A.
2. Если строку надо выделять и форматитровать, то пишем в эту колонку 1. В противном случае оставляем ячейку пустую.
3. Выполняем "ручную" операцию "Правка - Перейти - Выделить - Константы - Числа".
4. Получившееся выделение распространяем на всю ширину листа.
На VBA это выглядит примерно вот так:
Результат работы этого кода:
Эх понравилось.. тут получается правда под каждую секцию отчета нужно свою колонку отдельную.. жаль при "найти и выделить" нельзя указать конкретное значение типа 1 или 2.. ну тоже вариант по-любому тут получается правда под каждую секцию отчета нужно свою колонку отдельную.. жаль при "найти и выделить" нельзя указать конкретное значение типа 1 или 2.. Не нужно отдельную колонку. Конкретно для двух вариантов можно использовать число для одного и текст для другого и сформировать два разных составных диапазона (синий и желтый):И еще в запасе несколько подобных "индикаторов" - пустые, логические, ошибочные даже (можно ввести в ячейку формулу =0/0 и получить ошибку, которую можно выделить ):
Но, согласен, если бы можно было помимо "Константа - Число" еще при этом и конкретное число указывать - было бы очень удобно для подобных манипуляций.
если бы можно было помимо "Константа - Число" еще при этом и конкретное число указывать - было бы очень удобно для подобных манипуляций.Как мне только что стало ясно, достаточно всего двух колонок: в одной целые числа - метки секций, а в другой формула-индикатор. После окончания вывода всех данных отчета значения индикатора меняем в небольшом цикле, последовательно перебирающем все возможные уникальные значения из первой колонки.
На каждом шаге фиксируется диапазон (несколько несоседних строк) для конкретной метки. "Фиксируется" - в соответствующую объектную переменную, в Аксапте это будут COMы, здесь в Excel мы можем себе позволить массив типа Range.
С каждым таким составным диапазоном потом можно делать что угодно. В нижеследующем VBA-примере я их расцвечиваю разными цветами:
В этой статье говорится о объединении строки / столбца в одну ячейку с разделением запятой или пробелом, а также о объединении диапазона в одну строку / столбец / ячейку с разделением содержимого каждой ячейки любыми разделителями, такими как запятая, пробел, разрыв строки и т. д. в Excel.
- Объедините строку с запятой / пробелом по формуле
- Объедините столбец с запятой / пробелом по формуле
- Объедините диапазон / строку / столбец с запятой / пробелом / разрывом строки
Объедините строку с запятой / пробелом по формуле
Например, у вас есть строка имен, как показано на скриншоте ниже, и вам нужно объединить эту строку в одну ячейку и разделить содержимое ячейки запятой или пробелом. Вы можете применить функцию СЦЕПИТЬ следующим образом:
1. Выберите пустую ячейку, в которую вы поместите результат конкатенации, и введите формулу. = СЦЕПИТЬ (B1: J1 & ",") внутрь.
2. Выделите B1: J1 & "," в формуле и нажмите F9 клавиша для замены ссылок на ячейки содержимым ячейки.
3. Снимите фигурные скобки. < и >из формулы и нажмите Enter ключ. Смотрите скриншот:
Внимание: В приведенной выше формуле B1: J1 - это строка, которую вы объедините, а «,» означает, что вы разделите содержимое каждой ячейки запятой. Для разделения пробелом замените в формуле "," на "".
Пакетное объединение нескольких ячеек / строк / столбцов без потери данных и формата даты / числа в Excel
Обычно при объединении ячеек с помощью функции «Слияние» в Excel удаляется все содержимое ячеек, кроме содержимого первой ячейки. Однако с Kutools for Excel's Объединить (строки и столбцы)утилита, вы можете легко объединить несколько ячеек / строк / столбцов без потери данных. Более того, эта утилита также поддерживает оставшийся исходный формат даты и числовые форматы в результатах комбинации. Полнофункциональная бесплатная 30-дневная пробная версия!
Объедините столбец с запятой / пробелом по формуле
Предположим, у вас есть столбец с именами, как показано на скриншоте ниже, вы также можете применить функцию СЦЕПИТЬ, чтобы объединить этот столбец / список в одну ячейку и разделить содержимое каждой ячейки запятой или пробелом в Excel.
1. Выберите пустую ячейку, в которую вы поместите результат конкатенации, и введите формулу. = СЦЕПИТЬ (ТРАНСПОРТ (A2: A16) & ",") внутрь.
2. Выделите ТРАНСПОРТ (A2: A16) & "," в формуле и нажмите F9 клавиша для замены ссылки на ячейку содержимым ячейки.
3. Снимите фигурные скобки. < и >из формулы и нажмите Enter ключ. Смотрите скриншот выше:
Внимание: В приведенной выше формуле A2: A16 - это столбец / список, который вы объедините, и »,« означает, что вы разделите содержимое каждой ячейки запятой. Для разделения пробелом замените »,« с «» в формуле.
Объедините диапазон / строку / столбец с запятой / пробелом / разрывом строки Kutools for Excel
Например, у вас есть диапазон, как показано на скриншоте ниже, вы можете легко объединить этот диапазон в один столбец, одну строку или одну ячейку с помощью Kutools for Excel's Сочетать утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите диапазон, который вы хотите объединить, и нажмите Kutools > Слияние и разделение> Объединение строк, столбцов или ячеек без потери данных. Смотрите скриншот:
2. В открывшемся диалоговом окне сделайте следующее:
(1) В Чтобы объединить выбранные ячейки в соответствии со следующими параметрами В разделе укажите вариант комбинации в зависимости от ваших потребностей. В нашем случае мы проверяем Объединить столбцы вариант;
(2) В Укажите разделитель раздел, отметьте разделитель по мере необходимости. В нашем случае мы проверяем Новая линия вариант;
(3) Укажите вариант из Поместите результаты в раскрывающийся список. В нашем случае мы выбираем Левая ячейка.
(4) В Параметры раздел, пожалуйста, проверьте Удалить содержимое объединенных ячеек вариант. Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
3. Нажмите Ok кнопку.
Затем вы увидите, что выбранные столбцы объединены в один столбец, а ячейки в каждой строке объединены в первую ячейку этой строки. Смотрите скриншот:
Выберите Объединить ряды вариант в Чтобы объединить выбранные ячейки в соответствии со следующей опцией раздел:
Выберите опцию Объединить в одну ячейку в Чтобы объединить выбранные ячейки в соответствии со следующей опцией раздел:
Обычно вы можете нажать клавиши Alt + Enter, чтобы разделить содержимое ячейки на несколько строк внутри ячейки, но как вы можете отфильтровать все ячейки с этими символами разрыва строки в Excel?
Отфильтруйте все ячейки с разрывом строки или возвратом каретки с помощью функции фильтра
Следующие шаги могут помочь вам отфильтровать все ячейки, содержащие символы разрыва строки, сделайте следующее:
1. Дважды щелкните ячейку под данными столбца, которые вы хотите отфильтровать, а затем нажмите Alt + Enter клавиши для ввода одинарного разрыва строки, см. снимок экрана:
2. Затем щелкните первую ячейку столбца, который вы хотите отфильтровать, а затем щелкните Данные > Фильтр, затем щелкните стрелку раскрывающегося списка, чтобы выбрать Текстовые фильтры > Пользовательский фильтр, см. снимок экрана:
3. В выскочившем Пользовательский автофильтр диалоговом окне выберите содержит из раскрывающегося списка в Текстовая строка раздел, а затем выберите пустую строку, которая содержит разрыв строки, который вы создали в соседнем раскрывающемся списке, см. снимок экрана:
4. Потом выберите Разумеется, вариант и продолжайте выбирать не равно из второго раскрывающегося списка, затем снова выберите пустую строку из раскрывающегося списка, см. снимок экрана:
5. Нажмите OK кнопку, и все ячейки с символами разрыва строки были отфильтрованы, как показано на следующем снимке экрана:
Читайте также: