Shabakadda^(Web) xoqitaanka waa ficil ka soo saarida xogta, macluumaadka, ama sawirada mareegaha iyada oo la adeegsanayo hab otomaatig ah. Ka fakar^(Think) sida koobi oo ku dheji si toos ah.

Waxaan u qornaa ama isticmaalnaa app si aan u galno mareegaha aan rabno oo aan nuqul ka sameyno waxyaabaha gaarka ah ee aan ka rabno mareegahaas. Aad bay uga saxsan tahay soo dejinta degel dhan^{(downloading an entire website)} .

Sida qalab kasta, xoqitaanka shabakada waxaa loo isticmaali karaa wanaag ama xumaan. Qaar ka mid ah sababaha ugu wanagsan ee lagu xoqo boggaga internetka ayaa lagu qiimeyn doonaa mashiinka raadinta iyadoo lagu saleynayo nuxurkiisa, isbarbardhigga qiimaha, ama la socoshada macluumaadka suuqa saamiyada. Xitaa waxaad u isticmaali kartaa sidii qalab cilmi baaris oo noocyo kala duwan ah.

Sideen ku xoqi karaa mareegaha Excel ?

Rumayso ama ha rumaysan, Excel wuxuu awood u lahaa inuu xogta ka soo saaro boggaga internetka muddo dheer, ugu yaraan tan iyo Excel 2003 . Waa uun in xoqitaanka shabakadu ay tahay wax dadka intooda badani aanay ka fikirin, iska daa inay ka fikiraan isticmaalka barnaamijka faafinta si ay shaqada u qabtaan. Laakiin waa la yaab fudud, oo awood leh. Aynu barano sida loo sameeyo anagoo samaynayna ururinta furaha kiiboodhka ee Microsoft Office .

Soo hel bogagga aad rabto inaad xoqdo

Waxa ugu horreeya ee aan sameyn doono waa hel boggaga internetka ee gaarka ah ee aan rabno inaan ka helno macluumaadka. Aan tagno isha oo ka raadin https://support.office.com/ . Waxaan adeegsan doonnaa ereyga raadinta "Gaab-gaabyada inta badan la isticmaalo". Waxaan si gaar ah uga dhigi karnaa annaga oo adeegsanayna magaca app-ka gaarka ah, sida Outlook , Excel , Word , iyo wixii la mid ah. Waxaa laga yaabaa inay fikrad fiican tahay in la calaamadeeyo bogga natiijooyinka si aan si fudud halkaas ugu soo laabanno.

Guji^(Click) natiijada raadinta, "Keyboard shortcuts in Excel for Windows". Markaad boggaas gasho, hel liiska noocyada Excel oo guji Noocyada Cusub^{(Newer Versions)} . Hadda waxaan la shaqeyneynaa kuwa ugu dambeeyay iyo kuwa ugu waaweyn.

Waxaan ku noqon karnaa bogga natiijooyinka raadinta oo aan u furno natiijooyinka dhammaan barnaamijyada kale ee Office tabs iyaga u gaar ah oo ku calaamadee. Waa fikrad wanaagsan, xitaa layligan. Tani waa meesha ay dadka badankiisu ku joojin lahaayeen ururinta jidadka gaaban ee Xafiiska^(Office) , laakiin annagu maaha. Waxaan ku dhejin doonaa Excel si aan u samayno wax kasta oo aan rabno iyaga, markasta oo aan rabno.

Fur Excel oo xoq

Fur Excel oo bilow buug shaqo oo cusub. U keydi buugga shaqada sidii Hab-gaabyada Xafiiska^{(Office Shortcuts)} . Haddii aad leedahay OneDrive, ku keydi halkaas si habka AutoSave uu u shaqeeyo.

Marka buugga shaqada la keydiyo, dhagsii xogta^(Data) tab.

Lifaaqa tabka Xogta^(Data) , guji From Web .

Daaqadda Wizard Webka^{(From Web )} ayaa furmi doonta. Tani waa halka aan ku dhejineyno cinwaanka shabakada ama URL -ka bogga internetka ee aan rabno inaan ka xoqno xogta. U beddel shabakada internetka oo nuqul^(copy) ka samee URL.

Ku dheji URL-ka goobta URL- ka ee Wizard Webka . ^{(From Web)}Waxa aan dooran karnaa in aan u isticmaalno qaabka aasaasiga ah^(Basic) ama sare^(Advanced) . Habka Sare^(Advanced) wuxuu na siinayaa doorashooyin badan oo badan oo ku saabsan sida loo galo xogta shabakada. Layligan, waxaan u baahanahay kaliya qaabka aasaasiga ah. Guji ^(Click) OK _

Excel hadda wuxuu isku dayi doonaa inuu ku xidhmo mareegaha. Tani waxay qaadan kartaa dhowr ilbiriqsi Waxaan arki doonaa daaqad horumareed, haddii ay dhacdo.

Daaqadda Navigator ayaa furmi doonta, waxaanan arki doonaa liis jadwal ah oo ka socda mareegaha dhanka bidix. Markaan mid doorano, waxaan arki doonaa horudhac miis oo dhanka midig ah. Aynu doorano shaxda gaaban ee Inta badan la isticmaalo .^{(Frequently used shortcuts )}

Waxaan gujin karnaa tab View Web^{(Web View)} si aan u aragno mareegaha dhabta ah, haddii aan u baahanahay inaan hareeraha ka eegno miiska aan rabno. Markaan helno, waan gujin karnaa waxaana loo dooran doonaa soo dejinta.

Hadda, waxaan gujina badhanka Load ee hoose ee daaqadan. Waxaa jira doorashooyin kale oo aan dooran karno, kuwaas oo aad u adag oo ka baxsan baaxadda samaynta xoqitaankayada ugu horreeya. Kaliya la soco inay jiraan. Awoodaha xoqitaanka shabakadda ee Excel waa kuwo aad u xoog badan.

Shaxda shabkada waxa lagu shubi doonaa Excel dhawr ilbiriqsi kadib. Waxaan arki doonaa xogta dhanka bidix, halkaasoo nambarka 1 uu ku yaal sawirka hoose. Nambarka 2 ayaa iftiiminaya weydiinta^(Query) loo isticmaalay in xogta laga helo mareegaha. Marka aan ku hayno su'aalo badan buuga shaqada, tani waa meesha aan doorano midka aan u baahanahay inaan isticmaalno.

Ogaysii^(Notice) in xogtu u soo gasho xaashida xaashida sidii miiska Excel . Horay ayaa loo dejiyay si aan awoodno inaan u kala saarno xogta.

Waxaan ku celin karnaa habkan dhammaan boggaga internetka ee leh furayaasha xafiiska^(Office) ee aan rabno Outlook , Word , Access , PowerPoint , iyo barnaamij kasta oo kale oo Office ah.

Ku -haynta Xogta la xoqay ee hadda^{(Scraped Data Current)} gudaha Excel

Gunno ahaan adiga, waxaanu ku baran doonaa sida xogtayada la xoqay looga dhigo mid cusub oo Excel ah . Tani waa hab weyn oo lagu muujin karo sida ay u awood badan tahay Excel u xoqida xogta. Xitaa tan, waxaan sameyneynaa kaliya xoqitaanka aasaasiga ah ee Excel samayn karo.

Tusaale ahaan, aan isticmaalno bogga macluumaadka saamiyada sida https://www.cnbc.com/stocks/ .

U gudub wixii aan horay u sameynay oo koobi ka samee oo ku dheji URL cusub barta ciwaanka.

Waxaad tagi doontaa daaqada Navigator oo waxaad arki doontaa jaantusyada la heli karo. Aynu doorano tusmooyinka saamiyada Maraykanka ee waaweyn^{(Major U.S. Stock Indices)} .

Marka xogta la xoqo waxaan arki doonaa xaashida faafinta soo socota.

Dhanka midig, waxaan ku aragnaa weydiinta Tusmooyinka Saamiyada Weyn ee Mareykanka^{(Major U.S. Stock Indexes)} . Dooro^(Select) taas si loo iftiimiyo. Hubi^(Make) in aan ku jirno tab Tools Table iyo aagga ^{(Table Tools)}Naqshadda^(Design) . Kadibna ku dhufo falaarta hoose ee hoos timaada Refresh . Kadibna guji Connection Properties .

Daaqadda Guryaha Weydiinta, ee hoos timaada tab Isticmaalka ^{(Query Properties )},^(Usage) waxaan xakameyn karnaa sida macluumaadkani u soo cusboonaado. Waxaan dejin karnaa waqti gaar ah si aan u cusboonaysiinno, ama aan u cusboonaysiinno marka aan furno buugga shaqada marka xigta, ama aan ku cusboonaysiinno gadaasha, ama wax kasta oo kuwan la isku daro. Marka aan doorano waxa aan u baahanahay, dhagsii OK si aad u xirto daaqada oo aad u sii wado.

Waa kaas! Hadda waxaad la socon kartaa sicirka saamiyada, buundooyinka isboortiga, ama xog kasta oo kale oo si joogta ah uga beddesha xaashida Excel . Haddii aad ku fiican tahay isla'egyada iyo hawlaha Excel^{(Excel equations and functions)} , waxaad ku samayn kartaa ku dhawaad ​​wax kasta oo aad rabto xogta.

Waxaa laga yaabaa inaad isku daydo inaad aqoonsato isbeddellada saamiyada, socodsiiso barkad isboorti oo khiyaali ah goobta shaqada, ama laga yaabee inaad la socoto cimilada. Yaa garanaya? Male-awaalkaaga iyo xogta laga heli karo internetka^(Internet) , ayaa ah xadka kaliya.

Use Excel as a Tool to Copy Data from the Web

Web scraping is the act of extracting data, information, or images from a webѕite using an automated method. Think of it as copy and рaѕte on full automatic.

We either write or use an app to go to the websites we want it to and make a copy of the specific things we want from those websites. It’s much more precise than downloading an entire website.

Like any tool, web scraping can be used for good or evil. Some of the better reasons for scraping websites would be ranking it in a search engine based on its content, price comparison shopping, or monitoring stock market information. You might even use it as a research tool of sorts.

How Can I Scrape Websites with Excel?

Believe it or not, Excel has had the ability to extract data from websites for a long time, at least since Excel 2003. It’s just that web scraping is something most people don’t think of, let alone think of using a spreadsheet program to do the job. But it’s surprisingly easy, and powerful. Let’s learn how it’s done by making a collection of Microsoft Office keyboard shortcuts.

Find the Sites You Want to Scrape

The first thing we’re going to do is find the specific web pages from which we want to get information. Let’s go to the source and search at https://support.office.com/. We’re going to use the search term “frequently used shortcuts”. We can make it more specific by using the name of the specific app, like Outlook, Excel, Word, and so on. It may be a good idea to bookmark the results page so we can easily get back there.

Click on the search result, “Keyboard shortcuts in Excel for Windows”. Once on that page, find the list of Excel versions and click on Newer Versions. Now we’re working with the latest and greatest.

We could go back to our search results page and open the results for all the other Office apps in their own tabs and bookmark them. It’s a good idea, even for this exercise. This is where most people would stop in collecting Office shortcuts, but not us. We’re going to put them in Excel so we can do whatever we want with them, whenever we want.

Open Excel and Scrape

Open Excel and start a new workbook. Save the workbook as Office Shortcuts. If you have OneDrive, save it there so the AutoSave feature will work.

Once the workbook is saved, click on the Data tab.

In the ribbon of the Data tab, click on From Web.

The From Web wizard window will open. This is where we put the web address or URL of the website from which we want to scrape data. Switch to your web browser and copy the URL.

Paste the URL in to the URL field of the From Web wizard. We could choose to use this in Basic or Advanced mode. The Advanced mode gives us a lot more options on how to access the data from the website. For this exercise, we only need Basic mode. Click OK.

Excel will now attempt to connect to the website. This may take a few seconds. We’ll see a progress window, if it does.

The Navigator window will open, and we’ll see a list of tables from the website on the left. When we select one, we’ll see a table preview on the right. Let’s select the Frequently used shortcuts table.

We can click on the Web View tab to see the actual website, if we need to look around for the table we want. When we find it, we can click on it and it will be selected for import.

Now, we click on the Load button at the bottom of this window. There are other options we could choose, which are more complex and beyond the scope of doing our first scrape. Just be aware that they are there. Excel’s web scraping capabilities are very powerful.

The web table will be loaded in Excel after a few seconds. We’ll see the data on the left, where the number 1 is in the picture below. Number 2 highlights the Query used to get the data from the website. When we have multiple queries in a work book, this is where we select the one we need to use.

Notice that the data comes into the spreadsheet as an Excel table. It’s already set up for us to be able to filter or sort the data.

We can repeat this process for all the other web pages that have the Office shortcuts that we want for Outlook, Word, Access, PowerPoint, and any other Office app.

Keeping Scraped Data Current in Excel

As a bonus for you, we’re going to learn how to keep our scraped data fresh in Excel. This is a great way to illustrate just how powerful Excel is for data scraping. Even with this, we’re only doing the most basic scraping that Excel can do.

For this example, let’s use a stock information web page like https://www.cnbc.com/stocks/.

Go through what we did before and copy and paste the new URL from the address bar.

You’ll get to the Navigator window and see the tables available. Let’s select the Major U.S. Stock Indices.

Once the data is scraped we’ll see the following spreadsheet.

On the right, we see the query for Major U.S. Stock Indexes. Select that so it is highlighted. Make sure we’re in the Table Tools tab and in the Design area. Then click on the down arrow under Refresh. Then click on Connection Properties.

In the Query Properties window, under the Usage tab, we can control how this information refreshes. We can set a specific time period to refresh, or to refresh when we open the workbook the next time, or to refresh in the background, or any combination of these. Once we choose what we need, click on OK to close the window and continue.

That’s it! Now you can track stock prices, sports scores, or any other data that changes frequently from an Excel spreadsheet. If you’re good with Excel equations and functions, you can do almost anything you want with the data.

Maybe try to identify stock trends, run a fantasy sports pool at work, or maybe just keep track of the weather. Who knows? Your imagination and the data available on the Internet, are the only limits.

Related posts

• Adeegso Excel Mobile's cusub ee shaqada "Ka Geli Xogta Sawirka".

• Isticmaal Hawlaha Kooban si aad u soo koobto Xogta ku jirta Excel

• Adeegso Excel si aad u ogaato dulsaarka waxtarka leh ee ka imanaya heerka dulsaarka magaca ah

• Jadwalka xogtaada Excel

• Sida loo shaandheeyo xogta gudaha Excel

• Sida Loo Sameeyo loona Isticmaalo Foomamka Shaxanka ee Word

• Sida loo isticmaalo COUNTIFS, SUMIFS, AVERAGEIFS gudaha Excel

• Sida loo abuuro Gantt Charts gudaha Microsoft Excel

• U qaabeeya Unugyada iyadoo la isticmaalayo Habaynta Shuruudaha ah ee Excel

• Sida loo isticmaalo Sparklines gudaha Excel

• Geli xaashida shaqada ee Excel Word Doc

• Sida Taariikhaha Looga Jaro Excel

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida Loo Isticmaalo Shaqada YEARFRAC gudaha Excel

• Sida Loo Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Sida loogu daro faallooyinka unugga Xaashida shaqada ee Excel

• Sida loo xisaabiyo kala duwanaanshaha Excel

• Sida Looga Daro Baararka Cilada ee Excel

• Sida loo daro oo loo daabaco sawirada asalka ah ee Excel

• Ka haajiraan Xogta Helitaanka MS ee Xogta SQL Server