Web-studio46.ru

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

Язык программирования в excel

5 полезных функций Excel для начинающих программистов

Рабо­ту в таб­лич­ном редак­то­ре Excel тоже мож­но авто­ма­ти­зи­ро­вать и запро­грам­ми­ро­вать. Вы смо­же­те здо­ро­во помочь сво­им бух­гал­те­рам с отчё­та­ми, мар­ке­то­ло­гам — с ана­ли­зом рекла­мы, а сами научи­тесь раз­би­рать­ся в алго­рит­мах.

Мы раз­бе­рём 5 полез­ных функ­ций Excel. Если вы дума­е­те, что Excel — это толь­ко про циф­ры и таб­лич­ки, то вот вам видео, где в Excel запро­грам­ми­ро­ва­ли насто­я­щую 3D-игру.

Что­бы было про­ще разо­брать­ся, все фор­му­лы и при­ме­ры мы запи­са­ли в Excel-файл, кото­рый мож­но ска­чать и исполь­зо­вать для экс­пе­ри­мен­тов. Каж­дый при­мер — на отдель­ной вклад­ке сни­зу.

Это одна из самых важ­ных функ­ций в Excel. Как и в насто­я­щем язы­ке про­грам­ми­ро­ва­ния, она про­ве­ря­ет какое-то усло­вие, и если оно выпол­ня­ет­ся — пишет в ячей­ку что-то одно, а если нет — пишет что-то дру­гое.

В общем виде она выгля­дит так:

В усло­вии может быть что угод­но: срав­не­ние яче­ек, дру­гие фор­му­лы, срав­не­ния и мате­ма­ти­че­ские коман­ды — всё, что вам нуж­но про­ве­рить.

На прак­ти­ке мож­но сде­лать, напри­мер, так: пусть Excel про­ве­ря­ет воз­раст, и пишет доку­мент, кото­рый в этом воз­расте удо­сто­ве­ря­ет лич­ность. До 14 лет это сви­де­тель­ство о рож­де­нии, а после — пас­порт. Для это­го исполь­зу­ем такую коман­ду в ячей­ке:

Про­грам­ма про­ве­рит, что сто­ит в преды­ду­щей ячей­ке, и если там чис­ло мень­ше 14, то напи­шет «Сви­де­тель­ство о рож­де­нии», а если уже есть 14 лет, то «Пас­порт»:

СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Часто быва­ет нуж­но посчи­тать коли­че­ство строк или запол­нен­ных яче­ек по каким-то пара­мет­рам, напри­мер узнать коли­че­ство работ­ни­ков стар­ше 40 лет, или посчи­тать все стро­ки, где есть сло­во «пере­мен­ная». Для это­го исполь­зу­ют функ­цию СЧЁТЕСЛИ:

Диа­па­зон — это груп­па яче­ек, в кото­рых мы будем что-то искать, а усло­вие — это по како­му пара­мет­ру мы решим, что ячей­ка нам под­хо­дит.

Допу­стим мы ведём семей­ный бюд­жет и нам нуж­но узнать, сколь­ко раз у нас были покуп­ки боль­ше 5000 руб­лей в одном чеке. Для это­го исполь­зу­ем такое:

Теперь послож­нее. Если нам нуж­но посчи­тать коли­че­ство яче­ек по несколь­ким усло­ви­ям одно­вре­мен­но, то исполь­зу­ют функ­цию СЧЁТЕСЛИМН:

То, что в пря­мо­уголь­ных скоб­ках — допол­ни­тель­ные усло­вия, их может быть мак­си­мум 127, а сами скоб­ки при этом ста­вить не нуж­но. Обра­ти­те вни­ма­ние что для каж­до­го ново­го усло­вия мож­но задать свой диа­па­зон. Функ­ция про­ве­рит все усло­вия, каж­дое в сво­ём диа­па­зоне, и если все усло­вия одно­вре­мен­но выпол­ня­ет­ся — посчи­та­ет коли­че­ство таких яче­ек.

Для при­ме­ра най­дём все тра­ты боль­ше 5000 в одном чеке, за кото­рые отве­ча­ет Миша:

Теперь у нас пер­вый диа­па­зон отве­ча­ет за сум­му в чеке, а вто­рой — за име­на, поэто­му и усло­вия у каж­до­го из них раз­ные.

Функ­ция воз­вра­ща­ет мак­си­маль­ное зна­че­ние из выбран­но­го диа­па­зо­на:

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

ПОИСКПОЗ

Поиск пози­ции часто исполь­зу­ют, когда нуж­но най­ти поло­же­ние опре­де­лён­но­го эле­мен­та в таб­ли­це:

Зна­че­ние — то, что нам нуж­но най­ти в таб­ли­це, диа­па­зон про­смот­ра — где имен­но мы будем искать наше зна­че­ние. Если в каче­стве диа­па­зо­на ука­жем стол­бец с циф­ра­ми, то функ­ция вер­нёт номер в этом столб­це, где встре­ча­ет­ся нуж­ная циф­ра.

Давай­те най­дём номер стро­ки с мак­си­маль­ной покуп­кой:

Это чис­ло нам пона­до­бит­ся для сле­ду­ю­щей функ­ции

ИНДЕКС

Эта функ­ция воз­вра­ща­ет зна­че­ние кон­крет­ной ячей­ки в ука­зан­ном диа­па­зоне:

=ИНДЕКС(диапазон; номер_строки; [номер_столбца])

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

Попро­бу­ем в нашем при­ме­ре с бюд­же­том най­ти того, кто сде­лал самую боль­шую покуп­ку. Мы уже зна­ем номер стро­ки, в кото­рой запи­са­на самая боль­шая сум­ма, и на этой же стро­ке запи­сан тот, кто эту сум­му потра­тил. Исполь­зу­ем это:

Читать еще:  Что программируют на python

Когда Excel встре­тит такую фор­му­лу, он возь­мёт наш диа­па­зон из вто­ро­го столб­ца, затем возь­мёт номер стро­ки с самой боль­шой покуп­кой (она у нас в ячей­ке Е10, нашли в про­шлом при­ме­ре), и опре­де­лит, что самую круп­ную покуп­ку сде­лал Миша.

Что дальше

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

VBA Excel: примеры программ. Макросы в Excel

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

Что такое VBA

Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

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

Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.

Объекты, коллекции, свойства и методы

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

Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

Что касается понятия «коллекция», то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в «Эксель» используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами (1,1) т.е. A1.

Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.

Как начать

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

Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:

  • в строке меню, расположенном в верхней части окна, нажимают на иконку рядом с иконкой Excel;
  • выбирают команду Mudule;
  • сохраняют, нажав на иконку с изображением floppy disk;
  • пишут, скажем так, набросок кода.

Он выглядит следующим образом:

Обратите внимание, что строка «’Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.

Теперь вы можете написать любой код и создать для себя новый инструмент в VBA Excel (примеры программ см. далее). Конечно, тем, кто знаком с азами Visual Basic, будет намного проще. Однако даже те, кто их не имеет, при желании смогут освоиться достаточно быстро.

Читать еще:  Программирование для начинающих с нуля

Макросы в Excel

За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.

Пример 1

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

  • открывают вкладку «Вид»;
  • переходят на пиктограмму «Макросы»;
  • жмут на «Запись макроса»;
  • заполняют открывшуюся форму.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

Теперь, когда уже запущена запись макроса, производят копирование содержимого какой-либо ячейки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это действие означает завершение программки.

  • вновь переходят на строку «Макросы»;
  • в списке выбирают «Макрос 1»;
  • нажимают «Выполнить» (то же действие запускается начатием сочетания клавиш «Ctrl+hh»).

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

Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.

Пример 2

Циклы VBA помогают создавать различные макросы в Excel.

Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x 2 + 3x 3 – cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.

За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.

Все примеры макросов VBA Excel создаются по той же процедуре, которая представлена выше. В данном конкретном случае код выглядит, как:

Do While x1 0 Then Cells(1, 1).Value = 1

VBA-Урок 1. Что такое VBA. Основные понятия.

В данном разделе мы рассмотрим что такое VBA, посколько именно на нем и пишутся макросы.

Что такое VBA?

VBA — язык (расшифровывается как Visual Basic for Application) был разработан компанией Microsoft. Данный язык не является самостоятельным, а предназначен для автоматизации процессов в пакете MS Office. VBA широко используется в Excel, а также в Access, Word и других программах пакета.

VBA — простой язык программирования, которому может научиться любой желающий. Изучив его, вы сможет предоставлять команды Excel, что делать с колонками, строками, значениями в ячейках, перемещать/добавлять/сортировать листы, выводить заранее запрограммированные сообщения, писать свои формулы и функции и т.д. Суть языка заключается в оперировании объектами (что относит его к объектно-ориентированному программированию).

Чтобы работать с VBA кодом, нам нужен редактор, который уже установлен по умолчанию. Вы можете открыть его, нажав комбинацию клавиш » ALT + F11 «.

Объекты (Objects)

Давайте разберем, что же такое объект. Объект — это элемент, структурная частица Excel, а именно: книга, лист, диапазон, ячейка. Данные объекты имеют иерархию, т.е. подчиняются друг другу. Схематично структуру иерархии Excel можно изобразить следующим образом:

Читать еще:  Изучение языка программирования c

Главный объект это Application, что соответствует самой программе Excel. Далее следует Workbooks (книга), Worksheets (лист), Range (диапазон, или отдельная ячейка). Например, чтобы обратиться к ячейке «A1» на листе нам нужно будет прописать следующий путь с учетом иерархии:

Application.Workbooks(«Архив»).Worksheets(«Аркуш1»).Range(«A1»).

Таким образом, мы научились обращаться до наименьшего объекта в Excel — ячейки.

Коллекции (Collections)

В свою очередь объекты имеют «коллекции». Коллекция — это группа объектов одинакового класса. Отдельные элеметы коллекции являются также объектами. Так, объекты Worksheets являются элементами коллекции объекта Worksheet, который содержит также и другие коллекции и объекты:

  • ChartObjects (элемент коллекции объекта ChartObject)
  • Range
  • PageSetup
  • PivotTables (элемент коллекции объекта PivotTable).

Свойства (Properties)

Каждый объект имеет свойства . Например, объект Range имеет свойство Value или Formula.

Worksheets(“Sheet1”).Range(“A1”).Value або Worksheets(“Sheet1”).Range(“A1”).Formula

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

Также, через свойство Formula можно не только получить формулу, но и записать ее:

MsgBox Range(“A1”).Formula — получим сообщение с формулой в ячейке «А1«;

Range(“B12”).Formula = “=2+6*100” — вписываем формулу =2+6*100 в ячейку B12.

Методи (Methods)

Теперь давайте рассмотрим, каким образом мы можем управлять содержимым диапазона или ячейки. Для этого в VBA существуют, так-называемые методы (команды «что сделать»). При написании кода методы отделяются от объекта точкой, например:

Range(«A1»).Select или Cells(1, 1).Select

Данный метод указывает выбрать (Select) ячейку «A1».
Далее, давайте, удалим значение в данной ячейке. Для этого напишем следующий код:

Selection.ClearContents

Здесь программа «берет» то, что мы выделили (Selection) и удаляет его содержимое (ClearContents ).

Язык программирования в excel

������� ���������� ��������� �� ����� VBA – �������, ����� �������� �� ���������� ������, � ����������� �� ��������� ����������. ���� ���������� �������� ���������, ������� ����� ����������� ������������ ���������� ��� ��������, ����������� �������������� ����������� ������������ ���������� Microsoft Office, �� ���������� ������� ����������� ������. ��� ���������� ���� ��������� ����� ��������� � ������� ������� ���������� ������, ������� ������� ����� �������� ���������� ���������. ��� ����� � ���������� ���������� �������� ������ ������������ � ������� ������� ��� ������ ������������ �������� ����������, � ����� ������� ������ � ��������������� ����������� �����. ���� ��������� ��������� � ������� ������� ������ ������ �������.

���������� “�����������” ��������� (��� ���������� ������� ��������� ��������� ����, � ���������� ���������� ����������) ����� �������� ��� �����. ������ ���� – ���� ����������� ����������������, �� ������� ��������� ���� (�����) ���������, ��� ������������� ����������� �������� ����������. ������ – ���� ����������������, �� ������� ��������� ����� ��������� (���������), ������������� � ����� �� ������������ �������. �������� ��������, ��������, ������ ����� ������� ���� �� ��������� ������ (������� Click), ������� ������� �� ���������� (������� KeyPress) � �.�. ������������ ����� ���������� ����� �������� ������ – «������ �������».

Range(“�����”)

Cells(i, j)

Rows(� ������)

Columns(� �������)

Sheets(“���”)

Sheets(� �����)

WorkSheet

Range(“A5:A10”). Value = 0 ��� Range(“A5:A10”) = 0 – � �������� ����� A5:A10 ��������� �������� 0.

Cells(2, 4). Value = n ��� Cells(2, 4) = n – � ������, ����������� �� ����������� 2-� ������ � 4-�� ������� (������ � ������� “D2”), ��������� �������� ���������� n.

Xn = Cells(1, 2).Value ��� Xn = Range(“B1”).Value – ���������� Xn ������������� �������� �� ������ B1 �������� �������� �����.

Sheets(2).Activate ������� �������� ���� � �2.

Sheets(“���������”).Delete ������� ���� � ������ “���������”.

Range(«A5:A10»).Clear – �������� �������� ����� A5:A10.

Range(«A2:B10»).Select – �������� �������� ����� A2:B10.

Application.Quit — ���������� ������ � Excel.

Ссылка на основную публикацию
Adblock
detector