jak importować dane ze strony internetowej do Excela?
powszechnie wiadomo, że World Wide Web zawiera ogromne przydatne dane. Musimy jednak zaimportować dane do programu Microsoft Excel przed wykonaniem jakiejkolwiek analizy. Istnieją dwie metody, których możesz użyć do wykonania tego rodzaju zadania.
Pobierz dane zewnętrzne z sieci ręcznie
Załóżmy, że chcesz pobrać przychody z najlepszych filmów zarabiania pieniędzy z tej strony, tutaj pokazuje, jak to zrobić łatwo.
Otwórz Microsoft Excel i kliknij kartę Dane, w grupie Pobierz dane zewnętrzne Kliknij z sieci. Po pojawieniu się nowego okna dialogowego zapytania internetowego skopiuj adres internetowy (http://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) do pola adresu i kliknij przycisk Idź. Program Excel rozpocznie pobieranie strony internetowej, jak pokazano na rysunku 1.1. Wystarczy kliknąć na nie, Jeśli pojawi się okno ostrzegania o błędzie skryptu, które jest podobne do tego na poniższym rysunku. Pole zniknie i nie ma wpływu na proces importu.
Rys. 1.1
w żółtym polu W prawym górnym rogu okna dialogowego nowe zapytanie internetowe znajduje się strzałka. Klikając na niego, możesz określić, czy wyświetlać podobne ikony przed tabelami, czy nie. Na przykład, nie ma ikony strzałki obok tabeli w lewym panelu rysunku 1.2. I nie będzie ikona (w prawym panelu) po kliknięciu na przycisk strzałki, aby wyświetlić ikony.
rys 1.2
kliknij ikonę strzałki obok tabel, które chcesz wybrać. Ikony i tabele ulegną zmianie i będą podobne do tych pokazanych na lewym panelu rysunku 1.3. Po kliknięciu Importuj zostanie wyświetlone okno dialogowe Importuj dane. Wypełnij zakres (od kolumny A do kolumny H w naszym przypadku), w którym chcesz umieścić Dane i kliknij OK.
rysunek 1.3
dane zostaną zaimportowane do programu Excel Po kliknięciu przycisku Ok. Jeśli klikniesz prawym przyciskiem myszy dowolną komórkę w tabeli i wybierzesz odśwież, program Excel wyjdzie i wyciągnie Najnowsze dane ze strony internetowej.
rys. 1.4
możesz nawet ustawić odświeżanie danych zapytania w dowolny sposób. Wystarczy kliknąć prawym przyciskiem myszy dowolną komórkę w tabeli, wybierz Właściwości zakresu danych. W wyświetlonym oknie dialogowym Właściwości zewnętrznego zakresu danych możesz zmienić ustawienia odświeżania, aby zmienić styl. Na przykład można określić zapytanie, które ma być odświeżane co 60 minut lub kiedy plik jest otwierany.
zeskrobuj dane za pomocą programowania VBA
programowanie VBA może być używane do zeskrobywania danych ze strony internetowej. Jest to o wiele bardziej użyteczne, a jednocześnie trudne w porównaniu z pierwszym podejściem. Co więcej, musisz zrozumieć, czym jest HTML, zanim nauczysz się skrobać dane za pomocą programowania VBA. Polecam Ci nauczyć się podstawowej wiedzy o HTML z tej strony, jeśli nie wiesz nic lub mało o HTML. Stąd możesz dowiedzieć się prawie wszystkiego, co związane jest ze skrobaniem stron internetowych za pomocą VBA. Ten artykuł pokaże tylko dwa przykłady.
skrobanie danych z jednej strony internetowej
Załóżmy, że chcemy wyodrębnić nazwę firmy, adres e-mail i nazwę kontaktu z tej strony internetowej. Jeśli otworzysz tę stronę, zobaczysz, że na dole znajduje się blok kontaktów. Rysunek 2.1 pokazuje blok styków i odpowiadający mu kod źródłowy. Informacje w czerwonych polach są tym, czego chcemy. A te z Zielonym podkreśleniem są dokładnie tym, czego potrzebujemy.
Rys. 2.1
poniższy kod może pomóc w wyodrębnieniu powyższych wymaganych informacji i umieszczeniu ich w pierwszym arkuszu roboczym.
| kod źródłowy |    |
Sub Retrieve_Click () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Application") ' Nie oglądajmy Wind przeglądarki.Visible = False ' Otwórz stronę.Nawiguj "http://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736 "' poczekaj na załadowanie IE podczas IE.readyState < > 4 lub IE.Busy = True DoEvents Loop ' Pobierz nazwę firmy, adres e-mail & dane kontaktowe set contactobj = IE.dokument.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML MsgBox htext If InStr(htext, " < p> Nazwa firmy:") Then ThisWorkbook.Arkusze robocze (1).Cells (1, 1) = Split(Split(htext, "< p > Nazwa firmy: ")(1), "<br") (0) End If If InStr(htext, "mailto:") Then ThisWorkbook.Arkusze robocze (1).Cells(2, 1) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, " < P>Name:") ThisWorkbook.Arkusze robocze (1).Cells (3, 1) = Split (Split (htext, " < p > Nazwa: ")(1), "<br") (0) End If ThisWorkbook.Arkusze robocze (1).Komórki (4, 1) = IE.LocationURL ThisWorkbook.Save Set IE = Nothing Set contactobj = Nothing End Sub
„IE.dokument.getElementsByClassName („contact-details Block dark”) ” pozwala uzyskać wszystkie elementy z klasą name – contact-details block dark. Ta strona internetowa zawiera listę prawie wszystkich właściwości i metod, które mogą być użyte na elementach HTML. Możesz wybrać odpowiedni dla własnego problemu.
właściwość innerHTML umożliwia ustawienie lub zwrócenie zawartości elementu HTML. W naszym przypadku zwrócił zawartość elementu o nazwie klasy „contact-details block dark” i ustawił ją na zmienną htext. Zawartość (rysunek 2.2) może być wyświetlana przez „Msgbox htext”.
rys. 2.2
widać, że tekst jest dobrze zorganizowany. Dlatego możemy użyć funkcji SPLIT, aby wyodrębnić to, czego potrzebujesz. Na przykład, jeśli weźmiemy „< p> Nazwa firmy:”jako separator i formuła” Split (htext, „<p>Nazwa firmy:”) (1) „może zwrócić cały tekst po”<P>Nazwa firmy:”. Dla tego nowo zwróconego tekstu, możemy wziąć „<br” jako ogranicznik i uzyskać tekst przed pierwszym „< br” i to jest nazwa firmy. Podsumowując, funkcja SPLIT jest elastycznym narzędziem, za pomocą którego można wyodrębnić prawie wszystko. Inne przydatne funkcje to LEN, INSTR, LEFT, RIGHT, MID i REPLACE. Nie będę tutaj szczegółowo omawiał.
po kliknięciu OK na rysunku 2.2, wymagane dane można importować z internetu do arkusza Excel. Na przykład komórka A1 zawiera nazwę Firmy, podczas gdy komórka A4 zawiera adres strony internetowej firmy.
rys. 2.3
dodając poniższy kod przed zapisaniem skoroszytu, możesz dodać hiperłącze do komórki A4.
| kod źródłowy | |
'Dodaj hyperlinkThisWorkbook.Arkusze robocze (1).Hiperłącza.Dodaj ThisWorkbook.Arkusze robocze (1).Cells (4, 1), ThisWorkbook.Arkusze robocze (1).Komórki(4, 1)
jeśli klikniesz na komórkę A4, możesz ponownie odwiedzić stronę internetową. Jest to przydatne zwłaszcza, gdy musisz pobrać dane dla wielu firm. Ponieważ możesz kliknąć dowolne hiperłącze, aby ponownie odwiedzić odpowiednią stronę internetową i dodać lub zaktualizować wszelkie informacje ręcznie podczas późniejszego przeglądu.
rys. 2.4
Interakcja ze stronami internetowymi
powyższy przykład tylko ilustruje sposób pobierania danych z jednej statycznej strony internetowej. Ale więcej niż często, jesteśmy zobowiązani do wyodrębnienia mają interakcję ze stronami internetowymi w celu pobrania dużej ilości danych. Spójrz na rysunek 3.1. Pokazuje on jak dostać się na stronę powyższego przykładu. Widać, że jest wiele branż i dla każdej branży jest wiele firm. Na przykład w branży Agrobiznesu jest 651 firm. Co powinniśmy zrobić, jeśli chcemy wyodrębnić dane kontaktowe wszystkich firm ze wszystkich branż?
rys. 3.1
cóż, kluczowym punktem jest to, jak sprawić, aby VBA współdziałał ze stronami internetowymi, takimi jak to, co robimy ręcznie. Weźmy przewagę S. W. I. S jako przykład. Ogólnie rzecz biorąc, mamy nadzieję, że Excel może kliknąć Agrobiznes (górny panel rysunku 3.1), a tym samym wywołać IE skierować nas na drugą stronę internetową. Na drugiej stronie internetowej (dolny panel z rysunku 3.1), Excel może kliknąć na S. W. I. S zaletą, a następnie IE kieruje nas do strony, Jak pokazano na rysunku 2.1, abyśmy mogli pobrać informacje kontaktowe S. W. I. S zaletą.
po wprowadzeniu poniższego kodu do Edytora Visual Basic i przesłaniu kodu, zobaczysz, że twój IE jest otwarty. Pojawia się pierwsza strona internetowa, a następnie druga strona internetowa. Tutaj dowiesz się, jak uzyskać element rozwijanej listy, wybrać opcję i wywołać zdarzenie po wybraniu opcji. „m = IE.dokument.getElementsByTagName(„opcja”).Długość-1″ daje całkowitą liczbę opcji. Można to wykorzystać do następnej pętli.
| kod źródłowy | |
Sub retrieve () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Application") ' zobaczmy okno przeglądarki.Visible = True ' Otwórz stronę.Nawiguj "http://www.austrade.gov.au/international/buy # "' poczekaj, aż IE się załaduje, podczas gdy IE.Zajęta Aplikacja.Wait DateAdd ("s", 1, Now) aplikacja pętli.Wait (Now + TimeValue("00:00:10")) 'Część 1-Wybierz listę rozwijaną i wyzwalaj zdarzenie po wybraniu jednej opcji selectobj = IE . dokument.getElementsByTagName ("select") m = IE.dokument.getElementsByTagName("opcja").Długość-1 Wybierz(0).selectedIndex = 1 selectobj(0).FireEvent ("onchange") ' poczekaj, aż IE się załaduje, podczas gdy IE.readyState < > 4 lub IE.Busy = True Application.Wait DateAdd ("s", 1, Now) aplikacja pętli.Wait (Now + TimeValue("00:00:10")) End Sub
ta część kodu może skierować cię do strony internetowej pokazanej na rysunku 2.1 Po kliknięciu Excela na imię. Wszystkie nazwy firm są zawarte w elemencie z nazwą klasy „Nazwa”. Searchobj jest zbiorem i searchobj(i) może zwrócić obiekt (i+1)th. Na przykład searchobj(1).Kliknij, aby umożliwić Ci odwiedzenie strony internetowej RIDLEY CORPORATION (Melbourne).
| kod źródłowy | |
'część 2-Wybierz zestaw nazw firmy searchobj = IE.dokument.getElementsByClassName ("Nazwa") searchobj (0).Kliknij " poczekaj, aż IE się załaduje, podczas gdy IE.readyState < > 4 lub IE.Busy = True DoEvents Loop
wreszcie, oto cały kod, który może pokazać proces otwierania IE, przeglądania stron internetowych i wyodrębniania danych. Uzyskane dane będą takie same jak na rysunku 2.4.
| kod źródłowy | |
Sub Retrieve () ' Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Application") ' zobaczmy okno przeglądarki.Visible = True ' Otwórz stronę.Nawiguj "http://www.austrade.gov.au/international/buy # "' poczekaj, aż IE się załaduje, podczas gdy IE.Zajęta Aplikacja.Wait DateAdd ("s", 1, Now) aplikacja pętli.Wait (Now + TimeValue("00:00:10")) 'Część 1-Wybierz listę rozwijaną i wyzwalaj zdarzenie po wybraniu jednej opcji selectobj = IE . dokument.getElementsByTagName ("select") m = IE.dokument.getElementsByTagName("opcja").Długość-1 Wybierz(0).selectedIndex = 1 selectobj(0).FireEvent ("onchange") ' poczekaj, aż IE się załaduje, podczas gdy IE.readyState < > 4 lub IE.Busy = True Application.Wait DateAdd ("s", 1, Now) aplikacja pętli.Wait (Now + TimeValue("00:00:10")) 'Część 2-Wybierz zestaw nazw firmy searchobj = IE.dokument.getElementsByClassName ("Nazwa") searchobj (0).Kliknij " poczekaj, aż IE się załaduje, podczas gdy IE.readyState < > 4 lub IE.Busy = True DoEvents Loop ' Część 3-Odzyskaj nazwę firmy, adres e-mail & dane kontaktowe set contactobj = IE.dokument.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML If InStr(htext, " < p> Nazwa firmy:") Then ThisWorkbook.Arkusze robocze (1).Cells (1, 1) = Split(Split(htext, "< p > Nazwa firmy: ")(1), "<br") (0) End If If InStr(htext, "mailto:") Then ThisWorkbook.Arkusze robocze (1).Cells(2, 1) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, " < P>Name:") ThisWorkbook.Arkusze robocze (1).Cells (3, 1) = Split (Split (htext, " < p > Nazwa: ")(1), "<br") (0) End If ThisWorkbook.Arkusze robocze (1).Komórki (4, 1) = IE.LocationURL ' Dodaj hyperlinkThisWorkbook.Arkusze robocze (1).Hiperłącza.Dodaj ThisWorkbook.Arkusze robocze (1).Cells (4, 1), ThisWorkbook.Arkusze robocze (1).Komórki (4, 1) End Sub
w rzeczywistości musimy wyodrębnić dane kontaktowe wszystkich firm ze wszystkich branż, dlatego musimy wykonać polecenie use for loop, aby wykonać to zadanie. Poniżej znajduje się Pełny kod. Możesz również znaleźć kod w sekcji Pobieranie informacji kontaktowych dla wszystkich firm.xlsm, który można pobrać na końcu tego artykułu.
| kod źródłowy | |
Sub Retrieve() For idex = 2 To 18 'Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Application") ' zobaczmy okno przeglądarki.Visible = False ' Otwórz stronę.Nawiguj "http://www.austrade.gov.au/international/buy # "' poczekaj, aż IE się załaduje, podczas gdy IE.Zajęta Aplikacja.Wait DateAdd ("s", 1, Now) aplikacja pętli.Wait (Now + TimeValue("00:00:10")) idexn = idex-1 ' Część 1-Select dropdownSet selectobj = IE.dokument.getElementsByTagName ("select") m = IE.dokument.getElementsByTagName("opcja").Długość-1 Wybierz(0).selectedIndex = idexn selectobj(0).FireEvent ("onchange") ' poczekaj, aż IE się załaduje, podczas gdy IE.readyState < > 4 lub IE.Busy = True Application.Wait DateAdd ("s", 1, Now) aplikacja pętli.Wait (Now + TimeValue("00:00:10")) wurl = IE.LocationURL tot = IE.dokument.getElementsByClassName("SearchTotal") (0).innerHTML pg = Int (tot / 25) + 1 Max = (tot Mod 25) - 1 ' Part 2-Select Class = "Name" A = 2 For j = 1 To pg If J = 1 Then IE.Nawiguj (wurl) else IE.Navigate (wurl & "&pg = " & j) End If do While IE.Zajęta Aplikacja.Wait DateAdd ("s", 1, Now) Loop If j <> pg Then for i = 1 To 24 Set searchobj = IE.dokument.getElementsByClassName ("Nazwa") searchobj (i).Kliknij " poczekaj, aż IE się załaduje, podczas gdy IE.readyState < > 4 lub IE.Busy = True DoEvents Loop ' Część 3-Odzyskaj nazwę firmy, adres e-mail & dane kontaktowe set contactobj = IE.dokument.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML ThisWorkbook.Arkusze robocze (idex).Cells (a, 1) = J ThisWorkbook.Arkusze robocze (idex).Cells (a, 2) = A-1 If InStr (htext, " < p> Nazwa firmy:") Then ThisWorkbook.Arkusze robocze (idex).Cells (a, 3) = Split (Split (htext, " < p > Nazwa firmy: ")(1), "<br") (0) End If If InStr(htext, "mailto:") Then ThisWorkbook.Arkusze robocze (idex).Cells (a, 4) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, " < P>Name:") ThisWorkbook.Arkusze robocze (idex).Cells (a, 5) = Split (Split (htext, " < p > Nazwa: ")(1), "<br") (0) End If ThisWorkbook.Arkusze robocze (idex).Komórki (a, 6) = IE.LocationURL IE.GoBack zrobić podczas IE.Zajęta Aplikacja.Wait DateAdd ("s", 1, Now) Loop A = a + 1 Next I Else For i = 0 to Max Set searchobj = IE.dokument.getElementsByClassName ("Nazwa") searchobj (i).Kliknij " poczekaj, aż IE się załaduje, podczas gdy IE.readyState < > 4 lub IE.Busy = True DoEvents Loop ' Część 3-Odzyskaj nazwę firmy, adres e-mail & dane kontaktowe set contactobj = IE.dokument.getElementsByClassName ("contact-details block dark") htext = contactobj (0).innerHTML ThisWorkbook.Arkusze robocze (idex).Cells (a, 1) = J ThisWorkbook.Arkusze robocze (idex).Cells (a, 2) = A-1 If InStr (htext, " < p> Nazwa firmy:") Then ThisWorkbook.Arkusze robocze (idex).Cells (a, 3) = Split (Split (htext, " < p > Nazwa firmy: ")(1), "<br") (0) End If If InStr(htext, "mailto:") Then ThisWorkbook.Arkusze robocze (idex).Cells (a, 4) = Split(Split(htext, "mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, " < P>Name:") ThisWorkbook.Arkusze robocze (idex).Cells (a, 5) = Split (Split (htext, " < p > Nazwa: ")(1), "<br") (0) End If ThisWorkbook.Arkusze robocze (idex).Komórki (a, 6) = IE.LocationURL ThisWorkbook.Arkusze robocze (idex).Hiperłącza.Dodaj ThisWorkbook.Arkusze robocze (idex).Cells (a, 6), ThisWorkbook.Arkusze robocze (idex).Komórki (a, 6) tj.GoBack zrobić podczas IE.Zajęta Aplikacja.Wait DateAdd ("s", 1, Now) Loop a = a + 1 Next I End If ThisWorkbook.Save Next J Set IE = Nothing Set contactobj = Nothing Next idex End Sub
jedyny punkt jaki muszę wyjaśnić jest zilustrowany na rysunku 3.2. W jednej sieci można wymienić tylko 25 firm. Gdy łączna liczba firm jest większa niż 25, będzie więcej niż jedna strona. Rysunek 3.2 pokazuje, że istnieje reguła, aby uzyskać adres stron po pierwszej stronie. Jest to wynik połączenia adresu pierwszej strony, „& pg=” i rzeczywistego numeru strony. I dla wszystkich stron przed ostatnią stroną, całkowity numer obiektu wynosi 25. „IE.dokument.getElementsByClassName(„SearchTotal”) (0).innerHTML ” może zwrócić całkowitą liczbę firm w branży. W naszym przypadku będzie to 651. „Int (tot / 25) + 1” może uzyskać całkowitą liczbę stron. A „Max = (tot Mod 25) – 1” może zwrócić maksymalną liczbę firm na ostatniej stronie. Zatrzymam się tutaj i zostawię was, abyście wymyślili, jak zastosować ten pomysł do kodu. Jest to znacznie lepsze podejście do zrozumienia kodu. Możesz zostawić komentarz, jeśli masz pytania.
rys. 3.2
tutaj pokazuje część końcowego programu excel. Informacje kontaktowe dla wszystkich firm w jednej branży są zestawione w jednym arkuszu roboczym.
rys. 3.3
- jak automatycznie wyodrębnić dane ze strony internetowej do Excela?
- Importuj dane (zdanie, akapity, tabele, komentarze) z programu Word do programu Excel
- 6 najlepszych książek o programowaniu Excel VBA (dla początkujących & zaawansowanych użytkowników)
- Naucz się programowania Excel VBA & makra (bezpłatny samouczek – krok po kroku)
- Wskazówki dotyczące kodowania Excel VBA
- co możesz zrobić z VBA
- Wprowadzenie do makr VBA
Pobierz plik roboczy
Pobierz plik roboczy z linku poniżej.
Pull-Data-from-Web-to-Excel.rar