Признаюсь честно, я не большой специалист в Exel. Очень многие, даже простейшие процессы автоматизации в нем меня ставят в тупик. Поэтому данная статья мне была очень любопытна и полезна. Может быть и вы узнаете что то новое для себя. Итак читаем …
Сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе.О них расскажет Николай Павлов — автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓
Быстрое добавление новых данных в диаграмму
Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).
Мгновенное заполнение (Flash Fill)
Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.
Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.
Копирование без нарушения форматов
Вы, скорее всего, знаете про «волшебный» маркер автозаполнения — тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.
Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.
Отображение данных из таблицы Excel на карте
В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта, нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.
При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.
Быстрый переход к нужному листу
Если в вашей книге количество рабочих листов перевалило за 10, то ориентироваться в них становится трудновато. Щёлкните правой кнопкой мыши по любой из кнопок прокрутки ярлычков листов в левом нижнем углу экрана.
Появится оглавление, и на любой нужный лист можно будет перейти мгновенно.
Также можно создать на отдельном листе оглавление с гиперссылками. Это чуть сложнее, но зачастую удобнее.
Преобразование строк в столбцы и обратно
Если вам когда-нибудь приходилось руками перекладывать ячейки из строк в столбцы, то вы оцените следующий трюк:
В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).
Выпадающий список в ячейке
Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и т. д.), то это можно легко организовать при помощи выпадающего списка:
Продвинутые трюки из той же серии: выпадающий список с наполнением, связанные выпадающие списки, выпадающий список с фотографией и т. д.
«Умная» таблица
Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home — Format as Table), то наш список будет преобразован в «умную» таблицу, которая (кроме модной полосатой раскраски) умеет много полезного:
Спарклайны
Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.
После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.
Восстановление несохранённых файлов
Пятница. Вечер. Долгожданный конец ударной трудовой недели. Предвкушая отдых, вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет».
Опустевший офис оглашает ваш истошный вопль, но уже поздно — несколько последних часов работы пошли псу под хвост и вместо приятного вечера в компании друзей вам придётся восстанавливать утраченное.
На самом деле, есть неслабый шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» — «Последние» (File — Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks). В Excel 2013 путь немного другой: «Файл» — «Сведения» — «Управление версиями» — «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks). Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.
Сравнение двух диапазонов на отличия и совпадения
Весьма часто при работе в Excel возникает необходимость сравнить два списка и быстро найти элементы, которые в них совпадают или отличаются. Самый быстрый и наглядный способ сделать это:
Подбор (подгонка) результатов расчёта под нужные значения
Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!
Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.
Ну и еще некоторые более простые полезность про Exel:
Выпустив Excel 2010, Microsoft чуть ли не удвоила функциональность этой программы, добавив множество улучшений и нововведений, многие из которых не сразу заметны. Неважно, опытный вы пользователь или новичок, найдется немало способов упростить работу с Excel. О некоторых из них мы сегодня расскажем.
Выделение всех ячеек одним кликом
Все ячейки можно выделить комбинацией клавиш Ctrl + A, которая, кстати, работает и во всех других программах. Однако есть более простой способ выделения. Нажав на кнопку в углу листа Excel, вы выделите все ячейки одним кликом.
Открытие нескольких файлов одновременно
Вместо того чтобы открывать каждый файл Excel по отдельности, их можно открыть вместе. Для этого выделите файлы, которые нужно открыть, и нажмите Enter.
Перемещение по файлам Excel
Когда у вас открыто несколько книг в Excel, между ними можно легко перемещаться с помощью комбинации клавиш Ctrl + Tab. Эта функция также доступна по всей системе Windows, и ее можно использовать во многих приложениях. К примеру, для переключения вкладок в браузере.
Добавление новых кнопок на панель быстрого доступа
Стандартно в панели быстрого доступа Excel находятся 3 кнопки. Вы можете изменить это количество и добавить те, которые нужны именно вам.
Перейдите в меню «Файл» ⇒ «Параметры» ⇒ «Панель быстрого доступа». Теперь можно выбрать любые кнопки, которые вам нужны.
Диагональная линия в ячейках
Иногда бывают ситуации, когда нужно добавить в таблицу диагональную линию. К примеру, чтобы разделить дату и время. Для этого на главной странице Excel нажмите на привычную иконку границ и выберите «Другие границы».
Добавление в таблицу пустых строк или столбцов
Вставить одну строку или столбец достаточно просто. Но что делать, если их нужно вставить гораздо больше? Выделите нужное количество строк или столбцов и нажмите «Вставить». После этого выберите место, куда нужно сдвинуться ячейкам, и вы получите нужное количество пустых строк.
Скоростное копирование и перемещение информации
Если вам нужно переместить любую информацию (ячейку, строку, столбец) в Excel, выделите ее и наведите мышку на границу, чтобы изменился указатель. После этого переместите информацию в то место, которое вам нужно. Если необходимо скопировать информацию, сделайте то же самое, но с зажатой клавишей Ctrl.
Быстрое удаление пустых ячеек
Пустые ячейки — это бич Excel. Иногда они появляются просто из ниоткуда. Чтобы избавиться от них всех за один раз, выделите нужный столбец, перейдите на вкладку «Данные» и нажмите «Фильтр». Над каждым столбцом появится стрелка, направленная вниз. Нажав на нее, вы попадете в меню, которое поможет избавиться от пустых полей.
Расширенный поиск
Нажав Ctrl + F, мы попадаем в меню поиска, с помощью которого можно искать любые данные в Excel. Однако его функциональность можно расширить, используя символы «?» и «*». Знак вопроса отвечает за один неизвестный символ, а астериск — за несколько. Их стоит использовать, если вы не уверены, как выглядит искомый запрос.
Если же вам нужно найти вопросительный знак или астериск и вы не хотите, чтобы вместо них Excel искал неизвестный символ, то поставьте перед ними «
Копирование уникальных записей
Уникальные записи могут быть полезными, если вам нужно выделить в таблице неповторяющуюся информацию. К примеру, по одному человеку каждого возраста. Для этого выберите нужный столбец и нажмите «Дополнительно» слева от пункта «Фильтр». Выберите исходный диапазон (откуда копировать) и диапазон, в который нужно поместить результат. Не забудьте поставить галочку.
Создание выборки
Если вы делаете опрос, в котором могут участвовать только мужчины от 19 до 60, вы легко можете создать подобную выборку с помощью Excel. Перейдите в пункт меню «Данные» ⇒ «Проверка данных» и выберите необходимый диапазон или другое условие. Вводя информацию, которая не подходит под это условие, пользователи будут получать сообщение, что информация неверна.
Быстрая навигация с помощью Ctrl и стрелки
Нажимая Ctrl + стрелка, можно перемещаться в крайние точки листа. К примеру, Ctrl + ⇓ перенесет курсор в нижнюю часть листа.
Транспонирование информации из столбца в строку
Довольно полезная функция, которая нужна не так уж и часто. Но если она вдруг понадобится, вряд ли вы будете транспонировать по одной. Для транспонирования в Excel есть специальная вставка.
Скопируйте диапазон ячеек, который нужно транспонировать. После этого кликните правой кнопкой на нужное место и выберите специальную вставку.
Как скрывать информацию в Excel
Не знаю, зачем это может пригодиться, но тем не менее такая функция в Excel есть. Выделите нужный диапазон ячеек, нажмите «Формат» ⇒ «Скрыть или отобразить» и выберите нужное действие.
Объединение текста с помощью «&»
Если вам нужно объединить текст из нескольких ячеек в одну, необязательно использовать сложные формулы. Достаточно выбрать ячейку, в которой будет соединен текст, нажать «=» и последовательно выбрать ячейки, ставя перед каждой символ «&».
Изменение регистра букв
С помощью определенных формул можно менять регистр всей текстовой информации в Excel. Функция «ПРОПИСН» делает все буквы прописными, а «СТРОЧН» — строчными. «ПРОПНАЧ» делает прописной только первую букву в каждом слове.
Внесение информации с нулями в начале
Если вы введете в Excel число 000356, то программа автоматически превратит его в 356. Если вы хотите оставить нули в начале, поставьте перед числом апостроф «’».
Ускорение ввода сложных слов
Если вы часто вводите одни и те же слова, то обрадуетесь, узнав, что в Excel есть автозамена. Она очень похожа на автозамену в смартфонах, поэтому вы сразу поймете, как ей пользоваться. С ее помощью можно заменить повторяющиеся конструкции аббревиатурами. К примеру, Екатерина Петрова — ЕП.
Больше информации
В правом нижнем углу можно следить за различной информацией. Однако мало кто знает, что, нажав туда правой кнопкой мыши, можно убрать ненужные и добавить нужные строки.
Переименование листа с помощью двойного клика
Это самый простой способ переименовать лист. Просто кликните по нему два раза левой кнопкой мыши и введите новое название.
Объект Excel.Worksheet, программное создание, обнаружение, удаление листа Excel средствами VBA, свойства, методы и события объекта Excel.Worksheet
В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.
Чаще всего для ввода данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать.
Процесс создания выглядит очень просто:
Dim oExcel As New Excel.Application ‘Запускаем Excel
oExcel.Visible = True ‘Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add () ‘Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Add() ‘ Создаем новый лист
oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»
Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.
Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем «Лист1» и переименовываем его в «Новый лист»:
Dim oExcel As New Excel.Application ‘Запускаем Excel
oExcel.Visible = True ‘Делаем его видимым
Dim oWbk As Excel.Workbook
Set oWbk = oExcel.Workbooks.Add() ‘Создаем новую книгу
Dim oSheet As Excel.Worksheet
Set oSheet = oWbk.Worksheets.Item(«Лист 1») ‘ Находим Лист1
oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»
Обратите внимание, что в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются «Sheet1», «Sheet2» и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.
У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.
У объекта Worksheet — множество важных свойств и методов:
Некоторые важные методы объекта Worksheet:
Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.
У объекта Worksheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word). Это — события BeforeRightClick() и BeforeDoubleClick(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.
Объекты Excel
Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.
Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.
В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.
Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.
В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).
Из объекта Workbook можно получить доступ к объекту Sheets, который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets, который представляет из себя коллекцию всех рабочих листов книги Excel.
Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns, которые являются коллекцией объектов Range, ссылающихся на строки и столбцы рабочего листа. А также можно получить доступ к отдельной ячейке или к любому диапазону смежных ячеек на рабочем листе.
Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1).
По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(“A1:B10”) или Worksheet.Range(“A1”, “B10”) или Worksheet.Range(Cells(1,1), Cells(10,2)).
Обратите внимание, если в адресе Range вторая ячейка не указана (например, Worksheet.Range(“A1”) или Worksheet.Range(Cells(1,1)), то будет выбран диапазон, состоящий из единственной ячейки.
Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:
Присваивание объекта переменной
В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:
Активный объект
В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.
Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.
Если в коде VBA записана ссылка на рабочий лист, без указания к какой именно рабочей книге он относится, то Excel по умолчанию обращается к активной рабочей книге. Точно так же, если сослаться на диапазон, не указывая определённую рабочую книгу или лист, то Excel по умолчанию обратится к активному рабочему листу в активной рабочей книге.
Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:
Смена активного объекта
Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:
Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.
Свойства объектов
Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:
Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:
Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.
Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на “Мой рабочий лист“, достаточно присвоить это имя свойству Name активного листа, вот так:
Методы объектов
Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.
Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:
Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.
Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2, а аргументу FileFormat – значение xlCSV:
Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:=” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:
В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.
Рассмотрим несколько примеров
Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.
В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.
В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.
Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.
Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.
Свойства и методы Worksheet
Мечтатель начинает с чистого листа бумаги и переосмысливает мир
Эта статья содержит полное руководство по использованию Excel VBA Worksheet в Excel VBA. Если вы хотите узнать, как что-то сделать быстро, ознакомьтесь с кратким руководством к рабочему листу VBA ниже.
Если вы новичок в VBA, то эта статья — отличное место для начала. Мне нравится разбивать вещи на простые термины и объяснять их на простом языке.
Вы можете прочитать статью от начала до конца, так как она написана в логическом порядке. Или, если предпочитаете, вы можете использовать оглавление ниже и перейти непосредственно к теме по вашему выбору.
Краткое руководство к рабочему листу VBA
Вступление
Три наиболее важных элемента VBA — это Рабочая книга, Рабочий лист и Ячейки. Из всего кода, который вы пишете, 90% будут включать один или все из них.
Наиболее распространенное использование Worksheet в VBA для доступа к его ячейкам. Вы можете использовать его для защиты, скрытия, добавления, перемещения или копирования листа.
Тем не менее, вы будете в основном использовать его для выполнения некоторых действий с одной или несколькими ячейками на листе.
Использование Worksheets более простое, чем использование рабочих книг. С книгами вам может потребоваться открыть их, найти, в какой папке они находятся, проверить, используются ли они, и так далее. С рабочим листом он либо существует в рабочей книге, либо его нет.
Доступ к рабочему листу
В VBA каждая рабочая книга имеет коллекцию рабочих листов. В этой коллекции есть запись для каждого рабочего листа. Эта коллекция называется просто Worksheets и используется очень похоже на коллекцию Workbooks. Чтобы получить доступ к рабочему листу, достаточно указать имя.
Приведенный ниже код записывает «Привет Мир» в ячейках A1 на листах: Лист1, Лист2 и Лист3 текущей рабочей книги.
Коллекция Worksheets всегда принадлежит книге. Если мы не указываем рабочую книгу, то активная рабочая книга используется по умолчанию.
Скрыть рабочий лист
В следующих примерах показано, как скрыть и показать лист.
Если вы хотите запретить пользователю доступ к рабочему листу, вы можете сделать его «очень скрытым». Это означает, что это может быть сделано видимым только кодом.
Защитить рабочий лист
Другой пример использования Worksheet — когда вы хотите защитить его.
Индекс вне диапазона
При использовании Worksheets вы можете получить сообщение об ошибке:
Run-time Error 9 Subscript out of Range
Это означает, что вы пытались получить доступ к рабочему листу, который не существует. Это может произойти по следующим причинам:
Если у вас остались проблемы, используйте один из циклов из раздела «Циклы по рабочим листам», чтобы напечатать имена всех рабочих листов коллекции.
Использование индекса для доступа к рабочему листу
До сих пор мы использовали имя листа для доступа к листу. Указатель относится к положению вкладки листа в рабочей книге. Поскольку положение может быть легко изменено пользователем, не рекомендуется использовать это.
В следующем коде показаны примеры использования индекса.
Использование кодового имени рабочего листа
Лучший способ получить доступ к рабочему листу — использовать кодовое имя. Каждый лист имеет имя листа и кодовое имя. Имя листа — это имя, которое отображается на вкладке листа в Excel.
Изменение имени листа не приводит к изменению кодового имени, что означает, что ссылка на лист по кодовому имени — отличная идея.
Если вы посмотрите в окне свойств VBE, вы увидите оба имени. На рисунке вы можете видеть, что кодовое имя — это имя вне скобок, а имя листа — в скобках.
Вы можете изменить как имя листа, так и кодовое имя в окне свойств листа (см. Изображение ниже).
Если ваш код ссылается на кодовое имя, то пользователь может изменить имя листа, и это не повлияет на ваш код. В приведенном ниже примере мы ссылаемся на рабочий лист напрямую, используя кодовое имя.
Это делает код легким для чтения и безопасным от изменения пользователем имени листа.
Кодовое имя в других книгах
Есть один недостаток использования кодового имени. Он относится только к рабочим листам в рабочей книге, которая содержит код, т.е. ThisWorkbook.
Однако мы можем использовать простую функцию, чтобы найти кодовое имя листа в другой книге.
Использование приведенного выше кода означает, что если пользователь изменит имя рабочего листа, то на ваш код это не повлияет.
Существует другой способ получения имени листа внешней рабочей книги с использованием кодового имени. Вы можете использовать элемент VBProject этой Рабочей книги.
Вы можете увидеть, как это сделать, в примере ниже. Я включил это, как дополнительную информацию, я бы рекомендовал использовать метод из предыдущего примера, а не этот.
Резюме кодового имени
Ниже приведено краткое описание использования кодового имени:
Активный лист
Объект ActiveSheet ссылается на рабочий лист, который в данный момент активен. Вы должны использовать ActiveSheet только в том случае, если у вас есть особая необходимость ссылаться на активный лист.
В противном случае вы должны указать рабочий лист, который вы используете.
Если вы используете метод листа, такой как Range, и не упоминаете лист, он по умолчанию будет использовать активный лист.
Объявление объекта листа
Объявление объекта листа полезно для того, чтобы сделать ваш код более понятным и легким для чтения.
В следующем примере показан код для обновления диапазонов ячеек. Первый Sub не объявляет объект листа. Вторая подпрограмма объявляет объект листа, и поэтому код намного понятнее.
Вы также можете использовать ключевое слово With с объектом листа, как показано в следующем примере.
Доступ к рабочему листу в двух словах
Из-за множества различных способов доступа к рабочему листу вы можете быть сбитыми с толку. Так что в этом разделе я собираюсь разбить его на простые термины.
2. Если лист находится в той же книге, что и код, используйте кодовое имя.
3. Если рабочая таблица находится в другой рабочей книге, сначала получите рабочую книгу, а затем получите рабочую таблицу.
Если вы хотите защитить пользователя от изменения имени листа, используйте функцию SheetFromCodeName из раздела «Имя кода».
Добавить рабочий лист
Примеры в этом разделе показывают, как добавить новую рабочую таблицу в рабочую книгу. Если вы не предоставите никаких аргументов для функции Add, то новый рабочий лист будет помещен перед активным рабочим листом.
Когда вы добавляете рабочий лист, он создается с именем по умолчанию, например «Лист4». Если вы хотите изменить имя, вы можете легко сделать это, используя свойство Name.
В следующем примере добавляется новый рабочий лист и изменяется имя на «Счета». Если лист с именем «Счета» уже существует, вы получите сообщение об ошибке.
В предыдущем примере вы добавляете листы по отношению к активному листу. Вы также можете указать точную позицию для размещения листа.
Для этого вам нужно указать, какой лист новый лист должен быть вставлен до или после. Следующий код показывает вам, как это сделать.
Удалить рабочий лист
Чтобы удалить лист, просто вызовите Delete.
Excel отобразит предупреждающее сообщение при удалении листа. Если вы хотите скрыть это сообщение, вы можете использовать код ниже:
Есть два аспекта, которые нужно учитывать при удалении таблиц.
Если вы попытаетесь получить доступ к рабочему листу после его удаления, вы получите ошибку «Subscript out of Range», которую мы видели в разделе «Доступ к рабочему листу».
Вторая проблема — когда вы назначаете переменную листа. Если вы попытаетесь использовать эту переменную после удаления листа, вы получите ошибку автоматизации, подобную этой:
Если вы используете кодовое имя рабочего листа, а не переменную, это приведет к сбою Excel, а не к ошибке автоматизации.
В следующем примере показано, как происходят ошибки автоматизации.
Если вы назначите переменную Worksheet действительному рабочему листу, он будет работать нормально.
Цикл по рабочим листам
Элемент «Worksheets» — это набор рабочих листов, принадлежащих рабочей книге. Вы можете просмотреть каждый лист в коллекции рабочих листов, используя циклы «For Each» или «For».
В следующем примере используется цикл For Each.
В следующем примере используется стандартный цикл For.
Вы видели, как получить доступ ко всем открытым рабочим книгам и как получить доступ ко всем рабочим листам в ThisWorkbook. Давайте сделаем еще один шаг вперед — узнаем, как получить доступ ко всем рабочим листам во всех открытых рабочих книгах.
Примечание. Если вы используете код, подобный этому, для записи на листы, то сначала сделайте резервную копию всего, так как в итоге вы можете записать неверные данные на все листы.
Использование коллекции листов
Рабочая книга имеет еще одну коллекцию, похожую на Worksheets под названием Sheets. Это иногда путает пользователей. Чтобы понять, в первую очередь, вам нужно знать о типе листа, который является диаграммой.
В Excel есть возможность создать лист, который является диаграммой. Для этого нужно:
Теперь у вас есть рабочая книга, в которой есть типовые листы и лист-диаграмма.
Ниже приведены два примера кода. Первый проходит через все листы в рабочей книге и печатает название листа и тип листа. Второй пример делает то же самое с коллекцией Worksheets.
Чтобы опробовать эти примеры, вы должны сначала добавить лист-диаграмму в свою книгу, чтобы увидеть разницу.
Если у вас нет листов диаграмм, то использование коллекции Sheets — то же самое, что использование коллекции WorkSheets.
Заключение
На этом мы завершаем статью о Worksheet VBA. Я надеюсь, что было полезным.
Три наиболее важных элемента Excel VBA — это рабочие книги, рабочие таблицы, диапазоны и ячейки.
Эти элементы будут использоваться практически во всем, что вы делаете. Понимание их сделает вашу жизнь намного проще и сделает изучение VBA увлекательнее.
Microsoft Excel
трюки • приёмы • решения
Объекты Microsoft Excel, которые позволяют работать с информацией
В большинстве примеров вводной части раздела «Офисные решения» мы рассматривали возможности VBA без связи с информацией, находящейся на рабочем листе. Лишь несколько ситуаций продемонстрировали синтаксические конструкции, которые позволяли извлекать и записывать данные в ячейки листов Microsoft Excel. В этой части раздела мы детально рассмотрим объекты, которые позволяют работать с информацией, содержащейся в рабочих книгах Microsoft Excel. Приводимые здесь примеры, являются фундаментом для более сложных разработок, рассматриваемых в последующих статьях.
Файл рабочей книги Excel представлен в объекте Workbook, который имеет большое количество свойств и методов. Справочная информация по ним присутствует как в электронной справке по VBA, так и в большом количестве изданий по данной теме. Мы не будем углубляться в чисто справочную информацию и во вводной части рассмотрим только те сведения, с которыми далее встретимся в приведенных примерах.
Так, свойство Worksheets объекта Workbook представляет семейство всех рабочих листов книги. И для обращения к конкретному листу книги с помощью этого свойства следует просто указать в качестве параметра номер листа, что выглядит так: Worksheets(номер листа). В качестве номера листа выступает просто его порядковый номер в книге Microsoft Excel. Другой вариант заключается в указании в качестве параметра названия листа: Worksheets («Название листа»). Одним из наиболее часто программируемых событий, связанных с книгой в целом, является событие Open, которое происходит при открытии рабочей книги. Так, если мы хотим, чтобы при открытии книги выполнялись определенные действия, следует расположить необходимый программный фрагмент внутри процедуры Workbook_Open. Программирование этого события будет рассматриваться в части приводимых далее примеров.
Следующим объектом в порядке иерархии после Workbook является объект Worksheet, представляющий рабочий лист. Из многообразия методов этого объекта широко используется Activate, который существует и для семейства листов Worksheets, о котором мы уже сказали выше. Например, если при работе на первом листе книги Microsoft Excel требуется активизировать второй лист, то синтаксис программной строки в процедуре (выполняться она может, например, по щелчку на кнопке) должен выглядеть следующим образом: Worksheets(2).Activate. Если же требуется активизировать лист, называемый Информация о фирмах, то следует написать в процедуре такую конструкцию: Worksheets(«Информация о фирмах»).Activate.
Пользователи Microsoft Excel знают, что это приложение предлагает сервис, связанный с защитой рабочих книг и составляющих их листов. Так, метод Protect (семейства Worksheets) обеспечивает защиту рабочего листа от внесения в него изменений. Для программной установки защиты с паролем (пароль указывается в параметре Password этого метода) листа с названием Сотрудники можно поступить следующим образом:
Здесь и далее символ «нижнего подчеркивания» является информацией для Microsoft Visual Basic о том, что следующая строка является продолжением текущей.
Каждая рабочая книга состоит из листов. Совокупность всех листов рабочей книги задается коллекцией Sheets. Основных типов листов в рабочей книге два – Worksheet и Chart, соответственно имеются две коллекции для каждого типа листов. На практике для этих коллекций чаще всего используется метод Add, добавляющий новый лист в книгу. Метод возвращает соответствующий объект в качестве результата. Добавленный лист становится активным. Свойство Count у всех коллекций показывает количество элементов 15 коллекции. Для Sheets оно равно количеству листов. Так, например, можно добавить в активную книгу новый лист перед последним листом, назвав его «Новый»:
Set NewSheet = Sheets.Add(before:=Sheets(Sheets.Count))
Ссылка на добавленный лист сохранена в переменной NewSheet класса Worksheet, которую далее в программе можно использовать для работы с этим листом.
Основные свойства и методы объекта Worksheet приведены в табл. 12.25 и 12.26.
Основные события объекта Worksheet следующие: Activate, Calculate, Change, Deactivate, SelectionChange.
Таблица 12.25
Основные свойства объекта Worksheet
Возвращает объект Range. Позволяет задать как отдельную ячейку, так и диапазоны ячеек. Свойство имеет параметры. Чаще всего в качестве значений параметра используются адрес ячейки, диапазон ячеек или выражение над диапазонами, содержащее операции объединения (запятая) и пересечения (пробел), например:
Возвращает объект Range. Обычно свойство используется для указания конкретной ячейки с помощью ее номера строки и столбца. Например: ActiveSheet.Cells(5, 1) – это ячейка А5 активного листа
Возвращают коллекции, содержащие все строки и столбцы рабочего листа. Для доступа к конкретной строке или столбцу нужно указать его номер:
Таблица 12.26
Основные методы объекта Worksheet
Активизирует рабочий лист
Удаляет рабочий лист
Создает копию листа, помещая ее перед или после указанного в параметре (Before, After) листа:
Перемещает рабочий лист в позицию, указанную параметром Before или After
Помещает содержимое буфера обмена на рабочий лист. Позицию вставки можно указать с помощью параметра Destination
Выполняет вычисления формул рабочего листа
Объект Range
Это основной объект, с которым приходится работать программисту. Объект является коллекцией ячеек электронной таблицы, которая может содержать как единственную ячейку таблицы, так и столбец или строку, некоторую связную и не связную прямоугольную область, а также объединение и пересечение всех подобных элементов. Для создания объекта чаще всего используются свойства Range, Cells, Rows, Columns, рассмотренные выше. Основные свойства объекта приведены в табл. 12.27.
Таблица 12.27
Основные свойства объекта Range
Значение ячейки. Если она пуста, то возвращается значение Empty (можно проверить, вызвав функцию IsEmpty). Если объект Range содержит более одной ячейки, то возвращается массив значений (можно проверить, вызвав функцию IsArray). Функции IsNumber,LsText позволяют определить тип значения, хранимого в ячейке. Это свойство по умолчанию, поэтому часто опускается. Например, вместо Range(«Al»). Value = 2 можно написать Rangc(«Al”) = 2
Возвращает строку текста, связанного с ячейкой. Имеет статус только для чтения
Формула в формате А1. Если формула присваивается диапазону, то относительные ссылки ведут себя как обычно. Например:
Формула в формате А1 с учетом языка пользователя (для неанглоязычных версий Excel). Например:
Формула в формате R1С1. Например:
Формула в формате R1C1 с учетом языка пользователя (для неанглоязычных версий Excel)
Возвращает объект Range, в состав которого входит заданный диапазон. Границами возвращаемого объекта являются пустые строки и столбцы или границы таблицы. Например, так можно выделить диапазон, содержащий ячейку A2:
Возвращает строку, задающую ссылку на объект Range. Вид возвращаемого значения определяют параметры свойства
Возвращают соответственно номер первого столбца или первой строки объекта Range
Возвращает объект Font (шрифт). Например:
With Worksheets(nHncTl «).Range(”B5»).Font
Если при записи макроса вы будете вставлять в ячейку какую-либо формулу, то Excel в тексте, скорее всего, использует свойство FormulaRlCl и вставленная формула покажется вам непонятной. Дело в том, что для ссылок на ячейки в Excel используются два стиля: А1 и R1C1. Обычно вы работаете со ссылками в стиле А1: ссылка состоит из имени столбца (обозначаются латинскими буквами) и номера строки. Признаком абсолютной ссылки является знак доллара перед именем строки или столбца. Макросы используют стиль R1C1. В этом случае после буквы R указывается номер строки ячейки, после буквы С – номер столбца. Для задания относительной ссылки в стиле R1C1 указывается смещение по отношению к активной ячейке (в квадратных скобках). Знак определяет направление смещения. Например, R[-1]C (относительная ссылка на ячейку, расположенную в предыдущей строке и в том же столбце), R[3]C[2] (относительная ссылка на ячейку, расположенную на три строки ниже и на два столбца правее), R2C2 (абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце), R[–1 ] (относительная ссылка на предыдущую строку), R (абсолютная ссылка на текущую строку).
Объект Range имеет около 80 методов. Все, что вы можете сделать с диапазоном ячеек в Excel, скорее всего реализовано с помощью метода объекта Range. Например, сортировка – это метод Sort, заполнение диапазона – метод AutoFill, выделение диапазона – метод Select и т.д. Большинство методов имеют параметры. Для изучения методов рекомендуется записать макрос, выполняющий нужную команду, и проанализировать полученный код. Описание методов можно найти в справочной системе MS Excel. Воспользуйтесь контекстной справкой (клавиша F1) или в редакторе Visual Basic откройте окно справки, в строке поиска наберите «Range Object Members» и перейдите по найденной гиперссылке.
Объект Selection
В MS Excel нет класса объектов Selection. В программе объект Selection мы получаем либо в результате работы метода Select, либо при вызове свойства Selection. Тип объекта может быть различным. Свойства и методы объекта Selection определяются типом выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и тогда при работе с ним можно использовать все свойства и методы объектов класса Range.
masterok
masterok
