Ja ikdienā strādājat ar izklājlapām un datiem, jaukšana Excel ar Python Tā ir viena no jaudīgākajām kombinācijām, ko varat iekļaut savā darbplūsmā. Jūs varat ne tikai automatizēt atkārtotus uzdevumus, bet arī veikt padziļinātu analīzi, lielapjoma datu tīrīšanu un ģenerēt profesionālus pārskatus, neatstājot pa gabalu no sava uzticamā Excel.
Šajā visaptverošajā rokasgrāmatā jūs soli pa solim redzēsiet, kā lietot Python ar Excel dažādos scenārijos: sākot ar vietējo integrāciju ar Python programmā Excel (funkcija =PY), līdz klasiskai automatizācijai ar bibliotēkām, piemēram, pandas un openpyxl, un pat mākslīgā intelekta rīkiem, piemēram, Excelmatic. Mēs pāriesim no vides iestatīšanas uz ļoti praktiskiem analīzes, tīrīšanas un atskaišu ģenerēšanas piemēriem, lai jūs varētu visu pielietot savos projektos.
Python programmā Excel: kā darbojas vietējā integrācija
Microsoft ir iekļāvusi Python tieši programmā ExcelTas nozīmē, ka koda palaišanai vairs nav nepieciešami dīvaini makro vai trešo pušu pievienojumprogrammas. Python instrukcijas var rakstīt šūnās, apvienot tās ar diapazoniem un izlemt, vai rezultāts tiek atgriezts kā Excel vērtība vai kā Python objekts.
Aktivizējiet Python šūnā
Lai sāktu izmantot šo integrāciju, pirmais solis ir norādīt programmai Excel, ka šūnā būs Python kods. To var izdarīt divos galvenajos veidos. Iespējot formulas programmā Python šūnā:
- Atlasiet šūnu, dodieties uz cilni Formulas lenti un nospiediet Ievietot Python.
- Uzrakstiet funkciju tieši =PY( Šūnā atlasiet to automātiskajā pabeigšanā un pēc tam iekļaujiet kodu.
Kad tas ir izdarīts, šī šūna kļūst par "Pītona šūna" Gan pašā šūnā, gan formulu joslā, kad tā ir atlasīta, redzēsiet PY ikonu. Tur varat rakstīt vairākas koda rindiņas un izmantot redaktora stila rediģēšanas priekšrocības (rindiņu pārtraukumus ar taustiņu Enter, formulu joslas izvēršanu, lai redzētu vairāk koda utt.).
Ērta strādāšana formulu joslā
Formulu josla būtībā kļūst par nelielu redaktoru ar daudz ērtāku rakstīšanas pieredzi. Python koda blokiVarat pieskarties lejupvērstajai bultiņas ikonai, lai to izvērstu un redzētu vairākas rindas, vai arī izmantot saīsni Ctrl+Shift+U lai to ātri atvērtu un aizvērtu, nepieskaroties pelei.
Atsauces uz šūnām un diapazoniem, izmantojot xl() funkciju
Šajā integrācijā tilts starp Excel pasauli un Python pasauli ir pielāgotā funkcija. xl()kas ļauj piekļūt diapazoniem, tabulām, definētajiem nosaukumiem un vaicājumiem:
- Konkrētai šūnai, piemēram, A1, jūs izmantotu xl("A1").
- Diapazonam, piemēram, B1:C4, jūs izmantotu xl("B1:C4").
- Piemēram, nosauktai tabulai Mana tabula, jūs varētu izmantot xl("ManaTabula", galvenes=True) lai visa tabula būtu iekļauta un galvenes tiktu apstrādātas pareizi.
Tādā veidā jūs, piemēram, varat kaut ko līdzīgu izdarīt Python šūnā xl("A1") + xl("B1") un ka skaitliskais rezultāts tiek atgriezts tajā pašā lapā, tāpat kā jebkura cita Excel formula.
Izvades veidi: Excel vērtība vai Python objekts
Viens no svarīgākajiem lēmumiem, lietojot Python programmā Excel, ir tas, kā vēlaties atgriezt rezultātu. Formulu joslas izvades izvēlnē varat izvēlēties, vai aprēķins tiek konvertēts uz klasiskā Excel vērtība (skaitlis, teksts utt.) vai, ja vēlaties, lai tas tiktu parādīts kā Python objekts.
Ja izvēlaties to atgriezt kā vērtību, šūna uzvedas kā jebkura parasta formula: jūs redzat skaitli, tekstu vai visu, kas tika aprēķināts. Tomēr, ja izvēlaties to atgriezt kā Python objektu, šūnā tiek parādīta "kartes" ikona; to atverot, varat redzēt objekta priekšskatījumu, kas ir ļoti noderīgi, strādājot ar sarežģītiem elementiem, piemēram, gariem sarakstiem vai, jo īpaši, DataFrames.
Izmantojot Python programmā Excel, DataFrame kļūst par vienu no zvaigžņu struktūrām, jo tie koncentrējas veselas tabulas ar kolonnām, datu tipiem un darbībām tipiska analīze. Šis karšu skats ļauj ātri pārbaudīt saturu, neizgāžot visu lapā.
Ārējo datu importēšana lietošanai ar Python programmā Excel
Šīs integrācijas galvenais aspekts ir tāds, ka Python darbojas tikai ar datiem, kas jau ir programmā Excel. vai arī saņemt, izmantojot Power Query. Tas ir, ja vēlaties izmantot ārējos datus (CSV(datubāzes, tīmekļa pakalpojumi…), vispirms tie būs jāienes lapā vai modelī, izmantojot opciju Iegūstiet un pārveidojiet (Power Query).
Ideja ir tāda, ka jūs importējat datus, izmantojot Power Query, sagatavojat tos kā tabulu vai diapazonu programmā Excel un pēc tam lietojat savu loģiku programmā Python. xl()Apvienojot labāko no abām pasaulēm: Power Query savienotājus un transformāciju, kā arī precizēšanu ar Python.
Aprēķina secība un atkarības
Python pārrēķinu veikšanai programmā Excel ir svarīgas nianses. Koda līmenī atsevišķā šūnā instrukcijas tiek izpildītas šādi: no augšas uz lejugluži kā tradicionālā skriptā. Taču, ja izklājlapā ir vairākas Python šūnas, Excel joprojām ievēro savus noteikumus. parastā aprēķinu secība pa rindām: pārvietojas no A kolonnas uz XFD pirmajā rindā, pēc tam uz leju uz nākamo utt.
Tas nozīmē, ka, ja definējat mainīgo vienā Python šūnā un pēc tam izmantojat to citā, jums jāievēro šī secība: šūna, kas definē mainīgo, ir jāaprēķina vispirms lasīšanas secība pa rindāmPraksē vislabāk ir sakārtot Python formulas saskaņoti no kreisās uz labo pusi un no augšas uz leju, lai izvairītos no atsaucēm uz mainīgajiem, kas vēl nav definēti.
Pārrēķina kontrole: automātiska, daļēja un manuāla
Mainot vērtību, no kuras ir atkarīgas Python šūnas, programma Excel var aktivizēt visu šūnu automātisku pārrēķināšanu, kas lielos modeļos var izraisīt visa šūnu darbības avāriju. iet lēnāk nekā gaidītsLai no tā izvairītos, varat pielāgot Aprēķinu opcijas cilnē Formulas un izmantojiet:
- Daļēja aprēķina režīms, kur ir ierobežots, kuras daļas tiek automātiski pārrēķinātas.
- Manuālais aprēķina režīmskur jūs izlemjat, kad visu pārrēķināt.
Šajos režīmos varat manuāli aktivizēt pārrēķināšanu ar F9, izmantojot Formulas > Aprēķināt tagadvai no kļūdas ziņojuma, kas parādās šūnās ar novecojušām vērtībām (pārsvītrotām), izvēloties vēlreiz Aprēķināt tagadTas ir ļoti noderīgs triks, izstrādājot sarežģītas formulas un nevēloties, lai katra mazā izmaiņa izraisītu sarežģītus aprēķinus.
Tipiskas kļūdas programmā Python for Excel
Izmantojot Python programmā Excel, var parādīties konkrēti kļūdu kodi, piemēram, #PITONS!, #AIZŅEMTS! o #SAVIENOJIES!Katrs no tiem norāda uz cita veida problēmu: izpildes kļūdas Python kodā, joprojām notiekoši procesi vai savienojuma konflikti ar Python vidi.
Ieteicams iepazīties ar šiem kodiem un dokumentāciju. Python problēmu novēršana programmā Excel lai, piemēram, saprastu, vai kļūda rodas nepieejamas bibliotēkas, taimauta, nebeidzama cikla vai slikti konstruētas diapazona atsauces ar xl() dēļ.
Otrs veids: Excel automatizācija ar Python ārpus Excel
Vietējā integrācija ir lieliska failu analīzei, taču ir daudz situāciju, kad tā ir noderīgāka. automatizēt Excel no ārpusesServera procesi, ieplānoti darbi, datu plūsmas vai pilnīgas lietojumprogrammas. Tieši tur bibliotēkas, piemēram, openpyxl, pandas un modernāki uz mākslīgo intelektu balstīti risinājumi.
Divas galvenās automatizācijas pieejas
Kad mēs runājam par Excel automatizāciju no Python, mēs būtībā sastopamies ar divas galvenās pieejas kas viens otru papildina:
- Klasiskā programmatiskā pieeja ar openpyxl un pandaskur jums ir detalizēta kontrole pār koda lasīšanu, rakstīšanu, formatēšanu, formulām un grafiku. Tas ir ideāli piemērots, ja esat izstrādātājs vai jums ir ērti strādāt ar skriptiem, un jums ir nepieciešams stabila un atkārtojama automatizācija.
- Pieeja, ko virza Mākslīgais intelekts ar Excelmatic tipa rīkiemkur augšupielādējat Excel failus, dabiskā valodā aprakstiet vēlamo (tīrīšanu, atskaites, grafikus, formulas…), un rīks paveiks darbu, jums nerakstot nevienu koda rindiņu.
Sadalīsim tos mierīgi, jo, pamatojoties uz jūsu profiluJūs vairāk interesēs viens vai otrs... vai abu apvienojums.
"Klasiskā" automatizācija ar openpyxl un pandas
Ja jūs strādājat izstrādes vai datu zinātnes jomā, visdabiskākā rīcība ir specializētu bibliotēku izmantošana. Galvenie elementi darbam ar .xlsx un .xlsm faili Parasti tie ir pandas un openpyxl, kuriem atkarībā no vajadzībām var pievienot tādus spraudņus kā numpy vai xlsxwriter.
Ieteicamā uzstādīšana un vide
Lai izvairītos no versiju problēmām un konfliktiem starp bibliotēkām, vissaprātīgākais risinājums ir izveidot virtuālā vide un laba IDE un instalējiet tajā nepieciešamās atkarības. Tipiska darbplūsma būtu šāda:
- Pārliecinieties, vai ir instalēta Python 3.8 vai jaunāka versija (komanda python –versija o python3 versija).
- Izveidojiet vidi, piemēram, python -m venv excel_automationun aktivizējiet to.
- Instalējiet galvenās pakotnes, piemēram, pandas, openpyxl, numpy, xlsxwriter ar pip un, ja vēlaties būt drošībā, iestatiet konkrētas versijas.
Daudzas komandas pat sagatavo verifikācijas skriptu, kas pārbauda pakotņu versijas un klātbūtni, lai pārliecinātos, ka visiem projekta dalībniekiem ir tāda pati darba videTā ir laba prakse, ja nopietni domājat par automatizāciju.
pandas un openpyxl: ko katrs dara
Lai gan tās bieži tiek izmantotas kopā, katrai bibliotēkai ir savs fokuss. pandas Jūs galvenokārt strādājat ar DataFrame: tabulārām struktūrām, kas paredzētas datu analīzei, filtrēšanai, apkopošanai un pārveidošanai. openpyxl Jūs manipulējat ar "neapstrādātu" Excel darbgrāmatu: lapām, šūnām, stiliem, diagrammām, struktūrām, veidnēm…
Reālistiskā darbplūsmā pandas parasti tiek izmantotas lasīt un apstrādāt datus Un tad, kad nepieciešama precīza kontrole pār galīgā .xlsx faila izskatu un struktūru, nosūtiet rezultātu uz openpyxl vai citu rakstīšanas programmu un pabeidziet formatējumu.
Lasīšana un rakstīšana programmā Excel kopā ar pandām
Galvenā failu lasīšanas funkcija ir pd.read_excel()kas ļauj ielādēt jebko, sākot no vienkārša .xlsx faila līdz vairāku lapu darbgrāmatām, atlasīt tikai noteiktas kolonnas, izlaist rindas utt. Piemēram:
Izlasīt pilnu failu un parādīt pirmās dažas rindas:
import pandas as pd
df = pd.read_excel("ejemplo_excel.xlsx")
print(df.head())
Ja jūs interesē tikai noteiktas lapas vai kolonnas, varat tās pāriet. lapas_nosaukums o usecols lai ierobežotu ielādējamo failu skaitu, kas uzlabo veiktspēju un samazina atmiņas patēriņu, kas ir ļoti svarīgi, strādājot ar lieliem failiem.
Kad dati ir pārveidoti (filtrēti, grupēti, pievienotas aprēķinātās kolonnas utt.), varat tos saglabāt programmā Excel, izmantojot df.to_excel()Faila nosaukuma izvēle un indeksa iekļaušana. Tas ir ļoti vienkāršs veids, kā veidot tīras atskaites un tabulas pamatojoties uz neapstrādātiem datiem.
Automatizējiet vairāku failu konsolidāciju
Tipisks reālās dzīves scenārijs ir desmitiem Excel failu ar vienādu struktūru (piemēram, pārskati pa veikaliem, mēnešiem vai nodaļām) un vēlme apvienot tos visus vienā darba burtnīcā analīzei. Ar pandām modelis vienmēr ir ļoti līdzīgs: atrodiet failus ar glob, izlasiet katru no tiem ar read_excel, savienojiet un saglabājiet.
Šī pieeja ļauj dažu minūšu laikā iestatīt datu integrācijas procesus, kuru manuāla kopēšana un ielīmēšana programmā Excel būtu murgs, un tā ir arī… atkārtojams un mērogojams kad tiek saņemti jauni faili.
Paplašināta grāmatu manipulācija ar openpyxl
Kad nepieciešami ne tikai dati, bet arī Excel darbgrāmatas precizēšana (stili, apmales, nosacījuma formatēšana, formulas, diagrammas…), tiek izmantots openpyxl. Dažas no tā galvenajām iespējām ir:
- Atveriet esošās grāmatas ar ielādēt_darbgrāmatu vai izveidojiet jaunus ar Darba burtnīca().
- Piekļūstiet aktīvajām lapām vai pēc nosaukuma, ievietojiet un dzēsiet rindas/kolonnas, mainiet lapu nosaukumus, iesaldējiet paneļus utt.
- Lasīt un rakstīt šūnas gan pēc "A1" tipa apzīmējuma, gan pēc rindas/kolonnas (lapa.šūna(rinda=1, kolonna=1)).
- Definēt pasūtījuma stilitreknraksta fonti, fona krāsas, izlīdzināšana, apmales, skaitļu formāti… izmantojot tādus objektus kā Fonts, PatternFill, Alignment vai NamedStyle.
- Izveidot un novietot grafika (joslas, līnijas utt.) no datu diapazoniem, kas definēti ar Reference, un pievienojiet tos lapai, izmantojot sheet.add_chart().
- Ievietojiet Excel formulas, tieši ierakstot virkni (piemēram, "=SUMMA(A1:A2)") atbilstošajā šūnā.
Ņemiet vērā, ka openpyxl neaprēķina formulas, tas tikai ieraksta tās failā. Faktisko aprēķinu veic [atbilstošā programma/rīks]. Excel, atverot darbgrāmatuDaudziem atskaišu veidošanas procesiem tas ir ideāli piemērots, jo jūs nodrošināt, ka gala lietotājs var turpināt rediģēt formulas un veikt pārrēķinus pēc saviem ieskatiem.
Reālistiski piemēri: atskaišu ģenerēšana un datu attīrīšana
Ļoti spēcīgs lietošanas modelis ir vispirms ģenerēt parauga dati un testa vides izmantojot Python skriptus (piemēram, vairākus pārdošanas failus ar datumiem, kategorijām, summām un pārdošanas pārstāvjiem, kā arī vēl vienu ar "neapstrādātiem" klientu datiem). Pēc tam palaižat vēl vienu skriptu, kas:
- Pārliecinieties, vai ir visi nepieciešamie faili.
- Apvienojiet pārdošanas Excel failus vienā DataFrame.
- Aprēķiniet ikmēneša kopsavilkumus pēc kategorijas, statistikas un galvenajiem rādītājiem.
- Sakopiet "nekārtīgo" klientu failu, noņemot dublikātus, labojot formātus, standartizējot tekstus un labojot e-pastus un datumus.
- Izveidot a rezultātu grāmata ar vairākām lapām: mēneša kopsavilkums, analīze pa kategorijām, tīri klienti, datu paraugi un statistikas tabula.
Šāda veida skripts pārvērš to, kas agrāk bija vairāku stundu manuāls darbs programmā Excel, par sekunžu izpildeUn, ja pievienojat kļūdu apstrādi (atļauju pārbaudes, atvērtos failus, bibliotēkas versijas), jūs iegūstat stabilu darbplūsmu lokālai darbībai vai programmēšanai serverī.
Mūsdienīga pieeja: Excel automatizācija ar mākslīgo intelektu (Excelmatic un līdzīgas programmas)
Ne visi to vēlas vai var iemācīties programmētBiznesa, mārketinga, pārdošanas vai operāciju profesionāļiem kodēšanas rakstīšana, izmantojot pandas vai openpyxl, var būt tieši... ieejas sienaTieši šeit noder tādi uz mākslīgo intelektu balstīti rīki kā Excelmatic.
Ko mākslīgā intelekta rīks sniedz programmai Excel?
Pieeja ir radikāli atšķirīga: tā vietā, lai domātu funkciju, ciklu un DataFrame izteiksmē, jūs vienkārši Aprakstiet dabiskā valodā, ko vēlaties darītParastā plūsma ir apmēram šāda:
- Augšupielādējiet rīkā vienu vai vairākus Excel failus.
- Rakstiet pieprasījumus, piemēram: “Notīriet datus, noņemiet dublikātus, izlabojiet e-pasta adreses un izveidojiet tabulu ar kopējo pārdošanas apjomu pa mēnešiem un kategorijām.”
- Pārskatiet izvadi: apstrādātās tabulas, diagrammas, jaunās kolonnas, pievienotās formulas… un lejupielādējiet jauno Excel failu.
Zem pārsega sistēma pārvērš jūsu nodomu realitātē tehniskās operācijasIzsaukumi uz analīzes bibliotēkām, noteikumu tīrīšana, formatēšana, diagrammu izveide utt. Bet jūs par to neuztraucaties; jūs koncentrējaties tikai uz to, ko vēlaties sasniegt biznesa līmenī.
Tipiski uzdevumi, ar kuriem mākslīgais intelekts tiek galā ļoti labi
Šāda veida instrumenti izcelties jo:
- Ātra datu tīrīšanaliekas atstarpes, nekonsekventi lielie/mazie burti, jaukti datumu formāti, nepareizi uzrakstītas kategorijas, tukši lauki utt.
- Ad hoc ziņojumu izveideJūs pieprasāt "ceturkšņa pārdošanas apjomu kopsavilkumu pa reģioniem ar joslu diagrammu", un tas dažu sekunžu laikā ģenerē tabulas un vizualizācijas.
- Lietojiet konsekventu formatējumu un stilus: virsraksti korporatīvajā krāsā, viendabīgs fonts, atbilstoši skaitļu un datuma formāti, neizmantojot tūkstoš izvēlnes.
- Formulu ģenerēšana un aizpildīšana nezinot precīzu sintaksi: "Aprēķināt bruto peļņas normu D kolonnā no B un C", un tas arī viss.
Netehniskām komandām, kurām zibens ātrumā ir nepieciešamas atskaites, mākslīgais intelekts kļūst par ideālu saīsni, no kuras pāriet neapstrādāti dati par praktiski izmantojamām atziņām neizejot cauri attīstībai.
Un kā ar kontroli un reproducējamību?
Negatīvā puse ir tāda, ka ar mākslīgā intelekta rīku ir mazāk detalizēta kontrole nekā ar Python skriptu. Ja jums ir nepieciešama darbplūsma, kas katru nakti serverī darbojas tieši tāpat, visspēcīgākā opcija joprojām ir... programmatisks skriptsMākslīgais intelekts ir vispiemērotākais izpētes uzdevumiem, vienreizējām analīzēm, bieži mainīgiem ziņojumiem un lietotājiem, kuri dod priekšroku runāt dabiskā valodā, nevis rediģēt kodu.
Salīdzinājums: Kad izmantot katru pieeju?
Izvēle starp Python programmā Excel, ārējiem skriptiem vai mākslīgā intelekta risinājumiem nav "viss vai nekas" risinājums; Gudrākais, ko darīt, ir apvienot Atkarībā no konteksta un jūsu profila. Plašākā nozīmē mēs varētu teikt:
- Vietējais Python programmā Excel Tas ir ideāli piemērots, ja jau esat biežs Excel lietotājs, vēlaties turpināt strādāt tajā pašā saskarnē un jums ir nepieciešams padziļinātāka analīze (DataFrame, sarežģīta loģika), neizejot no faila. Lieliski piemērots uzlabotiem informācijas paneļiem, analītiskiem prototipiem vai modeļiem, kurus vēlaties kopīgot ar cilvēkiem, kuri strādā programmā Excel.
- pandas + openpyxl Tie ir jūsu labākā izvēle, ja jums nepieciešama stabila automatizācija, integrācija ar citām sistēmām, servera puses izpilde, uzdevumu plānošana vai sarežģīti cauruļvadi. Tā ir izstrādātāju un datu zinātnieku dabiskā vide.
- Excelmatic tipa mākslīgā intelekta rīki Tie ir piemēroti uzņēmumu profiliem, kas meklē ātrus rezultātus, neapgrūtinot sevi ar kodu, vai komandām, kas vēlas apstiprināt idejas un ģenerēt pārskatus acumirklī pēc iespējas dabiskākā veidā.
Patiesībā daudzi projekti apvieno visas trīs pasaules: dati tiek ģenerēti ar servera puses skriptiem, gala pārskati tiek apstrādāti ar openpyxl, rezultāti tiek pētīti ar Python programmā Excel, un vadītājiem tiek sagatavoti īpaši skati, izmantojot mākslīgā intelekta darbinātu rīku, kas viņiem ļauj… eksperimentējiet ar datiem savā tempā.
Apgūstot Excel un Python savienojamību, gan izmantojot vietējo integrāciju ar =PY un xl(), gan bibliotēkas, piemēram, pandas un openpyxl vai mākslīgā intelekta rīkus, jūs iegūstat milzīgs iespēju klāstsNo tūkstošiem rindu iztīrīšanas ar vienu klikšķi līdz vairāku lapu pārskatu, diagrammu un prezentācijām gatavu KPI izveidei, kā arī automatizācijai, kas darbojas automātiski katru nakti; jo labāk jūs apvienosiet šīs pieejas, jo vieglāk būs pāriet no haotiskām izklājlapām uz stabilas darbplūsmas, ātri un, galvenais, daudz mazāk nogurdinoši. Kopīgojiet šo informatīvo ceļvedi, un vairāk lietotāju uzzinās par šo tēmu.