Capcane folosind Excel Power Query și MySQL pentru a automatiza raportarea +9
- 06.01.16 05:19 •
- kpimaker •
- # 274615 •
- Habrahabr •
- 0 •
- 8400
- la fel ca Forbes, doar mai bine.
Dacă rapoartele trebuie să fie actualizate săptămânal sau chiar zilnic, atunci această procedură devine foarte stresantă, chiar și pentru cele mai răbdătoare. Folosind Excel Power Query add-in și scrierea de date în MySQL, puteți actualiza majoritatea rapoartelor printr-un simplu clic pe butonul Update:
1. Datele din orice număr de surse sunt importate prin interogări SQL în tabele Excel obișnuite.
2. Chiar și dintr-o bază de date mare, puteți scrie în Excel o mică parte a datelor (de exemplu, totalurile pentru intervalul de date dorit, grupând numai pe coloanele necesare).
3. Modificările raportului pot fi făcute doar prin schimbarea interogării SQL. Apoi, vom forma raportul necesar cu instrumente standard Excel.
În acest articol vă voi arăta cum să configurați și să completeze în mod automat într-o bază de date simplă MySQL (de exemplu, statisticile descărcate toate cuvintele cheie Yandex Metrics), și apoi cu un singur clic rapoarte de actualizare în Excel, folosind programul de completare Solicitare de putere. Puterea de interogare are o particularități foarte ciudat de muncă în pregătirea SQL-interogări (în special dinamice), pe care le vom discuta în a doua parte a articolului.
Alegerea MySQL (sau a oricărei alte baze de date populare) este destul de evidentă - liberă, relativ simplă, capacitatea de a lucra cu baze de date destul de mari fără trucuri tehnice. De exemplu, vom folosi serviciul Amazon Web Services: ieftin (în majoritatea cazurilor instanța utilizată va fi gratuită pentru dvs. în termen de 12 luni).
Deci, să începem (dacă aveți deja baze de date cu date gata, puteți merge imediat la secțiunea cu Excel):
1. Înregistrați-vă cu AWS (dacă nu există încă contabilitate), executați cea mai simplă instanță a t2.micro și accesați-o prin SSH. Puteți vedea instrucțiunea scurtă în postul anterior habrahabr.ru/post/265383. Rețineți că vom avea nevoie de opțiunea de primă instanță pe Amazon Linux AMI. Este necesar să se stabilească reguli care să permită accesul la instanță pe porturile necesare:
2. executa o comenzi rând în documentația docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf. Avem nevoie de capitolul "Tutorial: Instalarea unui server Web LAMP pe Amazon Linux". Rețineți parola care este introdusă în performanță «sudo mysql_secure_installation» echipă. Pentru confort, instalați phpMyAdmin așa cum este descris la sfârșitul acestui capitol. Dacă copy-paste a liniei documentului «sudo SED -e -i 's / 127.0.0.1 / your_ip_address / g' / etc / ab TPD / conf.d / phpMyAdmin.conf», veți observa că, uneori, la copierea «httpd "Apare un decalaj suplimentar.
După aceste acțiuni, ar trebui să deschideți o astfel de pagină pe instanța dvs.:
3. Mergem sub root-ul utilizatorului și parola care a fost introdusă în timpul configurării. Pentru a accesa baza de date "din exterior" (adică din Excel), avem nevoie de un alt utilizator decât root. Porniți-l în interfața phpMyAdmin din meniul Users -> Add user. Adăugați statisticile utilizatorului, setați parola și alocați-i privilegiile SELECT și INSERT. Total vom primi:
4. Acum creați datele bazei de date:
5. În acest exemplu, vom popula baza de date cu statistici pentru vizitatori pentru cuvintele cheie de la Yandex Metrics. Pentru a face acest lucru, creați un tabel SEO (rețineți că coloana id trebuie marcată cu opțiunea A_I (incrementare automată)):
Cod PHP pentru încărcarea datelor Yandex Metrics
Dacă apar erori la conectarea la baza de date, acestea vor fi afișate în consola și execuția va fi întreruptă. În cazul unei execuții de succes, obținem statisticile cuvintele cheie pentru perioada selectată:
Excelent, datele sunt primite. Să vedem cum să le obținem în Excel.
Utilizând Power Query pentru a încărca date în Excel
Power Query este un add-in care extinde capacitatea Excel de a descărca datele. Puteți să o descărcați aici la adresa www.microsoft.com/en-us/download/details.aspx?id=39379. Pentru a lucra cu MySQL, este posibil să aveți nevoie de MySQL Connector și Visual Studio (oferite atunci când instalați dintr-o distribuție).
1. După instalare, selectați MySQL:
2. Ca bază de date, specificați ID-ul instanței noastre (așa cum a fost în scenariu) ec2-. compute.amazonaws.com. Datele bazei de date. Pentru a introduce o autentificare, selectați "Baza de date":
3. În fereastra care se deschide, faceți dublu clic pe tabelul seo și obțineți:
În această fereastră, puteți gestiona interogările schimbând coloanele și numărul de linii. Când baza de date este mică, funcționează. Cu toate acestea, în cazul în care dimensiunea datelor depășește chiar 20MB, atunci Excel pe majoritatea calculatoarelor va pur și simplu să stea de la o astfel de cerere. În plus, ar fi bine să modificați datele de interogare sau alți parametri.
Interogările dinamice din Power Query pot fi realizate utilizând limbajul încorporat M msdn.microsoft.com/en-us/library/mt253322.aspx. Cu toate acestea, solicitările sunt extrem de instabile în ceea ce privește modificarea oricăror parametri din ele. Pentru a face cererea să rămână "permanentă", vom face următoarele:
1. Mai întâi compilam un tabel în care specificăm parametrii de care avem nevoie. În exemplul nostru, aceasta este data descărcării. Formatul celulelor cu valori este mai bine setat ca test, deoarece Excel îi place să schimbe formatul celulelor la discreția sa:
2. Creați o interogare Power Query "Din tabel", care va duplica pur și simplu acest tabel:
3. În opțiunile de interogare, asigurați-vă că specificați formatul celei de-a doua coloane ca Text, altfel interogarea SQL ulterioară nu va fi corectă. Apoi faceți clic pe "Închidere și descărcare".
În total, am primit o interogare Power Query la tabela normală, de unde se va lua valoarea startului și sfârșitului descărcării.
Pentru a face SQL-interogare nevoie pentru a dezactiva o opțiune: du-te la Setări și reglaje -> Opțiuni de interogare -> Confidențialitate și selectați „Ignorați niveluri de confidențialitate pentru o performanță îmbunătățită.“ Faceți clic pe OK.
Parametrii și startdate DatăDeÎncheiere specificate valorile în tabelul de la punctul 3. Când vi se solicită, „Pentru a realiza acest lucru, propria interogare permisiunea bazei de date necesară“ clic „Modificați permisiunile“, verificați dacă toți parametrii sunt strânse corect și executați interogări. Acum, răspunsul primit de la SQL-interogare pot fi prelucrate prin formule convenționale Excel în forma obișnuită pentru tine.
5. Important! Când actualizați încărcarea data viitoare, trebuie să faceți acest lucru în felul următor (altele dau o eroare):
- modificați datele din tabelul de la punctul 1
- accesați meniul Date -> Conexiuni și faceți clic pe "Actualizați totul":
În acest caz, toate interogările vor fi executate corect, iar rapoartele dvs. vor fi actualizate automat. În total, pentru a actualiza raportul, trebuie doar să modificați parametrii de interogare și să faceți clic pe Actualizați tot.
Vă puteți ajuta și puteți transfera niște bani la dezvoltarea site-ului