Categories: Advanced Excel VLOOKUP. You’ve heard about it – but what does it do? Vlookup searches a list for a value in the left most column and retrieves the corresponding value from other columns in that row. As an Excel user, what does that mean for you? Let’s say your organization receives donor money to support various charitable projects. You need to know how much money a given donor gave you last year. However, there are thousands of donors in your system, and because each donor record
contains dozens of pieces of information (name, address, phone number, preferred projects, donation history, etc.), scrolling through pages of information to find the data would take hours. VLOOKUP allows you search for that data quickly. The VLOOKUP searches through one column for a value – such as the donor’s name – and returns the value of the information you need. A VLOOKUP for donor Mark Smith that returns the donation column data saves you valuable time with an immediate figure. No
more scrolling through hundreds of lines for the name! Conduct a VLOOKUP Query In this example, a donor lookup field is created in which a donor’s name can be entered and the cell containing the VLOOKUP formula returns last year’s total donations for that member. 1. Open a worksheet in which you want to create a VLOOKUP field. 3. Enter a starting value in the field that is referenced by the VLOOKUP function. It should be a value near the top of the data list. In this case,
Potter was entered.  5. In the Formula bar, type =VLOOKUP(
7. Close the parentheses in the formula. Practice makes perfect. Try this out on your own and see how quickly you can access the information you need. Just make sure that if you plan to apply your VLOOKUP formula to multiple cells, then your table arguments are absolute references. PRYOR+ 7-DAYS OF FREE TRAININGCourses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams. Upgrade to remove ads Only ₩37,125/year
Terms in this set (54)Which of the following principles are key elements of data integrity? Select all that apply. -Consistency Which process do data analysts use to make data more organized and easier to read? Data Manipulation Before analysis, a company collects data from countries that use different date formats. Which of the following updates would improve the data integrity? -Remove data in an unfamiliar date format -Change all of the dates to the same format What should an analyst do if they do not have the data needed to meet a business objective? Select all that apply. -Gather related data on a small scale and request additional time to find more complete data. Which of the following are limitations that might lead to insufficient data? Select all that apply. -Data that updates continually -Data that updates continually A data analyst wants to find out how many people in Utah have swimming pools. It's unlikely that they can survey every Utah resident. Instead, they survey enough people to be representative of the population. This describes what data analytics concept? -Margin of error -Sample A research team runs an experiment to determine if a new security system is more effective than the previous version. What type of results are required for the experiment to be statistically significant? -Results that are unlikely to occur again -Results that are real and not caused by random chance In order to have a high confidence level in a customer survey, what should the sample size accurately reflect? -The most valuable members of the population -The entire population TRUE OR FALSE: A data analyst determines an appropriate sample size for a survey. They can check their work by making sure the confidence level percentage plus the margin of error percentage add up to 100%. False Fill in the blank: Margin of error is the _____ amount that the sample results are expected to differ from those of the actual population. Maximum In a survey about a new cleaning product, 75% of respondents report they would buy the product again. The margin of error for the survey is 5%. Based on the margin of error, what percentage range reflects the population's true response? 70%-80% Fill in the blank: If a data analyst is using data that has been _____, the data will lack integrity and the analysis will be
faulty. -compromised TRUE or FALSE: A data analyst is given a dataset for analysis. It includes data about the total population of every country in the previous 20 years. Based on the available data, an analyst would be able to determine the reasons behind a certain country's population increase from 2016 to 2017. FALSE A data analyst at a nonprofit organization is working with a dataset about a summer fundraiser. Although they have a lot of useful data by the end of the month, they recognize that the data is insufficient. So, they decide to wait until the end of the season to begin working with the dataset. Which type of insufficient data does this example describe? -Data from only one source -Data that keeps updating TRUE or FALSE: When gathering data through a survey, companies can save money by surveying 100% of a population. FALSE Fill in the blank: Sampling bias in data collection happens when a sample isn't representative of _____. The population as a whole TRUE or FALSE: Sometimes during analysis, an analyst discovers that it's necessary to adjust the business objective. When this happens, the analyst should take the initiative to do so without involving others in order to be respectful of their time. FALSE Fill in the blank: Data _____ refers to the accuracy, completeness, consistency, and trustworthiness of data throughout its life cycle. Integrity A financial analyst imports a dataset to their computer from a storage device. As it's being imported, the connection is interrupted, which compromises the data. Which of the following processes caused the compromise? Data Transfer A data analyst is given a dataset for analysis. It includes data about the total population of every country in the previous 20 years. Which of the following questions would the analyst need more data to address? -Which country had the smallest population in 2017? -What was the reason for the population increase in a certain country? A data analyst at a software company wants to learn more about industry competitors. Because the software industry has more mergers than any other field, the companies and their products are constantly evolving. The analyst has a dataset from three years ago, and they notice that many of the companies and products in the dataset have changed. What makes the analyst decide that the data is insufficient, so they should generate fresh data instead? Data is outdated A car manufacturer wants to learn more about the brand preferences of electric car owners. There are millions of electric car owners in the world. Who should the company survey? A sample of all electric car owners A restaurant wants to gather data about a new dish by giving out free samples and asking for feedback. Who should the restaurant give samples to? ... Data and business objectives might not align for a number of reasons. Which of the following issues can prevent alignment? Select all that apply. -Data visualization -Sampling bias Describe the difference between a null and a zero in a dataset. A null indicates that a value does not exist. A zero is a numerical response. What are the most common processes and procedures handled by data engineers? Select all that apply. -Transforming data into a useful format for analysis -Transforming data into a useful format for analysis What are the most common processes and procedures handled by data warehousing specialists? Select all that apply. -Ensuring data is secure -Ensuring data is secure A data analyst is cleaning a dataset. They want to confirm that users entered five-digit zip codes correctly by checking the data in a certain spreadsheet column. What would be most helpful as the next step? Using the field length tool to specify the number of characters in each cell in the column Every database has its own formatting, which can cause the data to seem inconsistent. Data analysts use the _____ tool to create a clean and consistent visual appearance for their spreadsheets. Clear formatting What is the process of combining two or more datasets into a single dataset? Data Merging Fill in the blank: In data analytics, _____ describes how well two or more datasets are able to work together. Compatibility A data analyst uses the COUNTIF function to count the number of times a value less than 5 occurs between spreadsheet cells A2 through A100. What is the correct syntax? =COUNTIF(A2:A100,"<5") Describe the relationship between a text string and a substring. A text string is a group of characters within a cell. A substring is a smaller subset of that text string. Fill in the blank: To remove leading, trailing, and repeated spaces in data, analysts use the ____ function. TRIM Fill in the blank: Conditional formatting is a spreadsheet tool that changes how _____ appear when values meet a specific condition. Cells A data analyst uses the SPLIT function to divide a text string around a specified character and put each fragment into a new, separate cell. What is the specified character separating each item called? Delimiter
Fill in the blank: A predetermined structure that includes a function's required information and its proper placement is called _____. Syntax In order to extract the five-digit postal code from Burlington, MA, what is the correct function? 8621 Glendale Dr. Burlington, MA 01803 =RIGHT(B3,5) An analyst is cleaning a new dataset containing 500 rows. They want to make sure the data contained from cell B2 through cell B300 does not contain a number greater than 50. Which of the following COUNTIF function syntaxes could be used to answer this question? Select all that apply. =COUNTIF(B2:B300,">50") The V in VLOOKUP stands for what? Vertical
TRUE or FALSE: To evaluate how well two or more data sources work together, data analysts use data mapping. True Conditional formatting is a spreadsheet tool that changes how cells appear when values meet a specific condition. Data analysts can use conditional formatting to do which of the following tasks? Select all that apply. -To identify blank cells or missing information A delimiter is a character that indicates the beginning or end of a data item. The split text to columns tool uses a delimiter to accomplish what task? Split one column into two TRUE or FALSE: VLOOKUP searches for a value in a row in order to return a corresponding piece of information. False Fill in the blank: Data mapping is the process of _____ fields from one data source to another. Matching Which of the following are benefits of using SQL? Select all that apply. -SQL offers powerful tools for cleaning data. -SQL can be adapted and used with multiple database programs. Which of the following tasks can data analysts do using both spreadsheets and SQL? Select all that apply. -Process huge amounts of data efficiently -Join data SQL is a language used to communicate with databases. Like most languages, SQL has dialects. What are the advantages of learning and using standard SQL? Select all that apply. -Standard SQL is much easier to learn than other dialects. -Standard SQL requires a small number of syntax changes to adapt to other dialects. A data analyst is analyzing medical data for a health insurance company. The dataset contains billions of rows of data. Which of the following tools will handle the data most efficiently? SQL In which of the following situations would a data analyst use SQL instead of a spreadsheet? -When quickly pulling information from many different sources in a database A data analyst runs a SQL query to extract
some data from a database for further analysis. How can the analyst save the data? Select all that apply. -Create a new table for the data. In SQL databases, what data type refers to a number that contains a decimal? Float A data analyst is working with product sales data. They import new data into a database. The database recognizes the data for product price as text strings. What SQL function can the analyst use to convert text strings to floats? CAST What SQL function lets you add strings together to create new text strings that can be used as unique keys? CONCAT Sets with similar termsMIS 3300 Midterm Exam91 terms Jake_Ward7 Chapter 913 terms cfblue15 Chapter 545 terms Mercedes_Amanda MIS 3305 ~ Esserman Exam 381 terms berkbaker Sets found in the same folderWeek 243 terms training4life Quiz Questions78 terms Jasmin_Gomez2 Data-Driven Decisions23 terms Owen_Dunfee4 spreadsheets9 terms Owen_Dunfee4 Other sets by this creatorQuiz Questions80 terms Jasmin_Gomez2 Vocabulary6 terms Jasmin_Gomez2 Quiz Questions57 terms Jasmin_Gomez2 Vocabulary62 terms Jasmin_Gomez2 Recommended textbook solutionsInformation Technology Project Management: Providing Measurable Organizational Value5th EditionJack T. Marchewka 346 solutions
Fundamentals of Database Systems7th EditionRamez Elmasri, Shamkant B. Navathe 687 solutions Introduction to Algorithms3rd EditionCharles E. Leiserson, Clifford Stein, Ronald L. Rivest, Thomas H. Cormen 726 solutions
Service Management: Operations, Strategy, and Information Technology7th EditionJames Fitzsimmons, Mona Fitzsimmons 103 solutions Other Quizlet setsvf #19101 terms lululu94 Marketing Strategy Quiz 1124 terms jessicajj20 Wilson Bio 1330 Tx State Test 249 terms Lazimhuq Vocal Pedagogy Midterm20 terms austin_g_shugart |
Vlookup searches for a value in a row in order to return a corresponding piece of information. 1 point true false
Postagens relacionadas
direito autoral © 2024 pt.wiewird Inc.
