Pereiti prie turinio
Di Automatizacijos
Di Automatizacijos
SISTEMOS BŪSENA: VEIKIA
Žinios

Kaip saugiai automatizuoti Excel lentelę: 7 taisyklės

Saugiai automatizuoti Excel lentelę reiškia, kad programa nesugadina to, ką įvedė žmonės, o kiekvieną jos įrašą galima patikrinti ir atšaukti. Tam laikomės septynių taisyklių: žmogus mato ir patvirtina, ką programa įrašys; programa rašo tik į tuščius langelius; prieš rašydama pasidaro atsarginę kopiją; į atidarytą failą rašo tik per Excel; stulpelius atpažįsta pagal antraštes; abejotinos eilutės nepildo, o nurodo priežastį; skaičiavimo variklis pasiekiamas tik iš to paties kompiuterio. Jei užduotis paprasta ir ją atliekate tik jūs patys, gali pakakti paties Excel įrankių – makrokomandų įrašymo priemonės ar Power Query įrankio. Kai biuras kas savaitę perrašo duomenis iš PDF ar el. laiškų, tokį darbą automatizuojame Excel priedu, o darbinė lentelė lieka paprastas failas be makrokomandų.

· 8 min. skaitymo

Kokios 7 taisyklės saugo darbinę Excel lentelę?

Darbinę Excel lentelę, į kurią rašo programa, saugo septynios taisyklės. Pirmosios šešios tinka bet kokiai automatizacijai, kuri rašo į darbinę lentelę – ir makrokomandai, ir priedui, o septintoji – sprendimams, kurių skaičiavimai vyksta atskiroje programoje. Kiekviena taisyklė mažina konkrečią riziką, pirmiausia – tylių automatizavimo klaidų, kurios dažnai paaiškėja tik tada, kai nesutampa sąskaita.

  1. Žmogus mato ir patvirtina. Prieš rašydama programa parodo, ką ir kur įrašys. Be žmogaus patvirtinimo į lentelę neįrašoma nieko.
  2. Tik į tuščius langelius. Programa ranka įvestų reikšmių neperrašo: jei vadybininkas skaičių įvedė pats, tas skaičius lieka nepakeistas.
  3. Atsarginė kopija. Prieš rašydama programa padaro failo atsarginę kopiją, todėl į ankstesnę būseną galima grįžti.
  4. Į atidarytą failą – tik per Excel. Kai darbuotojas dirba atidarytoje lentelėje, patvirtintą rezultatą įrašo priedas per patį Excel. Jei rezultatą reikia įrašyti tiesiai į failą diske, o tas failas tuo metu atidarytas, programa nieko neįrašo. Kai į tą patį failą vienu metu rašoma iš dviejų vietų, pakeitimai gali dingti.
  5. Stulpeliai atpažįstami pagal antraštes. Programa lentelės išdėstymą nustato pagal stulpelių antraštes, o ne pagal failo pavadinimą, todėl pervadintas failas ar nauja jo versija darbui netrukdo.
  6. Abejotina eilutė praleidžiama, o priežastis nurodoma. Jei eilutės negalima patikimai suskaičiuoti, pavyzdžiui, kai faile nelogiškos datos, programa jos nepildo ir paaiškina kodėl. Neatpažinta sąskaitos eilutė lieka matoma atskirame sąraše, o ne tyliai dingsta. Tai tikrina specialus automatinis testas.
  7. Variklis pasiekiamas tik iš to paties kompiuterio. Skaičiavimo variklis – tai programa, kuri skaičiuoja tame pačiame kompiuteryje, kuriame veikia Excel. Jis priima užklausas tik iš adreso 127.0.0.1, tik POST metodu su specialia HTTP antrašte ir tikrina užklausos kilmę. API raktai laikomi aplinkos kintamuosiuose, o ne failuose ar kode.

Šių taisyklių laikomės ir kurdami Excel automatizavimą įmonėms: pavyzdžiui, kad be žmogaus patvirtinimo programa į lentelę nerašo, užtikrina pats programos kodas, o ne instrukcija darbuotojams.

Kaip veikia automatizavimas, kai biuras lieka savo Excel lentelėje?

Biuras toliau dirba savo Excel faile, o į pačią Excel programą įdiegiamas priedas (.xlam, Microsoft jį vadina papildiniu), kuris kreipiasi į vietinį variklį tame pačiame kompiuteryje. Darbuotojas pažymi eilutes, dešiniuoju pelės klavišu pasirenka veiksmą, pamato, ką programa įrašys, ir tik tada patvirtina.

  • Excel – sąsaja. Tas pats failas, tie patys stulpeliai, tas pats darbo ritmas – naujos programos mokytis nereikia.
  • Priedo makrokomanda – tik pasiuntinys. Ji perduoda varikliui lapo duomenis ir įrašo patvirtintą rezultatą. Skaičiavimo taisyklių joje nėra.
  • Visos taisyklės – variklyje. Skaičiavimai, patikros ir žinynai laikomi vienoje vietoje. Įmonės ir jos užsakovų ypatumai aprašomi atskirai, todėl naujas užsakovas – tai naujas aprašas, o ne naujas kodas.
  • Darbinė lentelė – paprastas Excel failas. Makrokomandų joje nėra, todėl ją galima kopijuoti, siųsti ir atidaryti kaip anksčiau.

Pagal šį modelį – Excel sąsaja, priedas ir vietinis variklis – veikia trys automatizacijos, kurias pridavėme logistikos įmonei: kilometrų ir kelių mokesčių skaičiavimas, užsakovo sąskaitos PDF suvedimas ir sąskaitų surinkimas iš pašto dėžutės. Išsamiau jas aptariame logistikos įmonės atvejo apžvalgoje.

Variklis skaičiuoja, o ne spėja. DI (AI) kalbos modelis gali suklysti skaičiuodamas kiekius ar sumas, todėl skaičiavimus atlieka deterministinė programa, o kalbos modelis, jei jo apskritai reikia, tik paaiškina rezultatą. Struktūruotos PDF sąskaitos skaitomos tiesiai iš jose esančio teksto – be optinio ženklų atpažinimo (OCR) ir be kalbos modelio spėjimų.

Vietinis variklis dar nereiškia, kad duomenys niekur nesiunčiami. Jei skaičiavimui reikia išorinės paslaugos, pavyzdžiui, maršrutų API ar debesyje veikiančio kalbos modelio, ta paslauga gauna užklausai reikalingus duomenis: maršrutų API – adresus ar jų koordinates, kalbos modelis – tekstą, kurį turi perskaityti. Todėl prieš pradedant darbą verta susitarti, kokie duomenys ir kur siunčiami, kurioje šalyje jie apdorojami ir kieno vardu registruojamos paslaugos.

Kaip patikrinti, ar programa skaičiuoja teisingai?

Ar programa skaičiuoja teisingai, tikriname lentelės kopijoje: ištriname reikšmes, kurias ji turėtų atkurti, programa jas užpildo, o rezultatą lyginame su tuo, ką anksčiau įvedė žmonės. Pirmą kartą sprendimas paleidžiamas tik lentelės kopijoje, ne originale.

Lyginame stulpelį po stulpelio, o kiekvieną langelį priskiriame vienai iš keturių grupių.

Palyginimo kopijoje rezultatų grupės
GrupėKą reiškiaKą darome
SutampaProgramos reikšmė sutampa su ranka įvesta.Nieko – taip ir turi būti.
Reikšmė skiriasiSkaičius ar tekstas nesutampa.Ieškome priežasties: programos taisyklė, kitaip suprastas atvejis ar žmogaus perrašymo klaida.
Tipas skiriasiReikšmė ta pati, bet kito tipo, pavyzdžiui, data įrašyta kaip tekstas.Patikriname, ar tai tik formatas, ar tikras skirtumas.
NepalygintaNėra su kuo palyginti arba programa langelio sąmoningai nepildė.Patikriname, ar praleidimas pagrįstas ir ar nurodyta priežastis.

Toks palyginimas kartais parodo ir žmogaus klaidų: skirtumas atsiranda ne dėl programos, o todėl, kad skaičius kažkada buvo klaidingai perrašytas. Didelis skirtumas – ne atsitiktinumas, o signalas, todėl jo nelaikome paklaida: kiekvienam dideliam skirtumui ieškome priežasties ir ją tikriname skaičiais.

Palyginimas kopijoje – ne vienintelė apsauga. Kiekvienas automatinis skaičiavimas turi savo patikros taisyklę, pavyzdžiui, kontrolinę sumą: bendra sąskaitos suma turi sutapti su visų mokesčių stulpelių suma. Be to, kiekvieną kartą pakeitus kodą, automatiniai testai patikrina, ar rezultatai nepasikeitė.

Logistikos įmonės projekte kontroline suma tikrinama kiekviena užsakovo sąskaita. Bandyme programos rezultatai sutapo su ranka įvestais duomenimis, o sąskaitos suma – iki cento.

Kuo priedas skiriasi nuo makrokomandų ir gatavos programos?

Priedas su vietiniu varikliu nuo VBA makrokomandų ir gatavos programos pirmiausia skiriasi tuo, kur laikomos skaičiavimo taisyklės ir kas nutinka darbiniam failui.

Makrokomandos, gatava programa ir priedas su vietiniu varikliu
KriterijusVBA makrokomandos faileGatava programa (TMS, ERP modulis)Priedas ir vietinis variklis
Kur laikomos taisyklėsPačiame darbiniame faileTiekėjo programojeVariklyje, atskirai nuo duomenų
Kas nutinka darbiniam failuiTampa failu su makrokomandomisDažniausiai duomenys perkeliami į naują sistemąLieka paprastas Excel failas
Ką reikia išmoktiNieko naujo – tas pats ExcelNaują programąVieną naują meniu punktą
Pritaikymas įmonės ypatumamsToks, kokį parašo makrokomandos autoriusDažniausiai toks, kokį numato programaTaisyklės rašomos pagal jūsų duomenis
Kada tinkaKai veiksmas paprastas ir retai keičiasiKai procesas standartinis, o įmonė vis tiek ruošiasi keisti sistemąKai darbas kartojasi kas savaitę, o biuras nori likti savo lentelėje

Makrokomandos nėra blogas sprendimas. Vienam paprastam pasikartojančiam veiksmui dažnai pakanka Excel makrokomandų įrašymo priemonės: ji jūsų veiksmus įrašo VBA kodu, o įrašytą makrokomandą galima paleisti, kai tik prireikia.

Kai kuriems darbams makrokomandų iš viso nereikia. Duomenims iš kelių šaltinių sujungti ir sutvarkyti Excel turi įtaisytą Power Query įrankį: jis įsimena kiekvieną su duomenimis atliktą veiksmą, o atnaujinus užklausą visus veiksmus pakartoja automatiškai. Kaip be programuotojo perkelti duomenis iš PDF dokumento, aptariame straipsnyje kaip perkelti duomenis iš PDF į Excel.

Bėdos prasideda, kai taisyklės įrašomos į pačią darbinę lentelę. Tada kiekviena failo kopija turi savo kodo versiją. Be to, Office programos Windows kompiuteriuose pagal numatytuosius nustatymus blokuoja makrokomandas iš interneto gautuose failuose, pavyzdžiui, atsiųstuose el. paštu. Todėl per pažintinį pokalbį klausiame ir apie tai, kas prižiūri esamas makrokomandas.

Ta pati Microsoft apsauga taikoma ir priedams: iš interneto atsisiųstas .xlam priedas įkeliamas išjungtas ir neveikia, kol nuo failo nepašalinama interneto žyma (angl. Mark of the Web) arba failas neišsaugomas patikimoje vietoje (angl. Trusted Location). Skirtumas tas, kad priedą atblokuoti reikia tik jį diegiant ar atnaujinant, o darbinę lentelę, kurioje makrokomandų nėra, galima siųsti el. paštu be šio apribojimo.

Gatava programa dažniausiai reikalauja perkelti į ją visą darbą, o automatizavimas esamame faile pritaikomas konkrečios įmonės ypatumams. Jei procesas standartinis ir įmonė vis tiek ruošiasi keisti sistemą, gatava programa gali būti tinkamas kelias. Kai darbas apima kelias sistemas, kalbame jau ne apie vieną lentelę, o apie verslo procesų automatizavimą.

Kodėl Excel, o ne atskiras skydelis naršyklėje?

Biurui, kuriame nėra IT specialistų, geriausia sąsaja – ta pati Excel lentelė, kurią darbuotojai ir taip atsidaro kiekvieną rytą. Todėl klientui skirto atskiro skydelio naršyklėje atsisakėme: jį palikome tik savo vidinei diagnostikai.

Kiekvieną sprendimą tikriname vienu klausimu: ar įmonės vadovas jį atsidarytų ir naudotų be kūrėjo pagalbos? Skydelis su atskiru prisijungimu ir kitokiu duomenų vaizdu – dar vienas įrankis, kurį reikia prisiminti, išmokti ir kuriuo reikia pasitikėti. Lentelė, kurioje atsirado vienas naujas meniu punktas, šį patikrinimą išlaiko kur kas lengviau.

Automatizavus darbą, darbuotojai netampa nereikalingi – jie tampa tikrintojais: dirba toje pačioje lentelėje, tik nebeperrašinėja ranka to, ką suveda programa.

Kada darbą Excel lentelėje automatizuoti neverta?

Darbą Excel lentelėje automatizuoti neverta, kai lentelė jau atlieka duomenų bazės vaidmenį, kai ji pildoma tik naršyklėje, kai veiksmas nesikartoja arba kai reikalingų duomenų šaltinyje tiesiog nėra.

  • Daug žmonių vienu metu redaguoja tą patį failą. Jei lentelę nuolat būna atsidarę keli žmonės, nelieka momento, kai įrašyti būtų saugu, o jų pakeitimai ima prieštarauti vieni kitiems. Tokiam darbui reikia duomenų bazės su naudotojų teisėmis, o ne geresnės lentelės.
  • Procesui reikia tikros sistemos. Kai svarbios užsakymų būsenos, pakeitimų istorija ir ryšiai su kitomis sistemomis, Excel tampa siaurąja vieta. Jei įmonė jau turi ERP ar CRM su API, integracija įmanoma, bet ją žadame tik pamatę tos sistemos licenciją ir API dokumentaciją.
  • Lentelė pildoma tik naršyklėje. Naršyklėje atidarytame Excel faile makrokomandos neveikia, todėl toks priedas skirtas kompiuteryje įdiegtai Excel programai.
  • Darbas nesikartoja. Automatizuojame tai, kas iš tiesų kartojasi ir atima laiko. Viename projekte dalies duomenų suvedimo sąmoningai neautomatizavome – įmonės vadovas įvertino, kad tai nėra našta.
  • Duomenų šaltinyje nėra. Ko dokumente nėra, pavyzdžiui, faktinių atvykimo laikų, to programa neužpildys – tai pasakome iš anksto.

Jei nežinote, nuo ko pradėti, atskirame straipsnyje aptariame, kurį procesą automatizuoti pirmiausia.

Per nemokamą automatizavimo auditą jūsų procesą aptariame pokalbyje, pateikiame pirminį įvertinimą, ar jį verta automatizuoti, ir pasiūlome vieną konkretų tolesnį žingsnį – tam failų nereikia. Jei nuspręsite tęsti, pradžiai užteks vieno tipinio atvejo failų kopijos (originalų nereikia): sprendimą išbandysime ir parodysime su ja, o ne su demonstraciniais duomenimis.

Šaltiniai

  1. Microsoft palaikymas: Užduočių automatizavimas naudojant makrokomandų įrašymo priemonę
  2. Microsoft palaikymas: Apie „Power Query“ programoje „Excel“
  3. Microsoft Learn: Macros from the internet are blocked by default in Office
  4. Microsoft palaikymas: Darbaknygės naudojimo naršyklėje ir programoje „Excel“ skirtumai

Turite lentelę, į kurią duomenys perrašomi ranka?

Jei jūsų biure duomenys iš el. laiškų, PDF ar kitų lentelių vis dar ranka perrašomi į Excel, per nemokamą auditą pateiksime pirminį įvertinimą, ar šį darbą verta automatizuoti, ir pasiūlysime vieną konkretų tolesnį žingsnį.