содржина
Excel има моќна, но во исто време многу ретко користена, способност да создава автоматски секвенци на дејства користејќи макроа. Макрото е идеален излез ако се занимавате со ист тип на задача што се повторува многу пати. На пример, обработка на податоци или форматирање на документи според стандардизиран образец. Во овој случај, не ви треба познавање на програмски јазици.
Веќе сте љубопитни што е макро и како функционира? Потоа храбро продолжи - тогаш чекор по чекор ќе го направиме целиот процес на создавање макро со вас.
Што е макро?
Макро во Microsoft Office (да, оваа функционалност работи исто во многу апликации од пакетот Microsoft Office) е програмски код на програмски јазик Visual Basic за апликации (VBA) зачуван во документот. За да биде појасно, документот на Microsoft Office може да се спореди со HTML страница, а потоа макрото е аналог на Javascript. Она што Javascript може да го направи со HTML податоци во веб-страница е многу слично на она што макрото може да го направи со податоци во документ на Microsoft Office.
Макроата можат да направат речиси сè што сакате во документот. Еве некои од нив (многу мал дел):
- Примени стилови и форматирање.
- Вршете различни операции со нумерички и текстуални податоци.
- Користете надворешни извори на податоци (датотеки со бази на податоци, текстуални документи, итн.)
- Создадете нов документ.
- Направете го сето горенаведено во која било комбинација.
Креирање макро – практичен пример
На пример, да ја земеме најчестата датотека CSV. Ова е едноставна табела 10×20 исполнета со броеви од 0 до 100 со наслови за колони и редови. Нашата задача е да го претвориме овој сет на податоци во презентативно форматирана табела и да генерираме збирки во секој ред.
Како што веќе споменавме, макро е код напишан на програмскиот јазик VBA. Но, во Excel, можете да креирате програма без да напишете линија на код, што ќе го направиме токму сега.
За да креирате макро, отворете Види (Тип) > Макроа (Макро) > Сними макро (Макро снимање...)
Дајте му име на вашето макро (без празни места) и кликнете OK.
Почнувајќи од овој момент, СИТЕ ваши дејства со документот се снимаат: промени во ќелиите, лизгање низ табелата, дури и промена на големината на прозорецот.
Excel сигнализира дека режимот за макро снимање е овозможен на две места. Прво, на менито Макроа (макроа) – наместо низа Сними макро Се појави линија (Снимање макро...). Стоп за снимање (Стоп за снимање).
Второ, во долниот лев агол на прозорецот на Excel. Икона Стоп (мал квадрат) покажува дека режимот за макро снимање е овозможен. Со кликнување на него ќе престане снимањето. Спротивно на тоа, кога режимот за снимање не е овозможен, постои икона за да се овозможи макро снимање на оваа локација. Со кликнување на него ќе се добие истиот резултат како и вклучувањето на снимањето преку менито.
Сега кога е овозможен режимот за макро снимање, ајде да дојдеме до нашата задача. Пред сè, да додадеме заглавија за сумираните податоци.
Следно, внесете ги формулите во ќелиите во согласност со имињата на насловите (дадени се варијанти на формулите за англиски и верзии на Excel, адресите на ќелиите се секогаш латински букви и броеви):
- =SUM(B2:K2) or =SUM(B2:K2)
- =ПРОСЕК (B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =СРЕДЕН (B2:K2) or =СРЕДЕН (B2:K2)
Сега изберете ги ќелиите со формули и копирајте ги во сите редови од нашата табела со влечење на рачката за автоматско пополнување.
Откако ќе го завршите овој чекор, секој ред треба да ги има соодветните збирови.
Следно, ќе ги сумираме резултатите за целата табела, за ова правиме уште неколку математички операции:
Соодветно:
- =SUM(L2:L21) or =SUM(L2:L21)
- =ПРОСЕК (B2:K21) or =СРЗНАЧ(B2:K21) – за да се пресмета оваа вредност, потребно е точно да се земат првичните податоци од табелата. Ако го земете просекот на просеците за поединечни редови, резултатот ќе биде различен.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =СРЕДЕН (B2:K21) or =СРЕДЕН (B2:K21) – сметаме дека ги користиме првичните податоци од табелата, од причината наведена погоре.
Сега кога завршивме со пресметките, ајде да направиме форматирање. Прво, да го поставиме истиот формат на прикажување податоци за сите ќелии. Изберете ги сите ќелии на листот, за да го направите ова, користете ја кратенката на тастатурата Ctrl + Aили кликнете на иконата Избери ги сите, кој се наоѓа на пресекот на насловите на редовите и колоните. Потоа кликнете Стил на запирка табот (Ограничен формат). Почетна (Дома).
Следно, сменете го изгледот на заглавијата на колоните и редовите:
- Задебелен стил на фонт.
- Порамнување во центарот.
- Пополнување во боја.
И, конечно, да го поставиме форматот на збирките.
Вака треба да изгледа на крајот:
Ако сè ви одговара, престанете да го снимате макрото.
Секоја чест! Штотуку самите го снимивте вашето прво макро во Excel.
За да го користиме генерираното макро, треба да го зачуваме документот Excel во формат што поддржува макроа. Прво, треба да ги избришеме сите податоци од табелата што ја создадовме, односно да ја направиме празен шаблон. Факт е дека во иднина, работејќи со овој шаблон, ќе ги внесуваме најновите и релевантни податоци во него.
За да ги избришете сите ќелии од податоците, кликнете со десното копче на иконата Избери ги сите, кој се наоѓа на пресекот на насловите на редовите и колоните и од контекстното мени изберете Избришете (Избриши).
Сега нашиот лист е целосно исчистен од сите податоци, додека макрото останува снимено. Треба да ја зачуваме работната книга како макро-овозможен шаблон за Excel што ја има наставката XLTM.
Важна точка! Ако ја зачувате датотеката со наставката XLTX, тогаш макрото нема да работи во него. Патем, можете да ја зачувате работната книга како шаблон Excel 97-2003, кој има формат XLT, поддржува и макроа.
Кога шаблонот е зачуван, можете безбедно да го затворите Excel.
Водење макро во Excel
Пред да ги откриете сите можности на макрото што го создадовте, мислам дека е правилно да се обрне внимание на неколку важни точки во врска со макроата воопшто:
- Макроата може да бидат штетни.
- Прочитајте го претходниот став повторно.
VBA кодот е многу моќен. Конкретно, може да врши операции на датотеки надвор од тековниот документ. На пример, макрото може да ги избрише или менува сите датотеки во папка Мои документи. Поради оваа причина, стартувајте и дозволувајте макроа само од извори на кои им верувате.
За да го извршите нашето макро за форматирање податоци, отворете ја датотеката со шаблон што ја создадовме во првиот дел од ова упатство. Ако имате стандардни безбедносни поставки, тогаш кога ќе отворите датотека, над табелата ќе се појави предупредување дека макроата се оневозможени и копче за нивно овозможување. Бидејќи сами го направивме шаблонот и си веруваме, го притискаме копчето Овозможете содржина (Вклучете содржина).
Следниот чекор е да се увезе најновата ажурирана база на податоци од датотеката CSV (врз основа на таква датотека, го создадовме нашето макро).
Кога увезувате податоци од датотека CSV, Excel може да побара од вас да поставите некои поставки за правилно да ги пренесете податоците на табелата.
Кога ќе заврши увозот, одете во менито Макроа табот (макроа). Види (Преглед) и изберете команда Прикажи макроа (Макро).
Во полето за дијалог што се отвора, ќе видиме линија со името на нашето макро FormatData. Изберете го и кликнете Испратена (Изврши).
Кога макрото ќе почне да работи, ќе го видите курсорот на табелата како скока од ќелија до ќелија. По неколку секунди, со податоците ќе се направат истите операции како при снимање на макро. Кога сè е подготвено, табелата треба да изгледа исто како оригиналот што го форматиравме рачно, само со различни податоци во ќелиите.
Ајде да погледнеме под хаубата: Како функционира макрото?
Како што споменавме повеќе од еднаш, макро е програмски код во програмски јазик. Visual Basic за апликации (VBA). Кога ќе го вклучите режимот за макро снимање, Excel всушност го снима секое дејство што го правите во форма на инструкции за VBA. Едноставно кажано, Excel го пишува кодот за вас.
За да го видите овој програмски код, ви треба во менито Макроа табот (макроа). Види (преглед) кликнете Прикажи макроа (макроа) и во полето за дијалог што се отвора, кликнете Измени (Промени).
Прозорецот се отвора. Visual Basic за апликации, во која ќе го видиме програмскиот код на макрото што го снимивме. Да, правилно разбравте, тука можете да го промените овој код, па дури и да креирате ново макро. Дејствата што ги извршивме со табелата во оваа лекција може да се снимаат со автоматско макро снимање во Excel. Но, посложените макроа, со фино подесена секвенца и логика на дејствување, бараат рачно програмирање.
Ајде да додадеме уште еден чекор во нашата задача…
Замислете дека нашата оригинална датотека со податоци податоци.csv се креира автоматски со некој процес и секогаш се чува на дискот на истото место. На пример, C:Datadata.csv – патека до датотеката со ажурирани податоци. Процесот на отворање на оваа датотека и увоз на податоци од неа може да се сними и во макро:
- Отворете ја датотеката со шаблон каде што го зачувавме макрото − FormatData.
- Направете ново макро со име Вчитај податоци.
- Додека снимате макро Вчитај податоци увезете податоци од датотека податоци.csv – како што направивме во претходниот дел од часот.
- Кога ќе заврши увозот, престанете да го снимате макрото.
- Избришете ги сите податоци од ќелиите.
- Зачувајте ја датотеката како макро-овозможен шаблон Excel (наставка XLTM).
Така, со извршување на овој шаблон, добивате пристап до две макроа - едното ги вчитува податоците, другото ги форматира.
Ако сакате да се вклучите во програмирање, можете да ги комбинирате дејствата на овие две макроа во едно - едноставно со копирање на кодот од Вчитај податоци до почетокот на кодот FormatData.