Excel vba выделить столбец
Полагаю не совру когда скажу, что все кто программирует в VBA очень часто в своих кодах общаются к ячейкам листов. Ведь это чуть ли не основное предназначение VBA в Excel. В принципе ничего сложного в этом нет. Например, чтобы записать в ячейку A1 слово Привет необходимо выполнить код:
Тоже самое можно сделать сразу для нескольких ячеек:
Если необходимо обратиться к именованному диапазону:
Диапазон1 - это имя диапазона/ячейки, к которому надо обратиться в коде. Указывается в кавычках, как и адреса ячеек.
Но в VBA есть и альтернативный метод записи значений в ячейке - через объект Cells:
Cells(1, 1).Value = "Привет"
Синтаксис объекта Range:
Range(Cell1, Cell2)
- Cell1 - первая ячейка диапазона. Может быть ссылкой на ячейку или диапазон ячеек, текстовым представлением адреса или имени диапазона/ячейки. Допускается указание несвязанных диапазонов(A1,B10), пересечений(A1 B10).
- Cell2 - последняя ячейка диапазона. Необязательна к указанию. Допускается указание ссылки на ячейку, столбец или строку.
Синтаксис объекта Cells:
Cells(Rowindex, Columnindex)
Исходя из этого несложно предположить, что к диапазону можно обратиться, используя Cells и Range:
'выделяем диапазон "A1:B10" на активном листе Range(Cells(1,1), Cells(10,2)).Select
и для чего? Ведь можно гораздо короче:
Иногда обращение посредством Cells куда удобнее. Например для цикла по столбцам(да еще и с шагом 3) совершенно неудобно было бы использовать буквенное обозначение столбцов.
Объект Cells так же можно использовать для указания ячеек внутри непосредственно указанного диапазона. Например, Вам необходимо выделить ячейку в 3 строке и 2 столбце диапазона "D5:F56" . Можно пройтись по листу и посмотреть, отсчитать нужное количество строк и столбцов и понять, что это будет "E7" . А можно сделать проще:
Согласитесь, это гораздо удобнее, чем отсчитывать каждый раз. Особенно, если придется оперировать смещением не на 2-3 ячейки, а на 20 и более. Конечно, можно было бы применить Offset. Но данное свойство именно смещает диапазон на указанное количество строк и столбцов и придется уменьшать на 1 смещение каждого параметра для получения нужной ячейки. Да и смещает на указанное количество строк и столбцов весь диапазон, а не одну ячейку. Это, конечно, тоже не проблема - можно вдобавок к этому использовать метод Resize - но запись получится несколько длиннее и менее наглядной:
Range("D5:F56").Offset(2, 1).Resize(1, 1).Select
И неплохо бы теперь понять, как значение диапазона присвоить переменной. Для начала переменная должна быть объявлена с типом Range. А т.к. Range относится к глобальному типу Object, то присвоение значения такой переменной должно быть обязательно с применением оператора Set:
Dim rR as Range Set rR = Range("D5")
если оператор Set не применять, то в лучшем случае получите ошибку, а в худшем(он возможен, если переменной rR не назначать тип) переменной будет назначено значение Null или значение ячейки по умолчанию. Почему это хуже? Потому что в таком случае код продолжит выполняться, но логика кода будет неверной, т.к. эта самая переменная будет содержать значение неверного типа и применение её в коде в дальнейшем все равно приведет к ошибке. Только ошибку эту отловить будет уже сложнее.
Использовать же такую переменную в дальнейшем можно так же, как и прямое обращение к диапазону:
Вроде бы на этом можно было завершить, но. Это как раз только начало. То, что я написал выше знает практически каждый, кто пишет в VBA. Основной же целью этой статьи было пояснить некоторые нюансы обращения к диапазонам. Итак, поехали.
Обычно макрорекордер при обращении к диапазону(да и любым другим объектам) сначала его выделяет, а потом уже изменяет свойство или вызывает некий метод:
'так выглядит запись слова Test в ячейку А1 Range("A1").Select Selection.Value = "Test"
Но как правило выделение - действие лишнее. Можно записать значение и без него:
'запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"
Теперь чуть подробнее разберем, как обратиться к диапазону не выделяя его и при этом сделать все правильно. Диапазон и ячейка - это объекты листа. У каждого объекта есть родитель - грубо говоря это другой объект, который является управляющим для дочернего объекта. Для ячейки родительский объект - Лист, для Листа - Книга, для Книги - Приложение Excel. Если смотреть на иерархию зависимости объектов, то от старшего к младшему получится так:
Applicaton => Workbooks => Sheets => Range
По умолчанию для всех диапазонов и ячеек родительским объектом является текущий(активный) лист. Т.е. если для диапазона(ячейки) не указать явно лист, к которому он относится, в качестве родительского листа для него будет использован текущий - ActiveSheet:
'запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"
Т.е. если в данный момент активен Лист1 - то слово Test будет записано в ячейку А1 Лист1. Если активен Лист3 - в А1 Лист3. Иначе говоря такая запись равносильна записи:
Поэтому выхода два - либо активировать сначала нужный лист, либо записать без активации.
'активируем Лист2 Worksheets("Лист2").Select 'записываем слово Test в ячейку A1 Range("A1").Value = "Test"
Чтобы не активируя другой лист записать в него данные, необходимо явно указать принадлежность объекта Range именно этому листу:
'запишем слово Test в ячейку A1 на Лист2 независимо от того, какой лист активен Worksheets("Лист2").Range("A1").Value = "Test"
Таким же образом происходит считывание данных с ячеек - если не указывать лист, данные ячеек которого необходимо считать - считаны будут данные с ячейки активного листа. Чтобы считать данные с Лист2 независимо от того, какой лист активен применяется такой код:
'считываем значение ячейки A1 с Лист2 независимо от того, какой лист активен MsgBox Worksheets("Лист2").Range("A1").Value
Т.к. ячейка является частью листа, то лист в свою очередь является частью книги. Исходя из того легко сделать вывод, что при открытых двух и более книгах мы так же можем обратиться к ячейкам любого листа любой открытой книги не активируя при этом ни книгу, ни лист:
'запишем слово Test в ячейку A1 на Лист2 книги Книга2.xlsx независимо от того, какая книга и какой лист активен Workbooks("Книга2.xlsx").Worksheets("Лист2").Range("A1").Value = "Test" 'считываем значение ячейки A1 с Лист2 книги Книга3.xlsx независимо от того, какой лист активен MsgBox Workbooks("Книга3.xlsx").Worksheets("Лист2").Range("A1").Value
Очень часто ошибки обращения к ячейкам листов и книг делают начинающие, особенно в циклах по листам. Вот пример неправильного цикла:
Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox Range("A1").Value 'проверяем, то ли имя записалось Next wsSh
MsgBox будет выдавать правильные значения, но сами имена листов будут записываться не на каждый лист, а последовательно в ячейку активного листа. Поэтому на активном листе в ячейке А1 будет имя последнего листа.
А вот так выглядит правильный цикл:
Вариант 1 - активация листа (медленный)
Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Activate 'активируем каждый лист Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox Range("A1").Value 'проверяем, то ли имя записалось Next wsSh
Вариант 2 - без активации листа (быстрый и более правильный)
Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox wsSh.Range("A1").Value 'проверяем, то ли имя записалось Next wsSh
Важно: если код записан в модуле листа(правая кнопка мыши на листе-Исходный текст) и для объекта Range или Cells родитель явно не указан(т.е. нет имени листа и книги) - тогда в качестве родителя будет использован именно тот лист, в котором записан код, независимо от того какой лист активный. Иными словами - если в модуле листа записать обращение вроде Range("A1").Value = "привет" , то слово привет всегда будет записывать в ячейку A1 именно того листа, в котором записан сам код. Это следует учитывать, когда располагаете свои коды внутри модулей листов.
В конструкциях типа Range(Cells(,),Cells(,)) Range является контейнером, в котором указываются ссылки на объекты, из которых и будет создана ссылка на непосредственно конечный объект.
Предположим, что активен "Лист1" , а код запущен с листа "Итог" .
Если запись будет вида
Sheets("Итог").Range(Cells(1, 1), Cells(10, 1))
это вызовет ошибку "Run-time error '1004': Application-defined or object-defined error". А ошибка появляется потому, что контейнер и объекты внутри него не могут располагаться на разных листах, равно как и:
Sheets("Итог").Range(Cells(1, 1), Sheets("Итог").Cells(10, 1)) 'запись ниже так же неверна Range(Cells(1, 1), Sheets("Итог").Cells(10, 1))
т.к. ссылки на объекты внутри контейнера относятся к разным листам. Cells(1, 1) - к активному листу, а Sheets( "Итог" ).Cells(10, 1) - к листу Итог.
А вот такие записи будут правильными:
Sheets("Итог").Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1)) Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1))
Вторая запись не содержит ссылки на родителя для Range, но ошибки это в большинстве случаев не вызовет - т.к. если для контейнера ссылка не указана, а для двух объектов внутри контейнера родитель один - он будет применен и для самого контейнера. Однако лучше делать как в первой строке - т.е. с обязательным указанием родителя для контейнера и для его составляющих. Т.к. при определенных обстоятельствах(например, если в момент обращения к диапазону активной является книга, открытая в режиме защищенного просмотра) обращение к Range без родителя может вызывать ошибку выполнения.
Если запись будет вида Range( "A1" , "A10" ), то указывать ссылку на родителя внутри Range не обязательно - достаточно будет указать эту ссылку перед самим Range - Sheets( "Итог" ).Range( "A1" , "A10" ), т.к. текстовое представление адреса внутри Range не является объектом(у которого может быть какой-то родительский объект), что обязывает создать ссылку именно на родителя контейнера.
Разберем пример, приближенный к жизненной ситуации. Необходимо на лист Итог занести формулу вычитания, начиная с ячейки А2 и до последней заполненной. На момент записи активен Лист1. Очень часто начинающие записывают так:
Sheets("Итог").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"
Запись смешанная - и текстовое представление адреса ячейки( "A2:A" ) и ссылка на объект Cells. В данном случае явную ошибку код не вызовет, но и работать будет не всегда так, как хотелось бы. А это самое плохое, что может случиться при разработке.
Sheets("Итог").Range("A2:A" - создается ссылка на столбец " A " листа Итог. Но далее идет вычисление последней строки первого столбца. И вот как раз это вычисление происходит на основе объекта Cells, который не содержит в себе ссылки на родительский объект. А значит он будет вычислять последнюю строку исключительно для текущего листа(если код записан в стандартном модуле, а не модуле листа) - т.е. для Лист1. Правильно было бы записать так:
Sheets("Итог").Range("A2:A" & Sheets("Итог").Cells(Rows.Count, 1).End(xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"
Но и здесь неверное обращение с диапазоном может сыграть злую шутку. Например, надо получить последнюю заполненную ячейку в конкретной книге:
lLastRow = Workbooks("Книга3.xls").Sheets("Лист1").Cells(Rows.Count, 1).End(xlUp).Row
с виду все нормально, но есть нюанс. Rows.Count по умолчанию будет относится к активной книге, если записано в стандартном модуле. Приведенный выше код должен работать с книгой формата 97-2003 и вычислить последнюю заполненную ячейку на листе1. В книгах формата Excel 97-2003(.xls) всего 65536 строк. Если в момент выполнения приведенной строки активна книга формата 2007 и выше(форматы .xlsx, .xlsm, .xlsb и пр) - то Rows.Count вернет 1048576, т.к. именно такое количество строк в листах книг версий Excel, начиная с 2007. И т.к. в книге, в которой мы пытаемся вычислить последнюю строку всего 65536 строк - получим ошибку 1004, т.к. не может быть номера строки 1048576 на листе с количеством строк 65536. Поэтому имеет смысл указывать явно откуда считывать Rows.Count:
lLastRow = Workbooks("Книга3.xls").Sheets("Лист1").Cells(Workbooks("Книга3.xls").Sheets("Лист1").Rows.Count, 1).End(xlUp).Row
или применить конструкцию With
With Workbooks("Книга3.xls").Sheets("Лист1") lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row End With
Также не мешало бы упомянуть возможность выделения несмежного диапазона(часто его называют "рваным"). Это диапазон, который обычно привыкли выделять на листе при помощи зажатой клавиши Ctrl. Что это дает? Это дает возможность выделить одновременно ячейки A1 и B10 и записать значения только в них. Для этого есть несколько способов. Самый очевидный и описанный в справке - метод Union:
Union(Range("A1"), Range("B10")).Value = "Привет"
Однако существует и другой метод:
В чем отличие(я бы даже сказал преимущество) Union: можно применять в цикле по условию. Например, выделить в диапазоне A1:F50 только те ячейки, значение которых больше 10 и меньше 20:
Конечно, можно и просто в Range через запятую передать все эти ячейки, сформировав предварительно строку. Но в случае со строкой действует ограничение: длина строки не должна превышать 255 символов.
Надеюсь, что после прочтения данной статьи проблем с обращением к диапазонам и ячейкам у Вас будет гораздо меньше.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Доброго всем дня,коллеги! Подскажите чайнику простейшее.. Есть макрос,который вставляет в активную ячейку текущее время+текст. Необходимо,что бы текст возможно было вставить только в определенном диапазоне (н-р пару столбцов), т.к. при вставке "не туда" данные,записанные ранее удаляются,и откатиться назад уже нельзя. Буду благодарен за помощь! Девочек с наступающим.
Добрый день всем! В очередной раз за помощью! Простой макрос по кнопке вставляет дату + N дней. Но часто полученная дата попадает на выходной. Что нужно дописать,или изменить, что бы полученная дата "проскакивала" вперед на ближайший рабочий день?
Sub Через_10д()
If Not Intersect(ActiveCell, Range("J14:M350")) Is Nothing Then ActiveCell = Date + 10
End Sub
1. Что бы записать "привет" в ячейку А1 не надо объявлять переменную как диапазон и присваивать ей значение из ячейки D5. Самое начало статьи сразу с ошибки начинаете.
2. Диапазон - это диапазон, я не ячейка с номером типа Cell(х,y). Если уж начали писать про диапазоны - пишите про диапазоны! У меня задача - обратиться к именованному диапазону и вытащить оттуда через ВБА значения в массив для дальнейшей обработки. А вот теперь посмотрите что из написанного может помочь это сделать. Ничего.
Спасибо, бесполезная статья с намеком на гениальность и уводящая постоянно в сторону от озвученной темы
Артем,
1. Спасибо. Вообще в самом начале статьи написан другой код:
Range("A1").Value = "Привет"
Тоже самое можно сделать сразу для нескольких ячеек:
Range("A1:C10").Value = "Привет"
Просто не так давно сменил скрипт подсветки синтаксиса кода и он почему-то отобразил дважды один и тот же код из этой статьи, вместо других. В общем поправил.
2. Прежде чем критиковать, изучите хотя бы мат.часть. Ячейка - это тот же диапазон технически. И обращение Range("A1") равносильно обращению Cells(1,1). И свойства и методы у них идентичны. Только Range может содержать более одной ячейки, Cells нет.
Цель данной статьи - научить правильно обращаться к диапазонам и понимать отличие обращения к диапазону в активном листе или в других листах и книгах. И научить обращаться как через Range, так и через Cells. И никаких намеков на гениальность, просто описание технической части и её нюансов. Впрочем, об этом я тоже упоминаю:
Основной же целью этой статьи было пояснить некоторые нюансы обращения к диапазонам. Итак, поехали.
И в сторону статья уводит лишь от ВАШЕЙ проблемы, но не от темы. Ваша проблема - это работа не столько с диапазонами, сколько с массивами. Различайте понятия - вся статья про обращение к диапазонам, но описать в одной статье ответы на вопросы по диапазонам каждого проходящего нереально. Ведь само обращение к диапазону может использоваться в любой задаче: отобрать уникальные, загнать в массив, покрасить, подсчитать ячейки с тем-то и тем-то и т.д. и т.п. И все это будет подходить под определение диапазона - ведь мы к нему обращаемся :) Поэтому вменяемые люди пишут комментарии со своим вопросом, а не жалуются, что именно их вопрос здесь не озвучен.
А тем не менее Ваша задача решается элементарно и без всякого выпендрежа вроде "статья бесполезна". Надо было чуть пошерстить хотя бы этот же сайт - я приводил и такие примеры. Хотя бы в этой статье: Как ускорить и оптимизировать код VBA . Там есть советы по оптимизации для чуть более опытных, в том числе использование массивов, вместо прямого обращения к ячейкам.
И решается Ваша задача одной строкой кода:
Dim arr 'объявляем переменную, в которую помещать массив arr = Range("A1:F10").Value 'дальше делаем с массивом все, что надо
Есть еще один забавный способ адресоваться в VBA к ячейке.
[a2] = "Привет!" ' Занесет в ячейку А2 текущей книги текущего листа "Привет!"
Работает при способе адресации А1. При адресации RC будет работать такой способ:
[r1c2] = "Привет!"
Подскажите, пожалуйста, можно ли так же обращаться к динамическим диапазонам, нижняя граница которых записана через ИНДЕКС(ПОИСКПОЗ. У меня всякие ошибки вылезают при попытке обращения к такому диапазону.
Матвей, можно. Но не зная как и что Вы там задаете и какие лезут ошибки помочь нечем :( Попробуйте создать тему на форуме с приложенным файлом и подробным описанием проблемы.
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
В Microsoft Excel можно выбирать ячейки в диапазоне вручную или с помощью простого кода Microsoft Visual Basic для приложений. В этой статье приводится ряд примеров, которые можно использовать для выбора диапазона ячеек.
Дополнительная информация
Примеры выбора ячеек вручную
Чтобы вручную выбрать все данные в столбце, выберите первую ячейку и нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВНИЗ.
Точно так же, чтобы вручную выбрать строку и все вложенные в нее столбцы, нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВНИЗ+СТРЕЛКА ВПРАВО. Однако все данные должны быть неумными (т. е. пустые строки или столбцы не могут быть). Кроме того, вы можете выбрать текущую область данных (несколько строк или столбцов без пустых строк и столбцов).
В меню Правка выберите пункт Перейти.
В диалоговом окне Перейти нажмите кнопку Специальный.
В диалоговом окне Перейти к специальным выберите текущую область и нажмите кнопку
ОК.
Вы также можете выбрать этот диапазон с помощью простого Visual Basic для приложений кода.
Примечание. Если попытаться записать эту процедуру с помощью записи макроса, результаты будут не одинаковыми.
Примеры использования кода Visual Basic для выбора ячеек в диапазоне
Корпорация Майкрософт предоставляет примеры программирования только для иллюстрации без гарантии, выраженной или подразумеваемой. В частности, это подразумевает подразумеваемые гарантии пригодности для купли-связи или пригодности для определенной цели. В этой статье предполагается, что вы знакомы с языком программирования, который демонстрируется, и средствами, используемыми для создания и от debug procedures. Специалисты службы поддержки Майкрософт могут объяснить функциональные возможности конкретной процедуры, но не будут изменять эти примеры, чтобы предоставить дополнительные функции или процедуры по построению с учетом ваших конкретных требований. В примерах Visual Basic ниже покажите, как выбрать разные диапазоны.
Если вы знаете следующую ячейку (в данном примере — ячейка C1) и хотите выбрать ее до последней ячейки (в том же столбце), которая имеет данные, но не знает этот адрес, используйте следующий код:
Примечание. Макрос SelectRangeDown предполагает, что данные не должны быть соединимы. В противном случае, если в выбранном столбце данных есть пустые ячейки, этот макрос может не выбрать все ячейки в этом столбце.
Если данные начинаются с ячейки C1, но не являются неровными в этом столбце, используйте следующий макрос в Microsoft Office Excel 2003 и более ранних версиях Excel:
Так как Microsoft Office Excel 2007 поддерживает 1 048 576 строк, используйте следующий макрос в Excel 2007:
Если вы хотите выбрать активную ячейку вниз и все столбцы справа (предполагается, что данные есть во всех строках и столбцах), используйте следующий код:
Если вы знаете следующую следующую ячейку (в этом примере — ячейка D1) и хотите выбрать ее вниз и справа, используйте следующий код:
Чтобы выбрать все данные в текущем регионе, используйте следующий код:
В примерах, которые содержатся в этой статье, покажите, как выбрать различные диапазоны на активном компьютере текущей книги.
Чтобы получить дополнительные сведения о том, как выбрать диапазоны с помощью Visual Basic для приложений (и для более сложных примеров), щелкните номер следующей статьи, чтобы просмотреть статью в базе знаний Майкрософт:
У вас большой монитор, но таблицы, с которыми вы работаете - еще больше. И, пробегая взглядом по экрану в поисках нужной информации, всегда есть шанс "соскользнуть" взглядом на соседнюю строчку и посмотреть не туда. Я даже знаю людей, который для таких случаев постоянно держат недалеко от себя деревянную линейку, чтобы приложить ее к строке на мониторе. Технологии будущего!
А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:
Поудобнее, чем линейка, правда?
Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ - со своими плюсами и минусами. Давайте разберем их детально.
Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец
Самый очевидный путь для решения нашей проблемы "в лоб" - нам нужен макрос, который будет отслеживать изменение выделения на листе и выделять целую строку и столбец для текущей ячейки. Также желательно иметь возможность при необходимости включать и отключать эту функцию, чтобы такое крестообразное выделение не мешало нам вводить, например, формулы, а работало только тогда, когда мы просматриваем список в поисках нужной информации. Это приводит нас к трем макросам (выделения, включения и выключения), которые нужно будет добавить в модуль листа.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.
Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.
Плюсы этого способа:
- относительная простота реализации
- выделение - операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть
Минусы этого способа:
- такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки - выделяются сразу все строки и столбцы, входящие в объединение
- если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца
Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование
Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)
Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:
- кодовое слово для параметра, например "столбец" или "строка"
- адрес ячейки, для которой мы хотим определить значение этого параметра
Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.
Вторая составляющая этого способа - условное форматирование. Эта крайне полезная функция Excel позволяет автоматически форматировать ячейки, если они удовлетворяют заданным условиям. Если соединить эти две идеи в одно целое, то получим следующий алгоритм реализации нашего координатного выделения через условное форматирование:
- Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
- В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
- Вводим формулу для нашего координатного выделения:
Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:
Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.
Плюсы этого способа:
- Условное форматирование не нарушает пользовательское форматирование таблицы
- Этот вариант выделения корректно работает с объединенными ячейками.
- Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
- Макросы используются минимально
Минусы этого способа:
- Формулу для условного форматирования надо вводить вручную.
- Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.
Способ 3. Оптимальный. Условное форматирование + макросы
Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.
Способ 4. Красивый. Надстройка FollowCellPointer
Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:
Красивое решение. Не без глюков местами, но попробовать точно стоит. Качаем архив, распаковываем на диск и устанавливаем надстройку:
Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:
Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.
Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,
- скрывать подробности по месяцам, оставляя только кварталы
- скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
- скрывать ненужные в данный момент города (я работаю в Москве - зачем мне видеть Питер?) и т.д.
В реальной жизни примеров таких таблиц - море.
Способ 1. Скрытие строк и столбцов
Способ, прямо скажем, примитивный и не очень удобный, но два слова про него сказать можно. Любые выделенные предварительно строки или столбцы на листе можно скрыть, щелкнув по заголовку столбца или строки правой кнопкой мыши и выбрав в контекстном меню команду Скрыть (Hide) :
Для обратного отображения нужно выделить соседние строки/столбцы и, щелкнув правой кнопкой мыши, выбрать в меню, соответственно, Отобразить (Unhide) .
Проблема в том, что с каждым столбцом и строкой придется возиться персонально, что неудобно.
Способ 2. Группировка
Если выделить несколько строк или столбцов, а затем выбрать в меню Данные - Группа и структура - Группировать (Data - Group and Outline - Group) , то они будут охвачены прямоугольной скобкой (сгруппированы). Причем группы можно делать вложенными одна в другую (разрешается до 8 уровней вложенности):
Более удобный и быстрый способ - использовать для группировки выделенных предварительно строк или столбцов сочетание клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно.
Такой способ скрытия ненужных данных гораздо удобнее - можно нажимать либо на кнопку со знаком "+" или "-", либо на кнопки с цифровым обозначением уровня группировки в левом верхнем углу листа - тогда все группы нужного уровня будут сворачиваться или разворачиваться сразу.
Кроме того, если в вашей таблице присутствуют итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ый правда), что Excel сам создаст все нужные группировки в таблице одним движением - через меню Данные - Группа и структура - Создать структуру (Data - Group and Outline - Create Outline) . К сожалению, подобная функция работает весьма непредсказуемо и на сложных таблицах порой делает совершенную ерунду. Но попробовать можно.
В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline) :
Способ 3. Скрытие помеченных строк/столбцов макросом
Этот способ, пожалуй, можно назвать самым универсальным. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрывать:
Теперь откроем редактор Visual Basic (ALT+F11), вставим в нашу книгу новый пустой модуль (меню Insert - Module) и скопируем туда текст двух простых макросов:
Как легко догадаться, макрос Hide скрывает, а макрос Show - отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик - Вставить - Кнопка (Developer - Insert - Button) .
Способ 4. Скрытие строк/столбцов с заданным цветом
Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие "х" проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:
Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:
. и хотите их скрывать одним движением, то предыдущий макрос придется "допилить". Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:
Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.
Надстройка пригодится тем, кто часто работает с большими таблицами, просматривая их и сопоставляя данные в строках и столбцах. Что дает эта надстройка? Она выделяет столбец и строку таблицы на пересечении выделенной ячейки, благодаря чему можно просмотреть все данные в столбце и строке активной ячейки, не перепроверяя себя лишний раз – "А в том ли столбце я смотрю данные?".
Метод - выбор метода подсветки строки и столбца, их два:
- выделение - строка и столбец выделяются так же, как если бы вы выделяли ячейки мышкой. У этого метода есть один недостаток. Если на листе в этом режиме будут сделаны изменения(удаление значения ячейки), то отменить их будет невозможно
- условное форматирование - строка и столбец выделяются при помощи условного форматирования. Можно выбрать один из шести цветов для подсветки выделения строки и столбца. У такого подхода тоже есть недостаток. Все условное форматирование, примененное к текущему листу ранее, будет удалено
Весь лист - выделяются строка и столбец всего листа.
Вся таблица данных - выделяются строка и столбец только в пределах используемого диапазона данных.
Выбранный диапазон - выделяются строка и столбец только в пределах указанного вами диапазона. Особенность: не допускается выделение несвязанных диапазонов.
Область применения - выбирается область листа, к которой применяется координатное выделение:
- только строка - выделяется только строка активной ячейки
- только столбец - выделяется только столбец активной ячейки
- строка и столбец - выделяется строка и столбец на пересечении активной ячейки
Отменить Координатное выделение - отменяет примененное координатное выделение.
Внимание! Координатное выделение действует во всех открытых книгах во всех листах до тех пор, пока вы не отключите его через пункт «Отменить координатное выделение».
Важно! При примененном координатном выделении невозможно скопировать выделенный диапазон, однако можно удалить значение активной ячейки (будет удалено только значение одной ячейки, расположенной на пересечении строки и столбца).
ExcelVBA_KoordSel.zip (49,5 KiB, 5 253 скачиваний)
В архиве расположен файл надстройки. Прежде чем установить надстройку, необходимо её распаковать из архива на жесткий диск, после чего установить. Как установить надстройку: Установка надстроек
Надстройка распространяется бесплатно и с открытыми исходными кодами - смотрите, изучайте, меняйте под себя.
Самый важный момент для тех, кого не устроит цвет выделения по умолчанию: чтобы изменить цвет выделения ячеек через условное форматирование, необходимо перейти в модуль mKoordSelection, найти вверху строку
Public Const lKS_FC_Color As Long = 10921638
и заменить число 10921638 на числовой код нужного цвета заливки. Подобрать нужный цвет можно следующим образом:
- назначаем заливке любой ячейки нужный цвет
- выделяем эту ячейку и выполняем код:
Sub GetActiveCellColor() MsgBox ActiveCell.Interior.Color, vbInformation, sAPP_NAME End Sub
этот код так же есть внутри надстройки, поэтому его можно вызвать просто через Alt+F8
Изменить цвет выделения методом Обычного выделения нельзя. Это ограничение самого Excel. Можно изменить только изменением цветовых схем Windows.
Читайте также: