In this formula, QUERY searches data from a predefined range/array according to specific criteria. Simultaneously, IMPORTRANGE imports the data into the target spreadsheet. Show
Placing QUERY before IMPORTRANGE allows you to search (query) specific information from the data that is imported with IMPORTRANGE. This way, you only import the data you want to see in your spreadsheet. After IMPORTRANGE, you need to add search criteria. In this case, we added Select * Where Col1=’Americas’ in order to import the same data from countries in the Americas only. The * specifies all the data, so this query is saying “select all the data that has “Americas” in column 1”. It’s possible to do the same for any other search criteria. Just make sure to check spaces, quotation marks and parentheses. This formula especially comes in handy in situations where you want to merge data. If you work in online marketing for example, you might want to merge keyword data or traffic from various sources into one spreadsheet. It gives you a better overview of several data sources such as Google Analytics, Ahrefs, SEMrush or Google Search Console. Abstract: This is the first tutorial in a series designed to get you acquainted and comfortable using Excel and its built-in data mash-up and analysis features. These tutorials build and refine an Excel workbook from scratch, build a data model, then create amazing interactive reports using Power View. The tutorials are designed to demonstrate Microsoft Business Intelligence features and capabilities in Excel, PivotTables, Power Pivot, and Power View. Note: This article describes data models in Excel 2013. However, the same data modeling and Power Pivot features introduced in Excel 2013 also apply to Excel 2016. In these tutorials you learn how to import and explore data in Excel, build and refine a data model using Power Pivot, and create interactive reports with Power View that you can publish, protect, and share. The tutorials in this series are the following: Import Data into Excel 2013, and Create a Data Model Extend Data Model relationships using Excel, Power Pivot, and DAX Create Map-based Power View Reports Incorporate Internet Data, and Set Power View Report Defaults Power Pivot Help Create Amazing Power View Reports - Part 2 In this tutorial, you start with a blank Excel workbook. At the end of this tutorial is a quiz you can take to test your learning. This tutorial series uses data describing Olympic Medals, hosting countries, and various Olympic sporting events. We suggest you go through each tutorial in order. Also, tutorials use Excel 2013 with Power Pivot enabled. For more information on Excel 2013, click here. For guidance on enabling Power Pivot, click here. We start this tutorial with a blank workbook. The goal in this section is to connect to an external data source, and import that data into Excel for further analysis. Let’s start by downloading some data from the Internet. The data describes Olympic Medals, and is a Microsoft Access database. Click the following links to download files we use during this tutorial series. Download each of the four files to a location that’s easily accessible, such as Downloads or My Documents, or to a new folder you create: In Excel 2013, open a blank workbook. Click DATA > Get External Data > From Access. The ribbon adjusts dynamically based on the width of your workbook, so the commands on your ribbon may look slightly different from the following screens. The first screen shows the ribbon when a workbook is wide, the second image shows a workbook that has been resized to take up only a portion of the screen. Select the OlympicMedals.accdb file you downloaded and click Open. The following Select Table window appears, displaying the tables found in the database. Tables in a database are similar to worksheets or tables in Excel. Check the Enable selection of multiple tables box, and select all the tables. Then click OK. The Import Data window appears. Note: Notice the checkbox at the bottom of the window that allows you to Add this data to the Data Model, shown in the following screen. A Data Model is created automatically when you import or work with two or more tables simultaneously. A Data Model integrates the tables, enabling extensive analysis using PivotTables, Power Pivot, and Power View. When you import tables from a database, the existing database relationships between those tables is used to create the Data Model in Excel. The Data Model is transparent in Excel, but you can view and modify it directly using the Power Pivot add-in. The Data Model is discussed in more detail later in this tutorial.
Once the data is imported, a PivotTable is created using the imported tables. With the data imported into Excel, and the Data Model automatically created, you’re ready to explore the data. Explore data using a PivotTable Exploring imported data is easy using a PivotTable. In a PivotTable, you drag fields (similar to columns in Excel) from tables (like the tables you just imported from the Access database) into different areas of the PivotTable to adjust how it presents your data. A PivotTable has four areas: FILTERS, COLUMNS, ROWS, and VALUES. It might take some experimenting to determine which area a field should be dragged to. You can drag as many or few fields from your tables as you like, until the PivotTable presents your data how you want to see it. Feel free to explore by dragging fields into different areas of the PivotTable; the underlying data is not affected when you arrange fields in a PivotTable. Let’s explore the Olympic Medals data in the PivotTable, starting with Olympic medalists organized by discipline, medal type, and the athlete’s country or region.
Your PivotTable looks like the following screen. With little effort, you now have a basic PivotTable that includes fields from three different tables. What made this task so simple were the pre-existing relationships among the tables. Because table relationships existed in the source database, and because you imported all the tables in a single operation, Excel could recreate those table relationships in its Data Model. But what if your data originates from different sources, or is imported at a later time? Typically, you can create relationships with new data based on matching columns. In the next step, you import additional tables, and learn how to create new relationships. Import data from a spreadsheetNow let’s import data from another source, this time from an existing workbook, then specify the relationships between our existing data and the new data. Relationships let you analyze collections of data in Excel, and create interesting and immersive visualizations from the data you import. Let’s start by creating a blank worksheet, then import data from an Excel workbook.
Import data using copy and pasteNow that we’ve imported data from an Excel workbook, let’s import data from a table we find on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the Olympic host cities from a table.
City NOC_CountryRegion Alpha-2 Code Edition Season Melbourne / Stockholm AUS AS 1956 Summer Sydney AUS AS 2000 Summer Innsbruck AUT AT 1964 Winter Innsbruck AUT AT 1976 Winter Antwerp BEL BE 1920 Summer Antwerp BEL BE 1920 Winter Montreal CAN CA 1976 Summer Lake Placid CAN CA 1980 Winter Calgary CAN CA 1988 Winter St. Moritz SUI SZ 1928 Winter St. Moritz SUI SZ 1948 Winter Beijing CHN CH 2008 Summer Berlin GER GM 1936 Summer Garmisch-Partenkirchen GER GM 1936 Winter Barcelona ESP SP 1992 Summer Helsinki FIN FI 1952 Summer Paris FRA FR 1900 Summer Paris FRA FR 1924 Summer Chamonix FRA FR 1924 Winter Grenoble FRA FR 1968 Winter Albertville FRA FR 1992 Winter London GBR UK 1908 Summer London GBR UK 1908 Winter London GBR UK 1948 Summer Munich GER DE 1972 Summer Athens GRC GR 2004 Summer Cortina d'Ampezzo ITA IT 1956 Winter Rome ITA IT 1960 Summer Turin ITA IT 2006 Winter Tokyo JPN JA 1964 Summer Sapporo JPN JA 1972 Winter Nagano JPN JA 1998 Winter Seoul KOR KS 1988 Summer Mexico MEX MX 1968 Summer Amsterdam NED NL 1928 Summer Oslo NOR NO 1952 Winter Lillehammer NOR NO 1994 Winter Stockholm SWE SW 1912 Summer St Louis USA US 1904 Summer Los Angeles USA US 1932 Summer Lake Placid USA US 1932 Winter Squaw Valley USA US 1960 Winter Moscow URS RU 1980 Summer Los Angeles USA US 1984 Summer Atlanta USA US 1996 Summer Salt Lake City USA US 2002 Winter Sarajevo YUG YU 1984 Winter
Now that you have an Excel workbook with tables, you can create relationships between them. Creating relationships between tables lets you mash up the data from the two tables. Create a relationship between imported dataYou can immediately begin using fields in your PivotTable from the imported tables. If Excel can’t determine how to incorporate a field into the PivotTable, a relationship must be established with the existing Data Model. In the following steps, you learn how to create a relationship between data you imported from different sources.
The PivotTable changes to reflect the new relationship. But the PivotTable doesn’t look right quite yet, because of the ordering of fields in the ROWS area. Discipline is a subcategory of a given sport, but since we arranged Discipline above Sport in the ROWS area, it’s not organized properly. The following screen shows this unwanted ordering.
Behind the scenes, Excel is building a Data Model that can be used throughout the workbook, in any PivotTable, PivotChart, in Power Pivot, or any Power View report. Table relationships are the basis of a Data Model, and what determine navigation and calculation paths. In the next tutorial, Extend Data Model relationships using Excel 2013, Power Pivot, and DAX, you build on what you learned here, and step through extending the Data Model using a powerful and visual Excel add-in called Power Pivot. You also learn how to calculate columns in a table, and use that calculated column so that an otherwise unrelated table can be added to your Data Model. Checkpoint and QuizReview What You’ve Learned You now have an Excel workbook that includes a PivotTable accessing data in multiple tables, several of which you imported separately. You learned to import from a database, from another Excel workbook, and from copying data and pasting it into Excel. To make the data work together, you had to create a table relationship that Excel used to correlate the rows. You also learned that having columns in one table that correlate to data in another table is essential for creating relationships, and for looking up related rows. You’re ready for the next tutorial in this series. Here’s a link: Extend Data Model relationships using Excel 2013, Power Pivot, and DAX QUIZ Want to see how well you remember what you learned? Here’s your chance. The following quiz highlights features, capabilities, or requirements you learned about in this tutorial. At the bottom of the page, you’ll find the answers. Good luck! Question 1: Why is it important to convert imported data into tables? A: You don’t have to convert them into tables, because all imported data is automatically turned into tables. B: If you convert imported data into tables, they will be excluded from the Data Model. Only when they’re excluded from the Data Model are they available in PivotTables, Power Pivot, and Power View. C: If you convert imported data into tables, they can be included in the Data Model, and be made available to PivotTables, Power Pivot, and Power View. D: You cannot convert imported data into tables. Question 2: Which of the following data sources can you import into Excel, and include in the Data Model? A: Access Databases, and many other databases as well. B: Existing Excel files. C: Anything you can copy and paste into Excel and format as a table, including data tables in websites, documents, or anything else that can be pasted into Excel. D: All of the above Question 3: In a PivotTable, what happens when you reorder fields in the four PivotTable Fields areas? A: Nothing – you cannot reorder fields once you place them in the PivotTable Fields areas. B: The PivotTable format is changed to reflect the layout, but underlying data is unaffected. C: The PivotTable format is changed to reflect the layout, and all underlying data is permanently changed. Bagaimana menggunakan Importrange?Menggunakan fungsi IMPORTRANGE. Di Spreadsheet, buka spreadsheet.. Di sel yang kosong, masukkan =IMPORTRANGE.. Dalam tanda kurung, tambahkan spesifikasi berikut dalam kutipan dan pisahkan dengan koma: URL spreadsheet di Spreadsheet. ... . Tekan Enter.. Klik Izinkan akses untuk menghubungkan dua spreadsheet.. Langkah memasukkan data ke Google Sheet?Memasukkan teks atau data: Klik sel, lalu masukkan teks. Menyisipkan item lainnya: Klik Sisipkan, lalu tambahkan diagram, gambar, fungsi, catatan, dan lainnya. Catatan: Anda juga dapat menambahkan fungsi ke sel dengan mengetik =. Untuk melihat fungsi yang tersedia, buka daftar fungsi spreadsheet Google.
Importrange untuk apa?IMPORTRANGE adalah fungsi data eksternal, seperti halnya IMPORTXML dan GOOGLEFINANCE . Artinya, fungsi ini memerlukan koneksi internet agar dapat berfungsi.
Bagaimana cara mengakses Google sheet?Anda dapat membuka Spreadsheet dengan salah satu cara berikut: Browser web apa pun—Buka sheets.google.com. Google Drive—Klik Baru. Google Spreadsheet, lalu buat dari awal atau dari template.
|