Как перебрать ячейки в excel
Знакомство с объектной моделью Excel следует начинать с такого замечательного объекта, как Range . Поскольку любая ячейка - это Range , то без знания, как с этим объектом эффективно взаимодействовать, вам будет затруднительно программировать для Excel. Это очень ладно-скроенный объект. При некоторой сноровке вы найдёте его весьма удобным в эксплуатации.
Что такое объекты?
Мы собираемся изучать объект Range , поэтому пару слов надо сказать, что такое, собственно, " объект ". Всё, что вы наблюдаете в Excel, всё с чем вы работаете - это набор объектов. Например, лист рабочей книги Excel - не что иное, как объект типа WorkSheet . Однотипные объекты объединяют в коллекции себе подобных. Например, листы объединены в коллекцию Sheets . Чтобы не путать друг с другом объекты одного и того же типа, они имеют отличающиеся имена, а также номер индекса в коллекции. Объекты имеют свойства , методы и события .
Свойства - это информация об объекте. Часто эти свойства можно менять, что автоматически влечет изменения внешнего вида объекта или его поведения. Например свойство Visible объекта Worksheet отвечает за видимость листа на экране. Если ему присвоить значение xlSheetHidden (это константа, которая по факту равно нулю), то лист будет скрыт.
Методы - это то, что объект может делать. Например, метод Delete объекта Worksheet удаляет себя из книги. Метод Select делает лист активным.
События - это механизм, при помощи которого вы можете исполнять свой код VBA сразу по факту возникновения того или иного события с вашим объектом. Например, есть возможность выполнять ваш код, как только пользователь сделал текущим определенный лист рабочей книги, либо как только пользователь что-то изменил на этом листе.
Объекты Range
Range это диапазон ячеек. Минимум - одна ячейка, максимум - весь лист, теоретически насчитывающий более 17 миллиардов ячеек (строки 2^20 * столбцы 2^14 = 2^34).
В Excel объявлены глобально и всегда готовы к использованию несколько коллекций, имеющий членами объекты типа Range , либо свойства это же типа. Коллекции глобального объекта Application : Cells , Columns , Rows , а также свойства Range , Selection , ActiveCell , ThisCell .
ActiveCell - активная ячейка текущего листа, ThisCell - если вы написали пользовательскую функцию рабочего листа, то через это свойство вы можете определить какая конкретно ячейка в данный момент пересчитывает вашу функцию. Об остальных перечисленных объектов речь пойдёт ниже.
Работа с отдельными ячейками
| Синтаксическая форма | Комментарии по использованию |
| Range (" D5 ") или [ D5 ] | Ячейка D5 текущего листа. Полная и краткая формы. Тут применим только синтаксис типа A1, но не R1C1. То есть такая конструкция Range (" R1C2 ") - вызовет ошибку, даже если в книге Excel включен режим формул R1C1. Разумеется после этой формы вы можете обратиться к свойствам соответствующей ячейки. Например, Range (" D5 ") .Interior.Color = RGB(0, 255, 0) . |
| Cells(5, 4) или Cells(5, "D") | Ячейка D5 текущего листа через свойство Cells . 5 - строка (row), 4 - столбец (column). Допустимость второй формы мало кому известна. |
| Cells(65540) | Ячейку D5 можно адресовать и через указание только одного параметра свойсва Cells . При этом нумерация идёт слева направо, потом сверху вниз. То есть сначала нумеруется вся строка (2^14=16384 колонок) и только потом идёт переход на следующую строку. То есть Cells(16385) вернёт вам ячейку A2 , а D5 будет Cells(65540) . Пока данный способ выглядит не очень удобным. |
Работа с диапазоном ячеек
| Синтаксическая форма | Комментарии по использованию |
| Range ( "A1:B4 ") или [ A1:B4 ] | Диапазон ячеек A1:B4 текущего листа. Обратите внимание, что указываются координаты верхнего левого и правого нижнего углов диапазона. Причём первый указываемый угол вполне может быть правым нижним, это не имеет значения. |
| Range(Cells(1, 1), Cells(4, 2)) | Диапазон ячеек A1:B4 текущего листа. Удобно, когда вы знаете именно цифровые координаты углов диапазона. |
Работа со строками
| Синтаксическая форма | Комментарии по использованию |
| Range (" 3:5 ") или [ 3:5 ] | Строки 3, 4 и 5 текущего листа целиком. |
| Range (" A3:XFD3 ") или [ A3:XFD3 ] | Строка 3, но с указанием колонок. Просто, чтобы вы понимали, что это тождественные формы. XFD - последняя колонка листа. |
| Rows (" 3:3 ") | Строка 3 через свойство Rows . Параметр в виде диапазона строк. Двоеточие - это символ диапазона. |
| Rows(3) | Тут параметр - индекс строки в массиве строк. Так можно сослаться только не конкретную строку. Обратите внимание, что в предыдущем примере параметр текстовая строка " 3:3 " и она взята в кавычки, а тут - чистое число. |
Работа со столбцами
| Синтаксическая форма | Комментарии по использованию |
| Range (" B:B ") или [ B:B ] | Колонка B текущего листа. |
| Range (" B1:B1048576 ") или [ B1:B1048576 ] | То же самое, но с указанием номеров строк, чтобы вы понимали, что это тождественные формы. 2^20=1048576 - максимальный номер строки на листе. |
| Columns (" B:B ") | То же самое через свойство Columns . Параметр - текстовая строка. |
| Columns(2) | То же самое. Параметр - числовой индекс столбца. "A" -> 1, "B" -> 2, и т.д. |
Весь лист
| Синтаксическая форма | Комментарии по использованию |
| Range (" A1:XFD1048576 ") или [ A1:XFD1048576 ] | Диапазон размером во всё адресное пространство листа Excel. Воспринимайте эту таблицу лишь как теорию - так работать с листами вам не придётся - слишком большое количество ячеек. Даже современные компьютеры не смогут помочь Excel быстро работать с такими массивами информации. Тут проблема больше даже в самом приложении. |
| Range (" 1:1048576 ") или [ 1:1048576 ] | То же самое, но через строки. |
| Range (" A:XFD ") или [ A:XFD ] | Аналогично - через адреса столбцов. |
| Cells | Свойство Cells включает в себя ВСЕ ячейки. |
| Rows | Все строки листа. |
| Columns | Все столбцы листа. |
Следует иметь в виду, что свойства Range , Cells , Columns и Rows имеют как объекты типа Worksheet , так и объекты Range . Соответственно в первом случае эти коллекции будут относиться ко всему листу и отсчитываться будут от A1 , а вот в случае конкретного объекта Range эти коллекции будут относиться только к ячейкам этого диапазона и отсчитываться будут от левого верхнего угла диапазона. Например Cells(2,2) указывает на ячейку B2 , а Range("C3:D5").Cells(2,2) укажет на D4 .
Также много путаницы в умы вносит тот факт, что объект Range имеет одноименное свойство range . К примеру, Range("A100:D500").Range("A2") - тут выражение до точки ( Range("A100:D500") ) является объектом Range , выражение после точки ( Range("A2") ) - свойство range упомянутого объекта, но возвращает это свойство тоже объект типа Range . Вот такие пироги. Из этого следует, что такая цепочка может иметь и более двух членов. Практического смысла в этом будет не много, но синтаксически это будут совершенно корректно, например, так: Range("CV100:GR200").Range("J10:T20").Range("A1:B2") укажет на диапазон DE109:DF110 .
Ещё один сюрприз таится в том, что объекты Range имеют свойство по-умолчанию Item( RowIndex [, ColumnIndex] ) . По правилам VBA при ссылке на default свойства имя свойства ( Item ) можно опускать. Кстати говоря, то что вы привыкли видеть в скобках после Cells , есть не что иное, как это дефолтовое свойство Item , а не родные параметры Cells , который их не имеет вовсе. Ну ладно к Cells все привыкли и это никакого отторжения не вызывает, но если вы увидите нечто подобное - Range("C3:D5")(2,2) , то, скорее всего, будете несколько озадачены, а тем временем - это буквально тоже самое, что и у Cells - всё то же дефолтовое свойство Item . Последняя конструкция ссылается на D4 . А вот для Columns и Rows свойство Item может быть только одночленным, например Columns(1) - и к этой форме мы тоже вполне привыкли. Однако конструкции вида Columns(2)(3)(4) могут сильно удивить (столбец 7 будет выделен).
Примеры кода
Типовые задачи
Перебор ячеек в диапазоне (вариант 1)
В данном примере организован цикл For. Next и доступ к ячейкам осуществляется по их индексу. Вместо parRange(i) мы могли бы написать parRange.Item(i) (выше это объяснялось). Обратите внимание, что мы в этом примере успешно применяем, как вариант с parRange(i,c) , так и parRange(i) . То есть, если мы применяем одночленную форму свойства Item , то диапазон перебирается по строкам ( A1 , B1 , C1 , A2 , . ), а если двухчленную, то столбец у нас зафиксирован и каждая итерация цикла - на новой строке. Это очень интересный эффект, его можно применять для вытягивания таблиц по вертикали. Но - продолжим!
Количество ячеек в диапазоне получено при помощи свойства .Count . Как .Item , так и .Count - это всё атрибуты коллекций, которые широко применяются в объектой модели MS Office и, в частности, Excel.
Вы можете разделить содержимое ячейки и распределить ее части по нескольким смежным ячейкам. Например, если ваш сайт содержит столбец "Полное имя", его можно разделить на два столбца: столбец "Имя" и "Фамилия".
Альтернативный способ распределения текста по столбцам см. в статье "Разделение текста по столбцам с помощью функций".
Ячейки можно объединить с помощью функции СОВКА или С CONCATENATE.
Выполните указанные ниже действия:
Примечание: Диапазон, содержащий столбец, который вы хотите разделить, может включать любое количество строк, но не более одного столбца. Важно оставить достаточное количество пустых столбцов справа от выбранного столбца, чтобы данные в соседних столбцах не перезаписывалися данными, которые нужно распределить. При необходимости вставьте количество пустых столбцов, которых будет достаточно для всех составляющих частей распределенных данных.
Выделите ячейку, диапазон или весь столбец, где содержатся текстовые значения, которые требуется разделить.
На вкладке Данные в группе Работа с данными нажмите кнопку Текст по столбцам.
Следуя инструкциям мастера текстов, укажите, как вы хотите разделить текст на отдельные столбцы.
Примечание: Чтобы получить справку по выполнению всех действий мастера, см. статью, разделите текст на разные столбцы с помощью мастера текстов или нажмите кнопку " в мастере текстов".
Эта функция недоступна в Excel в Интернете.
Если у вас есть настольное приложение Excel, вы можете нажать кнопку "Открыть в Excel", чтобы открыть книгу и распределить содержимое ячейки на соседние столбцы.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
| из | сделать |
В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
Способ 1. Без макросов
Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).
Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :
Не снимая выделения, вводим в первую ячейку знак "равно" и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):
И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.
В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
Способ 2. Заполнение пустых ячеек макросом
Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert - Module и копируем или вводим туда вот такой короткий код:
Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.
Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.
Способ 3. Power Query
Power Query - это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно - в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:
- Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
- При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов - всё делать заново.
Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы - Диспетчер имен), либо превратить в "умную" таблицу командой Главная - Форматировать как таблицу (Home - Format as Table ) или сочетанием клавиш Ctrl + T :
После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.
В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить - Заполнить вниз (Transform - Fill - Fill Down) :
Вот и всё :) Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to. )
В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные - Обновить всё (Data - Refresh All) .
Перечислите или сгенерируйте все возможные комбинации из двух списков с формулой
Следующая длинная формула поможет вам быстро составить список всех возможных комбинаций значений двух списков. Пожалуйста, сделайте следующее:
1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, в этом случае я введу ее в ячейку D2, а затем нажмите Enter ключ для получения результата, см. снимок экрана:
=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$5)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$5,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))
2. Затем выберите ячейку D2 и перетащите маркер заполнения вниз к ячейкам, пока не получите пустые ячейки, и все возможные комбинации будут перечислены на основе значений двух списков. Смотрите скриншот:
Перечислите или сгенерируйте все возможные комбинации из трех или более списков с кодом VBA
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модульи вставьте следующий код в Модуль Окно.
Код VBA: генерировать все комбинации из 3 или нескольких столбцов
Внимание: В приведенном выше коде A2: A5, B2: B4, C2: C4 диапазон данных, который вы хотите использовать, E2 - это выходная ячейка, в которой вы хотите найти результаты. Если вы хотите получить все комбинации из большего количества столбцов, измените и добавьте другие параметры в код по мере необходимости.3. Затем нажмите F5 ключ для запуска этого кода, и все комбинации из 3 столбцов будут сгенерированы сразу, см. снимок экрана:
Составьте список или сгенерируйте все возможные комбинации из нескольких списков с помощью мощной функции
Если имеется несколько значений списков, необходимо указать возможные комбинации, возможно, вам будет сложно изменить код. Здесь я могу порекомендовать мощный инструмент - Kutools for Excel, он содержит удобную функцию Список всех комбинаций который может быстро перечислить все возможные комбинации на основе заданных списков данных.
Советы: Чтобы применить это Список всех комбинаций функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите на Kutools > Вставить > Список всех комбинаций, см. снимок экрана:
2. В Список всех комбинаций В диалоговом окне выполните операции, как показано в демонстрации ниже:
3. Затем все указанные значения и разделители были перечислены в диалоговом окне, см. Снимок экрана:
4.А затем нажмите Ok Кнопка, и появится окно подсказки, напоминающее вам о выборе ячейки для вывода результата, см. снимок экрана:
5. Нажмите OK, все возможные комбинации на основе данных списков были сгенерированы на листе, как показано на следующем снимке экрана:
Читайте также: