Come importare i dati da un sito Web in Excel?
È noto che il World Wide Web contiene enormi dati utili. Tuttavia, dobbiamo importare i dati in Microsoft Excel prima di fare qualsiasi tipo di analisi. Ci sono due metodi che è possibile utilizzare per completare questo tipo di attività.
Ottieni dati esterni dal Web manualmente
Supponiamo che tu voglia scaricare i ricavi sui migliori film di guadagno da questa pagina Web, qui ti mostra come farlo facilmente.
Aprire Microsoft Excel e fare clic sulla scheda Dati, nel gruppo Ottieni dati esterni, fare clic su Da Web. Dopo che viene visualizzata la finestra di dialogo Nuova query Web, copiare l’indirizzo Web (http://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) nel campo Indirizzo e fare clic sul pulsante Vai. Excel inizierà a scaricare la pagina web come mostrato in Figura 1.1. Basta fare clic su No se si otterrà una casella di avviso di errore di script che è simile a quella nella figura sottostante. La casella scompare e non ha alcun impatto sul processo di importazione.
Figura 1.1
C’è una freccia nella casella gialla nell’angolo in alto a destra della finestra di dialogo Nuova query Web. Facendo clic su di esso, è possibile determinare se mostrare icone simili prima delle tabelle o meno. Ad esempio, non vi è alcuna icona freccia accanto alla tabella nel pannello di sinistra della Figura 1.2. E ci sarà un’icona (nel pannello di destra) dopo aver fatto clic sul pulsante freccia per mostrare le icone.
Figura 1.2
Fare clic sull’icona a forma di freccia accanto alle tabelle che si desidera selezionare. Le icone e le tabelle cambieranno e simile a quello mostrato nel pannello di sinistra della Figura 1.3. Dopo aver fatto clic su Importa; verrà richiesta una finestra di dialogo Importa dati. Riempi l’intervallo (dalla colonna A alla colonna H nel nostro caso) in cui vuoi inserire i dati e fai clic su OK.
Figura 1.3
I dati verranno importati in Excel dopo aver fatto clic su Ok. Se si fa clic destro su qualsiasi cella all’interno della tabella e selezionare Aggiorna, Excel uscirà e tirare fuori i dati più recenti da una pagina web.
Figura 1.4
E puoi anche impostare i tuoi dati di query per essere aggiornati in qualsiasi modo tu voglia. Basta fare clic destro su qualsiasi cella all’interno della tabella, selezionare Proprietà Intervallo di dati. Nella finestra di dialogo Proprietà intervallo dati esterno richiesto, è possibile modificare le impostazioni del controllo Aggiorna per modificare lo stile. Ad esempio, è possibile specificare la query da aggiornare ogni 60 minuti o quando il file viene aperto.
Raschiare i dati utilizzando la programmazione VBA
La programmazione VBA può essere utilizzata per raschiare i dati da una pagina web. È molto più utile mentre è difficile quando si confronta con il primo approccio. Inoltre, è necessario capire cos’è l’HTML prima di imparare come raschiare i dati utilizzando la programmazione VBA. Vi consiglio di imparare le conoscenze di base di HTML da questo sito se si sa nulla o poco di HTML. E da qui, puoi imparare quasi tutto ciò che riguarda il web scraping usando VBA. Questo articolo ti mostrerà solo due esempi.
Raschiare i dati da una pagina web
Supponiamo di voler estrarre il nome della società, l’indirizzo email e il nome del contatto da questa pagina web. Se apri questa pagina web, scoprirai che c’è un blocco di contatti in fondo. Figura 2.1 mostra il blocco di contatto e il codice sorgente corrispondente. Le informazioni in scatole rosse sono ciò di cui vogliamo avere bisogno. E quelli con una sottolineatura verde sono esattamente ciò che dobbiamo estrarre.
Figura 2.1
Il seguente codice può aiutarti a estrarre le informazioni richieste sopra e inserirle nel primo foglio di lavoro.
| Codice sorgente |    |
Sub Retrieve_Click () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Applicazione") ' Non vediamo la finestra del browser.Visible = False ' Apri la pagina web.Naviga "http://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736 "' Attendi mentre IE sta caricandofai mentre IE.readyState < > 4 O IE.Busy = True DoEvents Loop ' Recupera nome azienda, indirizzo e-mail & contact informationSet contactobj = IE.documento.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML MsgBox htext Se InStr(htext, "<p>Nome della società:") Allora ThisWorkbook.Fogli di lavoro (1).Cells (1, 1) = Split(Split(htext, "<p > Nome azienda: ")(1), "<br") (0) Fine If If InStr (htext, "mailto:") Allora ThisWorkbook.Fogli di lavoro (1).Celle(2, 1) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name:") Allora ThisWorkbook.Fogli di lavoro (1).Celle(3, 1) = Split(Split (htext, "<p>Nome: ")(1), "<br") (0) Fine se ThisWorkbook.Fogli di lavoro (1).Celle(4, 1) = IE.LocationURL questo libro di lavoro.Salva Set IE = Niente Set contactobj = Niente Fine Sub
“CIOÈ.documento.getElementsByClassName (“contact – details block dark”) ” può consentire di ottenere tutti gli elementi con il nome della classe-contact-details block dark. Questa pagina web elenca quasi tutte le proprietà e i metodi che possono essere utilizzati sugli elementi HTML. Puoi scegliere quello appropriato per il tuo problema.
La proprietà innerHTML consente di impostare o restituire il contenuto di un elemento HTML. Nel nostro caso, ha restituito il contenuto dell’elemento con il nome della classe “contact-details block dark” e lo ha impostato su una variabile htext. Il contenuto (Figura 2.2) può essere richiesto da ‘Msgbox htext’.
Figura 2.2
Si può vedere che il testo è ben strutturato. Ecco perché possiamo usare la funzione SPLIT per estrarre ciò che serve. Ad esempio, se prendiamo ” <p > Nome della società:”come delimitatore e formula” Split(htext, “<p>Nome azienda:”) (1)” può restituire l’intero testo dopo “<p>Nome azienda:”. Per questo testo appena restituito, possiamo prendere” < br ” come delimitatore e ottenere il testo prima del primo “<br” e questo è il nome della società. In sintesi, funzione SPLIT è uno strumento flessibile che è possibile utilizzare per estrarre quasi tutto. Altre funzioni utili includono LEN, INSTR, SINISTRA, DESTRA, MID, e SOSTITUIRE. Non voglio discutere in dettaglio qui.
Dopo aver fatto clic su OK in Figura 2.2, i dati richiesti possono essere importati dal Web in un foglio di lavoro Excel. Ad esempio, la cella A1 contiene il nome dell’azienda mentre la cella A4 contiene l’indirizzo della pagina Web dell’azienda.
Figura 2.3
Aggiungendo il codice seguente prima di salvare la cartella di lavoro, è possibile aggiungere un collegamento ipertestuale alla cella A4.
| Codice sorgente | |
'Aggiungi hyperlinkThisWorkbook.Fogli di lavoro (1).Hyperlink.Aggiungi ThisWorkbook.Fogli di lavoro (1).Celle (4, 1), questolibro del lavoro.Fogli di lavoro (1).Cellule(4, 1)
Se si fa clic sulla cella A4, è possibile rivisitare la pagina web. Questo è utile soprattutto quando si devono recuperare i dati per un sacco di aziende. Dal momento che è possibile fare clic su qualsiasi collegamento ipertestuale per rivisitare la pagina web corrispondente e aggiungere o aggiornare le informazioni manualmente durante la revisione successiva.
Figura 2.4
Interagisci con le pagine web
L’esempio precedente illustra come recuperare i dati da una pagina Web statica. Ma più che spesso, ci viene richiesto di estrarre hanno interagito con le pagine web al fine di recuperare una grande quantità di dati. Guarda la figura 3.1. Ti mostra come arrivare alla pagina web dell’esempio precedente. Si può vedere che ci sono un sacco di industrie e per ogni settore, ci sono un sacco di aziende. Ad esempio, ci sono 651 aziende nel settore agroalimentare. Cosa dobbiamo fare se vogliamo estrarre le informazioni di contatto di tutte le aziende di tutti i settori?
Figura 3.1
Bene, il punto chiave è che come far interagire VBA con le pagine web come quello che facciamo manualmente. Prendiamo il vantaggio S. W. I. S come esempio. In generale, speriamo che Excel possa fare clic su Agribusiness (pannello superiore di Figura 3.1) e quindi attivare IE per indirizzarci alla seconda pagina web. Nella seconda pagina web (pannello inferiore della Figura 3.1), Excel può fare clic su S. W. I. S Advantage, e quindi IE ci indirizza alla pagina come mostrato in Figura 2.1 in modo che possiamo recuperare le informazioni di contatto di S. W. I. S Advantage.
Dopo aver inserito il seguente codice in Visual Basic Editor e aver inviato il codice, vedrai che il tuo IE è aperto. Viene visualizzata la prima pagina web seguita dalla seconda pagina web. Qui imparerai come ottenere l’elemento dell’elenco a discesa, selezionare un’opzione e attivare un evento dopo aver selezionato l’opzione. “m = IE.documento.getElementsByTagName (“opzione”).Lunghezza-1 ” vi darà un numero totale di opzioni. Questo può essere usato per il ciclo successivo.
| Codice sorgente | |
Sub retrieve () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Applicazione") ' Vediamo la finestra del browser.Visible = True ' Apri la pagina web.Naviga "http://www.austrade.gov.au/international/buy#" 'Attendi mentre IE sta caricandofai mentre IE.Applicazione occupata.Attendere DateAdd ("s", 1, Ora) Applicazione loop.Attendere ( Ora + TimeValue("00:00:10")) 'Parte 1-Selezionare l'elenco a discesa e l'evento trigger dopo aver selezionato un optionSet selectobj = IE.documento.getElementsByTagName ("seleziona") m = IE.documento.getElementsByTagName ("opzione").Lunghezza-1 selectobj (0).selectedIndex = 1 selectobj (0).FireEvent ("onchange") ' Aspetta mentre IE sta caricandofare Mentre IE.readyState < > 4 O IE.Occupato = Vera applicazione.Attendere DateAdd ("s", 1, Ora) Applicazione loop.Attendere ( Ora + TimeValue("00:00:10")) Fine Sub
Questa parte del codice può indirizzarti alla pagina web mostrata in Figura 2.1 dopo aver fatto clic su Excel sul nome. I nomi di tutte le società sono inclusi nell’elemento con il nome della classe “Nome”. Searchobj è una raccolta e searchobj (i)può restituire il (i+1) esimo oggetto. Ad esempio, searchobj(1).Fare clic può consentire di visitare la pagina web per RIDLEY CORPORATION (Melbourne).
| Codice sorgente | |
'Parte 2-Selezionare società NameSet searchobj = IE.documento.getElementsByClassName ("Nome") searchobj (0).Fare clic su ' Attendi mentre IE sta caricandofare Mentre IE.readyState < > 4 O IE.Busy = Ciclo DoEvents vero
Infine, ecco l’intero codice che può mostrarti il processo di apertura di IE, navigazione di pagine web ed estrazione di dati. I dati estratti saranno gli stessi della Figura 2.4.
| Codice sorgente | |
Sub Retrieve () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Applicazione") ' Vediamo la finestra del browser.Visible = True ' Apri la pagina web.Naviga "http://www.austrade.gov.au/international/buy#" 'Attendi mentre IE sta caricandofai mentre IE.Applicazione occupata.Attendere DateAdd ("s", 1, Ora) Applicazione loop.Attendere ( Ora + TimeValue("00:00:10")) 'Parte 1-Selezionare l'elenco a discesa e l'evento trigger dopo aver selezionato un optionSet selectobj = IE.documento.getElementsByTagName ("seleziona") m = IE.documento.getElementsByTagName ("opzione").Lunghezza-1 selectobj (0).selectedIndex = 1 selectobj (0).FireEvent ("onchange") ' Aspetta mentre IE sta caricandofare Mentre IE.readyState < > 4 O IE.Occupato = Vera applicazione.Attendere DateAdd ("s", 1, Ora) Applicazione loop.Attendere ( Ora + TimeValue("00:00:10")) 'Parte 2-Selezionare società NameSet searchobj = IE.documento.getElementsByClassName ("Nome") searchobj (0).Fare clic su ' Attendi mentre IE sta caricandofare Mentre IE.readyState < > 4 O IE.Busy = True DoEvents Loop 'Parte 3-Recuperare il nome della società, indirizzo e-mail & contatto informationSet contactobj = IE.documento.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML Se InStr (htext, " < p > Nome della società:") Allora ThisWorkbook.Fogli di lavoro (1).Cells (1, 1) = Split(Split(htext, "<p > Nome azienda: ")(1), "<br") (0) Fine If If InStr (htext, "mailto:") Allora ThisWorkbook.Fogli di lavoro (1).Celle(2, 1) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name:") Allora ThisWorkbook.Fogli di lavoro (1).Celle(3, 1) = Split(Split (htext, "<p>Nome: ")(1), "<br") (0) Fine se ThisWorkbook.Fogli di lavoro (1).Celle(4, 1) = IE.LocationURL ' Aggiungere hyperlinkThisWorkbook.Fogli di lavoro (1).Hyperlink.Aggiungi ThisWorkbook.Fogli di lavoro (1).Celle (4, 1), questolibro del lavoro.Fogli di lavoro (1).Celle (4, 1) Fine Sub
In effetti, quello che dobbiamo fare è estrarre le informazioni di contatto per tutte le aziende di tutti i settori, quindi, dobbiamo utilizzare l’istruzione loop per completare questa attività. Di seguito è riportato il codice completo. E si può anche trovare il codice in Recuperare le informazioni di contatto per tutte le aziende.xlsm che puoi scaricare alla fine di questo articolo.
| Codice sorgente | |
Sub Retrieve () Per idex = 2 A 18 ' Creare InternetExplorerSet IE = CreateObject ("InternetExplorer.Applicazione") ' Vediamo la finestra del browser.Visible = False ' Apri la pagina web.Naviga "http://www.austrade.gov.au/international/buy#" 'Attendi mentre IE sta caricandofai mentre IE.Applicazione occupata.Attendere DateAdd ("s", 1, Ora) Applicazione loop.Attendere ( Ora + TimeValue("00:00:10")) idexn = idex-1 ' Parte 1-Selezionare dropdownSet selectobj = IE.documento.getElementsByTagName ("seleziona") m = IE.documento.getElementsByTagName ("opzione").Lunghezza-1 selectobj (0).selectedIndex = idexn selectobj (0).FireEvent ("onchange") ' Aspetta mentre IE sta caricandofare Mentre IE.readyState < > 4 O IE.Occupato = Vera applicazione.Attendere DateAdd ("s", 1, Ora) Applicazione loop.Attendere ( Ora + TimeValue("00:00:10")) wurl = IE.LocationURL tot = IE.documento.getElementsByClassName ("SearchTotal") (0).innerHTML pg = Int (tot / 25) + 1 Max = (tot Mod 25) - 1 'Parte 2 - Seleziona Classe = "Nome"a = 2 Per j = 1 A pg Se j = 1 Quindi IE.Naviga (wurl) Altro IE.Naviga (wurl &" & pg= " & j) Termina se fai mentre IE.Applicazione occupata.Attendere DateAdd ("s", 1, Ora) Loop Se j < > pg Quindi Per i = 1 a 24 Set searchobj = IE.documento.getElementsByClassName ("Nome") searchobj (i).Fare clic su ' Attendi mentre IE sta caricandofare Mentre IE.readyState < > 4 O IE.Busy = True DoEvents Loop 'Parte 3-Recuperare il nome della società, indirizzo e-mail & contatto informationSet contactobj = IE.documento.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML Questo libro di lavoro.Fogli di lavoro (idex).Celle(a, 1) = j ThisWorkbook.Fogli di lavoro (idex).Cells (a, 2) = a-1 If InStr(htext, "<p>Nome della società:") Allora ThisWorkbook.Fogli di lavoro (idex).Cells (a, 3) = Split (Split (htext, " < p > Nome azienda: ")(1), "<br") (0) Fine If If InStr (htext, "mailto:") Allora ThisWorkbook.Fogli di lavoro (idex).Celle (a, 4) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name:") Allora ThisWorkbook.Fogli di lavoro (idex).Celle (a, 5) = Split(Split (htext, "<p>Nome: ")(1), "<br") (0) Fine se ThisWorkbook.Fogli di lavoro (idex).Celle(a, 6) = IE.LocationURL IE.GoBack Fare mentre IE.Applicazione occupata.Attendere DateAdd ("s", 1, Ora) Loop a = a + 1 Successivo i Else Per i = 0 a Max Set searchobj = IE.documento.getElementsByClassName ("Nome") searchobj (i).Fare clic su ' Attendi mentre IE sta caricandofare Mentre IE.readyState < > 4 O IE.Busy = True DoEvents Loop 'Parte 3-Recuperare il nome della società, indirizzo e-mail & contatto informationSet contactobj = IE.documento.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML Questo libro di lavoro.Fogli di lavoro (idex).Celle(a, 1) = j ThisWorkbook.Fogli di lavoro (idex).Cells (a, 2) = a-1 If InStr(htext, "<p>Nome della società:") Allora ThisWorkbook.Fogli di lavoro (idex).Cells (a, 3) = Split (Split (htext, " < p > Nome azienda: ")(1), "<br") (0) Fine If If InStr (htext, "mailto:") Allora ThisWorkbook.Fogli di lavoro (idex).Celle (a, 4) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name:") Allora ThisWorkbook.Fogli di lavoro (idex).Celle (a, 5) = Split(Split (htext, "<p>Nome: ")(1), "<br") (0) Fine se ThisWorkbook.Fogli di lavoro (idex).Celle(a, 6) = IE.LocationURL questo libro di lavoro.Fogli di lavoro (idex).Hyperlink.Aggiungi ThisWorkbook.Fogli di lavoro (idex).Celle (a, 6), questolibro di lavoro.Fogli di lavoro (idex).Celle(a, 6) IE.GoBack Fare mentre IE.Applicazione occupata.Attendere DateAdd ("s", 1, Ora) Loop a = a + 1 Successivo alla fine se ThisWorkbook.Save Next j Set IE = Niente Set contactobj = Niente Next idex End Sub
L’unico punto che ho bisogno di spiegare è illustrato nella Figura 3.2. Un web può elencare solo fino a 25 aziende. Quando il numero totale di aziende è superiore a 25, ci sarà più di una pagina. La figura 3.2 mostra che esiste una regola per ottenere l’indirizzo delle pagine dopo la prima pagina. È il risultato dell’indirizzo concatenante della prima pagina, “& pg= ” e del numero di pagina effettivo. E per tutte le pagine prima dell’ultima pagina, il numero totale dell’oggetto è 25. “IE.documento.getElementsByClassName (“SearchTotal”) (0).innerHTML ” può restituire il numero totale di aziende all’interno di un settore. Nel nostro caso, sarà 651. “Int (tot / 25) + 1” può ottenere il numero totale di pagine. E “Max = (tot Mod 25) – 1” può restituire il numero massimo di aziende nell’ultima pagina. Mi fermerò qui e ti lascerò per capire come applicare questa idea al codice. È un approccio molto migliore per cogliere il codice. Puoi lasciare un commento se hai domande.
Figura 3.2
Qui si mostra parte della finale excel. Le informazioni di contatto per tutte le aziende all’interno di un settore sono riunite in un unico foglio di lavoro.
Figura 3.3
- Come estrarre automaticamente i dati dal sito Web in Excel?
- Importa Dati (Frase, Paragrafi, Tabelle, Commenti), da Word a Excel
- 6 Migliori VBA di Excel Libri di Programmazione (Per Principianti & gli Utenti Avanzati)
- Imparare il VBA di Excel Programmazione & Macro (Tutorial – passo-Passo)
- VBA di Excel Codifica Consigli
- Cosa Si Può Fare con VBA
- Introduzione alle Macro VBA
Scarica il file di lavoro
Scaricare il file dal link qui sotto.
Pull-Dati-da-Web-a-Excel.rar