10Excel functions for data analysis

Posted on by s4l8384gmailcom
Advertisements

The Excel program is one of the programs that has features and characteristics that help the user to analyze data easily, and due to the multiple formulas and functions it provides that are capable of carrying out a set of operations, from which we will discuss in our article these functions of calculations, character and date text tasks, and a set of other research tasks

1. CONCATENATE

This formula is considered one of the most effective formulas in analyzing data, despite its ease and simplicity of working with it. Its task is to use dates, texts, numbers, and different data present in several cells and merge them into one cell.

SYNTAX = CONCATENATE (text1, text2, [text3], ā€¦)

Concatenate multiple cell values

The simple CONCATENATE formula for the values of two cells A2 and B2 is as follows:

= CONCATENATE (A2, B2)

The values will be combined without using any delimiter, and to separate the values with a space we use ā€œ ā€

=CONCATENATE(A3, ā€œ ā€œ, B3)

Connect a string of texts and the computed value

You can also bind a string and a computed value to the formula as in the example of restoring the current date

=CONCATENATE(ā€œToday is ā€, TEXT(TODAY(), ā€œdd-mmm-yyā€))

You can verify that the results provided by the CONCATENATE function are correct by doing the following:

In all cases, the result of the CONCATENATE function is a text string, even if all the source values are numbers

Make sure there is a text argument in the CONCATENATE function to ensure that it works

You have to pay close attention to the validity of the text argument in order for the CONCATENATE function to work correctly, otherwise the formula will return the error #VALUE! This is because the arguments are not valid

2.Len()

This function is used to know the number of characters in one cell, or when dealing with text that contains a limited number of characters, or to know the difference between the numbers of a group of products

SYNTAX = LEN (text)

3.Days()

This function is used to calculate the number of days between two dates

SYNTAX = DAYS (end_date, start_date)

4.Networkdays

It is considered to be a function of date and time in Excel and is often used by finance and accounting departments to exclude the number of weekends to determine the wages of employees based on the calculation of actual working days for them or the calculation of the total number of working days for a specific project

SYNTAX = NETWORKDAYS (start_date, end_date, [holidays])

5.Sumifs()

It is one of the most common formulas in Excel and is considered one of the most important functions for data analysts =SUMIFS. =SUM, especially for conducting data collection under sample conditions

SYNTAX = SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ā€¦)

Advertisements

6. Averageifs()

This task allows the average to be extracted from one or more parameters

SYNTAX = AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ā€¦)

7. Countsifs()

It is an important tool in data analysis and it is similar to SUMIFS. In most functions it counts the number of values that satisfy certain conditions but it doesn’t need a summation range

SYNTAX = COUNTIFS (range, criteria)

8.Count()

Its job is to determine whether a cell is empty or not by discovering gaps in the data set without you, as a data analyst, having to restructure it.

SYNTAX = COUNTA (value1, [value2], …)

9. Vlookup()

This shortcut stands for Vertically searching for a value in the leftmost column of the table so that you can return a value in the same row of the column you specify

SYNTAX = VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

We will explain the arguments to the VLOOKUP function

– lookup_value : is the value to look up in the first column of the table

table – : indicates the table from which the value is to be retrieved

-col_index: returns the column in the table from the value

range_lookup – :

Optional: TRUE = approximate match

Default: FALSE = exact match

The following table will explain the use of VLOOKUP

Cell A11 contains the lookup value

A2:E7 is the table array

3 is the column index with the information for the sections

0 is the search for the range

If you press the Enter key, it will return “Marketing”, which indicates that Stuart works in the marketing department

10. Lookup()

In it, “horizontal” is represented by the letter H, and it searches for one or more values in the top row of the table, then it retrieves a value from a row you specify in the table or row from the same column if this tool makes things easier, for example when the values you use are in the rows The first one from the spreadsheet and you need to look at a certain number of rows, this tool will do the trick

SYNTAX = HLOOKUP (lookup_value, table_array, row_index, [range_lookup])

Let’s learn about Hlookup’s arguments

Lookup_Value denotes the attached value

table ā€” the table from which you need to retrieve data

ROW_INDEX which is the row number to restore the data

Range_lookup for exact and approximate matching, and that is determined by specifying the validity of the default value, so the match is approximate

In our next example, we’ll search for the city Jenson is from using Hlookup.

The search value shown in H23 is Jenson

G1: M5 is the table array

4 is the row index number

0 is for an approximate match

Pressing enter will take you back to New York.

at the end

We conclude from the above how effective Excel is in analyzing data. By learning its formulas and functions, you can make work easier for you and thus save a lot of time and effort.

Advertisements

Ų¹Ų“Ų±Ų© ŁˆŲøŲ§Ų¦Ł Ł„Ų„ŁƒŲ³Ł„ ŁŁŠ ŲŖŲ­Ł„ŁŠŁ„ Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ

Advertisements

ŁŠŲ¹ŲŖŲØŲ± ŲØŲ±Ł†Ų§Ł…Ų¬ Ų„ŁƒŲ³Ł„ Ł…Ł† Ų§Ł„ŲØŲ±Ų§Ł…Ų¬ Ų§Ł„ŲŖŁŠ ŲŖŲŖŁ…ŲŖŲ¹ ŲØŁ…ŁŠŲ²Ų§ŲŖ ŁˆŲ®ŲµŲ§Ų¦Ųµ ŲŖŲ¹ŁŠŁ† Ų§Ł„Ł…Ų³ŲŖŲ®ŲÆŁ… Ų¹Ł„Ł‰ ŲŖŲ­Ł„ŁŠŁ„ Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ ŲØŲ³Ł‡ŁˆŁ„Ų© ŁˆŁ†ŲøŲ±Ų§Ł‹ Ł„Ł…Ų§ ŁŠŁˆŁŲ±Ł‡ Ł…Ł† ŲµŁŠŲŗ ŁˆŁˆŲøŲ§Ų¦Ł Ł…ŲŖŲ¹ŲÆŲÆŲ© Ł‚Ų§ŲÆŲ±Ų© Ų¹Ł„Ł‰ ŲŖŁ†ŁŁŠŲ° Ł…Ų¬ŁˆŲ¹Ų© Ų¹Ł…Ł„ŁŠŲ§ŲŖ Ų³Ł†ŲŖŁ†Ų§ŁˆŁ„ Ł…Ł†Ł‡Ų§ ŁŁŠ Ł…Ł‚Ų§Ł„Ł†Ų§ Ł‡Ų°Ł‡ ŁˆŲøŲ§Ų¦Ł Ų§Ł„Ų¹Ł…Ł„ŁŠŲ§ŲŖ Ų§Ł„Ų­Ų³Ų§ŲØŁŠŲ© ŁˆŁ…Ł‡Ų§Ł… Ł†ŲµŁˆŲµ Ų§Ł„Ų£Ų­Ų±Ł ŁˆŲ§Ł„ŲŖŲ§Ų±ŁŠŲ® ŁˆŁ…Ų¬Ł…ŁˆŲ¹Ų© Ų£Ų®Ų±Ł‰ Ł…Ł† Ł…Ł‡Ų§Ł… Ų§Ł„ŲØŲ­Ų«

CONCATENATE 1

ŲŖŲ¹ŲŖŲØŲ± Ł‡Ų°Ł‡ Ų§Ł„ŲµŁŠŲŗŲ© Ł…Ł† Ų§Ł„ŲµŁŠŲŗ Ų§Ł„Ų£ŁƒŲ«Ų± ŁŲ§Ų¹Ł„ŁŠŲ© ŁŁŠ ŲŖŲ­Ł„ŁŠŁ„ Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ Ų±ŲŗŁ… Ų³Ł‡ŁˆŁ„ŲŖŁ‡Ų§ ŁˆŲØŲ³Ų§Ų·Ų© Ų§Ł„Ų¹Ł…Ł„ ŲØŁ‡Ų§ ŁˆŁ‡ŁŠ Ł…Ł‡Ł…ŲŖŁ‡Ų§ Ų§Ų³ŲŖŲ®ŲÆŲ§Ł… Ų§Ł„ŲŖŁˆŲ§Ų±ŁŠŲ® ŁˆŲ§Ł„Ł†ŲµŁˆŲµ ŁˆŲ§Ł„Ų£Ų±Ł‚Ų§Ł… ŁˆŲØŁŠŲ§Ł†Ų§ŲŖ Ł…Ų®ŲŖŁ„ŁŲ© Ł…ŁˆŲ¬ŁˆŲÆŲ© ŁŁŠ Ų¹ŲÆŲ© Ų®Ł„Ų§ŁŠŲ§ ŁˆŲÆŁ…Ų¬Ł‡Ų§ ŁŁŠ Ų®Ł„ŁŠŲ© ŁˆŲ§Ų­ŲÆŲ©

SYNTAX = CONCATENATE (text1, text2, [text3], ā€¦)

ŲŖŲ³Ł„Ų³Ł„ Ł‚ŁŠŁ… Ų®Ł„Ų§ŁŠŲ§ Ł…ŲŖŲ¹ŲÆŲÆŲ©

CONCATENATE ŲµŁŠŲŗŲ©

A2 Łˆ B2 Ų§Ł„ŲØŲ³ŁŠŲ·Ų© Ł„Ł‚ŁŠŁ… Ų®Ł„ŁŠŲŖŁŠŁ†

Ł‡ŁŠ ŁƒŁ…Ų§ ŁŠŁ„ŁŠ

= CONCATENATE (A2ŲŒ B2)

ā€œ ā€œŲ³ŁŠŲŖŁ… ŲÆŁ…Ų¬ Ų§Ł„Ł‚ŁŠŁ… ŲØŲÆŁˆŁ† Ų§Ų³ŲŖŲ®ŲÆŲ§Ł… Ų£ŁŠ Ł…Ų­ŲÆŲÆ ŲŒ ŁˆŁ„ŁŲµŁ… Ų§Ł„Ł‚ŁŠŁ… ŲØŁ…Ų³Ų§ŁŲ© Ł†Ų³ŲŖŲ®ŲÆŁ…

=CONCATENATE(A3, ā€œ ā€œ, B3)

Ų±ŲØŲ· Ų³Ł„Ų³Ł„Ų© Ł…Ł† Ų§Ł„Ł†ŲµŁˆŲµ ŁˆŲ§Ł„Ł‚ŁŠŁ…Ų© Ų§Ł„Ł…Ų­Ų³ŁˆŲØŲ©

ŁƒŁ…Ų§ ŁˆŁŠŁ…ŁƒŁ†Łƒ Ų±ŲØŲ· Ų³Ł„Ų³Ł„Ų© Ł†ŲµŁŠŲ© ŁˆŁ‚ŁŠŁ…Ų© Ł…Ų­Ų³ŁˆŲØŲ© ŲØŲ§Ł„ŲµŁŠŲŗŲ© ŁƒŁ…Ų§ ŁŁŠ Ų§Ł„Ł…Ų«Ų§Ł„ Ų§Ł„Ł…ŁˆŲ¶Ų­ Ų¹Ł† Ų§Ų³ŲŖŲ¹Ų§ŲÆŲ© Ų§Ł„ŲŖŲ§Ų±ŁŠŲ® Ų§Ł„Ų­Ų§Ł„ŁŠ

=CONCATENATE(ā€œToday is ā€œ, TEXT(TODAY(), ā€œdd-mmm-yyā€))

ŁˆŁŠŁ…ŁƒŁ†Łƒ Ų§Ł„ŲŖŲ£ŁƒŲÆ Ł…Ł† ŲµŲ­Ų© Ų§Ł„Ł†ŲŖŲ§Ų¦Ų¬ Ų§Ł„ŲŖŁŠ ŲŖŁ‚ŲÆŁ…Ł‡Ų§

CONCATENATE Ų§Ł„ŲÆŲ§Ł„Ų©

Ł…Ł† Ų®Ł„Ų§Ł„ Ų§ŲŖŲØŲ§Ų¹ Ł…Ų§ ŁŠŁ„ŁŠ

ŁŁŠ Ų¬Ł…ŁŠŲ¹ Ų§Ł„Ų£Ų­ŁˆŲ§Ł„ ŲŖŁƒŁˆŁ† Ł†ŲŖŁŠŲ¬Ų© *

CONCATENATE Ų§Ł„ŲÆŲ§Ł„Ų©

Ų¹ŲØŲ§Ų±Ų© Ų¹Ł† Ų³Ł„Ų³Ł„Ų© Ł†ŲµŁŠŲ© ŁˆŲ„Ł† ŁƒŲ§Ł†ŲŖ Ų¬Ł…ŁŠŲ¹ Ł‚ŁŠŁ… Ų§Ł„Ł…ŲµŲÆŲ± Ų£Ų±Ł‚Ų§Ł…Ų§Ł‹

Ų§Ų­Ų±Ųµ Ų¹Ł„Ł‰ ŁˆŲ¬ŁˆŲÆ ŁˆŲ³ŁŠŲ·Ų© Ł†ŲµŁŠŲ© ŁŁŠ *

CONCATENATE ŲÆŲ§Ł„Ų©

Ł„Ų¶Ł…Ų§Ł† Ų¹Ł…Ł„Ł‡Ų§

ŁˆŲ¹Ł„ŁŠŁƒ Ų£Ł† ŲŖŁ†ŲŖŲØŁ‡ Ų¬ŁŠŲÆŲ§Ł‹ Ł…Ł† ŲµŲ­Ų© Ų§Ł„ŁˆŲ³ŁŠŲ·Ų© Ų§Ł„Ł†ŲµŁŠŲ© Ł„ŁƒŁŠ ŲŖŲ¹Ł…Ł„ *

CONCATENATE Ų§Ł„ŲÆŲ§Ł„Ų©

ŲØŲ“ŁƒŁ„ ŲµŲ­ŁŠŲ­ ŁˆŲ„Ł„Ų§ ŁŲ§Ł„ŲµŁŠŲŗŲ©

#VALUE! Ų³ŲŖŲ±Ų¬Ų¹ Ł„Łƒ Ų§Ł„Ų®Ų·Ų£

ŁˆŁ‡Ų°Ų§ Ų³ŲØŲØŁ‡ Ų£Ł† Ų§Ł„ŁˆŲ³ŁŠŲ·Ų§ŲŖ ŲŗŁŠŲ± ŲµŲ§Ł„Ų­Ų©

Len() 2.

ŲŖŲ³ŲŖŲ®ŲÆŁ… Ł‡Ų°Ł‡ Ų§Ł„ŲÆŲ§Ł„Ų© Ł„Ł…Ų¹Ų±ŁŲ© Ų¹ŲÆŲÆ Ų§Ł„Ų£Ų­Ų±Ł ŁŁŠ Ų§Ł„Ų®Ł„ŁŠŲ© Ų§Ł„ŁˆŲ§Ų­ŲÆŲ© ŲŒ Ų£Łˆ Ų¹Ł†ŲÆ Ų§Ł„ŲŖŲ¹Ų§Ł…Ł„ Ł…Ų¹ Ł†Ųµ ŁŠŲ­ŁˆŁŠ Ų¹ŲÆŲÆ Ł…Ų­ŲÆŁˆŲÆ Ł…Ł† Ų§Ł„Ų£Ų­Ų±Ł Ų£Łˆ Ł…Ų¹Ų±ŁŲ© Ų§Ł„Ų§Ų®ŲŖŁ„Ų§Ł ŲØŁŠŁ† Ų£Ų±Ł‚Ų§Ł… Ł…Ų¬Ł…ŁˆŲ¹Ų© Ł…Ł† Ų§Ł„Ł…Ł†ŲŖŲ¬Ų§ŲŖ

SYNTAX = LEN (text)

Days() 3.

ŲŖŲ³ŲŖŲ®ŲÆŁ… Ł‡Ų°Ł‡ Ų§Ł„ŲÆŲ§Ł„Ų© Ł„Ų­Ų³Ų§ŲØ Ų¹ŲÆŲÆ Ų§Ł„Ų£ŁŠŲ§Ł… Ų§Ł„ŁˆŲ§Ł‚Ų¹Ų© ŲØŁŠŁ† ŲŖŲ§Ų±ŁŠŲ®ŁŠŁ†

SYNTAX =DAYS (end_date, start_date)

Networkdays4.

ŁˆŁ‡ŁŠ ŲŖŲ¹ŲŖŲØŲ± Ų£Ł†Ł‡Ų§ ŲÆŲ§Ł„Ų© Ų§Ł„ŲŖŲ§Ų±ŁŠŲ® ŁˆŲ§Ł„ŁˆŁ‚ŲŖ ŁŁŠ Ų„ŁƒŲ³Ł„ ŁˆŲŖŲ³ŲŖŲ®ŲÆŁ… ŲŗŲ§Ł„ŲØŲ§Ł‹ Ł…Ł† Ł‚ŲØŁ„ Ų£Ł‚Ų³Ų§Ł… Ų§Ł„Ł…Ų§Ł„ŁŠŲ© ŁˆŲ§Ł„Ł…Ų­Ų§Ų³ŲØŲ© Ł„Ų§Ų³ŲŖŲØŲ¹Ų§ŲÆ Ų¹ŲÆŲÆ Ų¹Ų·Ł„Ų§ŲŖ Ł†Ł‡Ų§ŁŠŲ© Ų§Ł„Ų£Ų³ŲØŁˆŲ¹ Ł„ŲŖŲ­ŲÆŁŠŲÆ Ų£Ų¬ŁˆŲ± Ų§Ł„Ł…ŁˆŲøŁŁŠŁ† ŲØŁ†Ų§Ų”Ł‹ Ų¹Ł„Ł‰ Ų­Ų³Ų§ŲØ Ų£ŁŠŲ§Ł… Ų§Ł„Ų¹Ł…Ł„ Ų§Ł„ŁŲ¹Ł„ŁŠŲ© Ł„Ł‡Ł… Ų£Łˆ Ų­Ų³Ų§ŲØ Ų¹ŲÆŲÆ ŁƒŲ§Ł…Ł„ Ų£ŁŠŲ§Ł… Ų§Ł„Ų¹Ł…Ł„ Ł„Ł…Ų“Ų±ŁˆŲ¹ Ł…Ų¹ŁŠŁ†   

SYNTAX = NETWORKDAYS (start_date, end_date, [holidays])

Sumifs() 5.

ŁˆŁ‡ŁŠ Ł…Ł† Ų§Ł„ŲµŁŠŲŗ Ų§Ł„Ł…ŲŖŲÆŲ§ŁˆŁ„Ų© ŲØŁƒŲ«Ų±Ų© ŁŁŠ Ų„ŁƒŲ³Ł„ ŁˆŲŖŲ¹ŲŖŲØŲ± Ł…Ł† Ų£Ł‡Ł… Ų§Ł„ŁˆŲøŲ§Ų¦Ł ŲØŲ§Ł„Ł†Ų³ŲØŲ© Ł„Ł…Ų­Ł„Ł„ŁŠ Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ

=SUMIFS. =SUM

ŁˆŲ®ŲµŁˆŲµŲ§Ł‹ Ł„Ų„Ų¬Ų±Ų§Ų” Ų¹Ł…Ł„ŁŠŲ© Ų¬Ł…Ų¹ Ł„Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ ŁˆŁŁ‚ Ų“Ų±ŁˆŲ· Ł…Ų¹ŁŠŁ†Ų©

SYNTAX = SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ā€¦)

Advertisements

Averageifs() 6.

ŲŖŲŖŁŠŲ­ Ł‡Ų°Ł‡ Ų§Ł„Ł…Ł‡Ł…Ų© Ų§Ų³ŲŖŲ®Ł„Ų§Ųµ Ų§Ł„Ł…ŲŖŁˆŲ³Ų· Ł…Ł† Ł…Ų¹Ł„Ł…Ų© ŁˆŲ§Ų­ŲÆŲ© Ų£Łˆ Ų£ŁƒŲ«Ų±

SYNTAX = AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ā€¦)

Countsifs() 7.

Ł…Ł† Ų§Ł„Ų£ŲÆŁˆŲ§ŲŖ Ų§Ł„Ł…Ł‡Ł…Ų© ŁŁŠ ŲŖŲ­Ł„ŁŠŁ„ Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ

SUMIFS. ŁˆŁ‡ŁŠ ŲŖŲŖŲ“Ų§ŲØŁ‡ Ł…Ų¹

ŁŁŠ Ł…Ų¹ŲøŁ… Ų§Ł„ŁˆŲøŲ§Ų¦Ł ŁŁ‡ŁŠ ŲŖŁ‚ŁˆŁ… ŲØŲ­Ų³Ų§ŲØ Ų¹ŲÆŲÆ Ų§Ł„Ł‚ŁŠŁ… Ų§Ł„ŲŖŁŠ ŲŖŲ­Ł‚Ł‚ Ų“Ų±ŁˆŲ· Ł…Ų¹ŁŠŁ†Ų© Ų„Ł„Ų§ Ų£Ł†Ł‡Ų§ Ł„Ų§ ŲŖŲ­ŲŖŲ§Ų¬ Ų„Ł„Ł‰ Ł†Ų·Ų§Ł‚ Ų¬Ł…Ų¹

SYNTAX = COUNTIFS (range, criteria)

8. Counta()

Ł…Ł‡Ł…ŲŖŁ‡Ų§ Ł‡ŁŠ Ų£Ł† ŲŖŲ­ŲÆŲÆ Ł‡Ł„ Ų§Ł„Ų®Ł„ŁŠŲ© ŁŲ§Ų±ŲŗŲ© Ų£Ł… Ł„Ų§ Ł…Ł† Ų®Ł„Ų§Ł„ Ų§ŁƒŲŖŲ“Ų§Ł Ų§Ł„ŁŲ¬ŁˆŲ§ŲŖ Ų§Ł„Ł…ŁˆŲ¬ŁˆŲÆŲ© ŁŁŠ Ł…Ų¬Ł…ŁˆŲ¹Ų© Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ ŲÆŁˆŁ† Ų£Ł† ŲŖŲ¶Ų·Ų± ŁƒŁ…Ų­Ł„Ł„ ŲØŁŠŲ§Ł†Ų§ŲŖ Ų„Ł„Ł‰ Ų„Ų¹Ų§ŲÆŲ© Ł‡ŁŠŁƒŁ„ŲŖŁ‡Ų§

SYNTAX = COUNTA (value1, [value2], ā€¦)

9. Vlookup()

ŁŠŲÆŁ„ Ł‡Ų°Ų§ Ų§Ł„Ų§Ų®ŲŖŲµŲ§Ų± Ų¹Ł„Ł‰ Ų§Ł„ŲØŲ­Ų« Ų§Ł„Ų“Ų§Ł‚ŁˆŁ„ŁŠ Ų¹Ł† Ł‚ŁŠŁ…Ų© Ł…Ų§ ŁŁŠ Ų§Ł„Ų¹Ł…ŁˆŲÆ Ų§Ł„ŁƒŲ§Ų¦Ł† ŁŁŠ Ų£Ł‚ŲµŁ‰ ŁŠŲ³Ų§Ų± Ų§Ł„Ų¬ŲÆŁˆŁ„ Ł„ŁŠŲŖŲ³Ł†Ł‰ Ł„Łƒ Ų„Ų±Ų¬Ų§Ų¹ Ł‚ŁŠŁ…Ų© ŁŁŠ Ł†ŁŲ³ Ų§Ł„ŲµŁ Ł…Ł† Ų§Ł„Ų¹Ł…ŁˆŲÆ Ų§Ł„Ų°ŁŠ ŲŖŲ­ŲÆŲÆŁ‡

SYNTAX = VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

VLOOKUP ŁˆŲ³Ł†Ł‚ŁˆŁ… ŲØŲ“Ų±Ų­ Ų§Ł„ŁˆŲ³ŁŠŲ·Ų§ŲŖ Ł„Ł„ŲÆŲ§Ł„Ų©

lookup_value

Ł‡ŁŠ Ų§Ł„Ł‚ŁŠŁ…Ų© Ų§Ł„ŲŖŁŠ Ų¹Ł„ŁŠŁƒ Ų§Ł„ŲØŲ­Ų« Ų¹Ł†Ł‡Ų§ ŁŁŠ Ų§Ł„Ų¹Ł…ŁˆŲÆ Ų§Ł„Ų£ŁˆŁ„ Ł…Ł† Ų§Ł„Ų¬ŲÆŁˆŁ„

table

ŁŠŲÆŁ„ Ų¹Ł„Ł‰ Ų§Ł„Ų¬ŲÆŁˆŁ„ Ų§Ł„ŲŖŁŠ ŁŠŲŖŁ… Ų§Ų³ŲŖŲ±ŲÆŲ§ŲÆ Ų§Ł„Ł‚ŁŠŁ…Ų© Ł…Ł†Ł‡

col_index

ŁŠŲŖŁŠŲ­ Ų§Ų³ŲŖŲ¹Ų§ŲÆŲ© Ų§Ł„Ų¹Ł…ŁˆŲÆ Ų§Ł„Ł…ŁˆŲ¬ŁˆŲÆ ŁŁŠ Ų§Ł„Ų¬ŲÆŁˆŁ„ Ł…Ł† Ų§Ł„Ł‚ŁŠŁ…Ų©

range_lookup

Ų§Ų®ŲŖŁŠŲ§Ų±ŁŠ : TRUE = approximate match

Ų§ŁŲŖŲ±Ų§Ų¶ŁŠ : FALSE = exact match

VLOOKUP ŁˆŲ³ŁŠŁˆŲ¶Ų­ Ų§Ł„Ų¬ŲÆŁˆŁ„ Ų§Ł„ŲŖŲ§Ł„ŁŠ Ų§Ų³ŲŖŲ®ŲÆŲ§Ł…

lookup ŲŖŲ­ŁˆŁŠ Ł‚ŁŠŁ…Ų© A11 Ų§Ł„Ų®Ł„ŁŠŲ©

Ł‡ŁŠ ŲµŁŁˆŁ Ų§Ł„Ų¬ŲÆŁˆŁ„ A2: E7

Ų±Ł‚Ł… 3 Ł‡Łˆ ŁŁ‡Ų±Ų³ Ų§Ł„Ų¹Ł…ŁˆŲÆ Ł…Ų¹ Ų§Ł„Ł…Ų¹Ł„ŁˆŁ…Ų§ŲŖ Ų§Ł„Ų®Ų§ŲµŲ© ŲØŲ§Ł„Ų£Ł‚Ų³Ų§Ł…

Ų±Ł‚Ł… 0 Ł‡Łˆ Ų§Ł„ŲØŲ­Ų« Ų¹Ł† Ų§Ł„Ł†Ų·Ų§Ł‚

Enter ŁˆŁŁŠ Ų­Ų§Ł„ Ų§Ł„Ų¶ŲŗŲ· Ų¹Ł„Ł‰ Ł…ŁŲŖŲ§Ų­

ŁŲ³ŁŠŲ¹ŁŠŲÆ “Ų§Ł„ŲŖŲ³ŁˆŁŠŁ‚” ŁˆŁ‡Ų°Ł‡ ŲÆŁ„Ų§Ł„Ų© Ų¹Ł„Ł‰ Ų£Ł†

ŁŠŲ¹Ł…Ł„ ŁŁŠ Ł‚Ų³Ł… Ų§Ł„ŲŖŲ³ŁˆŁŠŁ‚ Stuart

10. Hlookup()

“ŁˆŁŁŠŁ‡ ŁŠŁ…Ų«Ł„ “Ų§Ł„Ų£ŁŁ‚ŁŠ

H ŲØŲ§Ł„Ų­Ų±Ł

ŁˆŁ‡Łˆ ŁŠŲØŲ­Ų« Ų¹Ł† Ł‚ŁŠŁ…Ų© ŁˆŲ§Ų­ŲÆŲ© Ų£Łˆ Ų£ŁƒŲ«Ų± ŁŁŠ Ų§Ł„ŲµŁ Ų§Ł„Ų¹Ł„ŁˆŁŠ Ł…Ł† Ų§Ł„Ų¬ŲÆŁˆŁ„ŲŒ Ų«Ł… ŁŠŁ‚ŁˆŁ… ŲØŲ§Ų³ŲŖŲ¹Ų§ŲÆŲ© Ł‚ŁŠŁ…Ų© Ł…Ł† ŲµŁ ŲŖŲ­ŲÆŲÆŁ‡ ŁŁŠ Ų§Ł„Ų¬ŲÆŁˆŁ„ Ų£Łˆ Ų§Ł„ŲµŁ Ł…Ł† Ł†ŁŲ³ Ų§Ł„Ų¹Ł…ŁˆŲÆ Ų„Ų°Ų§ ŲŖŁ‚ŁˆŁ… Ł‡Ų°Ł‡ Ų§Ł„Ų£ŲÆŲ§Ų© ŲØŲŖŲ³Ł‡ŁŠŁ„ Ų§Ł„Ų£Ł…ŁˆŲ± Ų£ŁƒŲ«Ų± ŁŁ…Ų«Ł„Ų§Ł‹ Ų¹Ł†ŲÆ ŲŖŁƒŁˆŁ† Ų§Ł„Ł‚ŁŠŁ… Ų§Ł„ŲŖŁŠ ŲŖŲ³ŲŖŲ®ŲÆŁ…Ł‡Ų§ Ł…ŁˆŲ¬ŁˆŲÆŲ© ŁŁŠ Ų§Ł„ŲµŁŁˆŁ Ų§Ł„Ų£ŁˆŁ„Ł‰ Ł…Ł† Ų¬ŲÆŁˆŁ„ Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ ŁˆŲ§Ų­ŲŖŲ¬ŲŖ Ų„Ł„Ł‰ Ų£Ł† ŲŖŲŖŲ·Ł„Ų¹ Ų¹Ł„Ł‰ Ų¹ŲÆŲÆ ŲµŁŁˆŁ Ł…Ų¹ŁŠŁ† ŁŁ‡Ų°Ł‡ Ų§Ł„Ų£ŲÆŲ§Ų© ŲŖŁŁŠ ŲØŲ§Ł„ŲŗŲ±Ų¶ 

SYNTAX = HLOOKUP (lookup_value, table_array, row_index, [range_lookup])

Hlookup Ł„Ł†ŲŖŲ¹Ų±Ł Ų¹Ł„Ł‰ ŁˆŲ³ŁŠŲ·Ų§ŲŖ

Lookup_Value  

ŁŠŲÆŁ„ Ų¹Ł„Ł‰ Ų§Ł„Ł‚ŁŠŁ…Ų© Ų§Ł„Ł…Ų±ŁŁ‚Ų©

table ā€” 

ŁˆŁ‡Łˆ Ų§Ł„Ų¬ŲÆŁˆŁ„ Ų§Ł„Ų°ŁŠ Ų¹Ł„ŁŠŁƒ Ų§Ų³ŲŖŲ¹Ų§ŲÆŲ© Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ Ł…Ł†Ł‡

ROW_INDEX

ŁˆŁ‡Łˆ Ų±Ł‚Ł… Ų§Ł„ŲµŁ Ł„Ų§Ų³ŲŖŲ¹Ų§ŲÆŲ© Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ

Range_lookup

Ł„Ł„Ł…Ų·Ų§ŲØŁ‚Ų© Ų§Ł„ŲÆŁ‚ŁŠŁ‚Ų© ŁˆŲ§Ł„ŲŖŁ‚Ų±ŁŠŲØŁŠŲ© ŁˆŲ°Ł„Łƒ ŁŠŲŖŲ­ŲÆŲÆ ŲØŲŖŲ­ŲÆŁŠŲÆ ŲµŲ­Ų© Ų§Ł„Ł‚ŁŠŁ…Ų© Ų§Ł„Ų§ŁŲŖŲ±Ų§Ų¶ŁŠŲ© ŁŲØŲµŲ­ŲŖŁ‡Ų§ ŁŠŁƒŁˆŁ† Ų§Ł„ŲŖŲ·Ų§ŲØŁ‚ ŲŖŁ‚Ų±ŁŠŲØŁŠ

ŁŁŠ Ł…Ų«Ų§Ł„Ł†Ų§ Ų§Ł„ŲŖŲ§Ł„ŁŠ Ų³Ł†Ł‚ŁˆŁ… ŲØŲ§Ł„ŲØŲ­Ų« Ų¹Ł† Ų§Ł„Ł…ŲÆŁŠŁ†Ų©

Jenson Ų§Ł„ŲŖŁŠ ŁŠŁ†ŲŖŁ…ŁŠ Ų„Ł„ŁŠŁ‡Ų§

Hlookup. ŲØŲ§Ų³ŲŖŲ®ŲÆŲ§Ł…

Jenson ŁˆŁ‡ŁŠ H23 ŲŖŲøŁ‡Ų± Ł‚ŁŠŁ…Ų© Ų§Ł„ŲØŲ­Ų« ŁŁŠ

Ł‡ŁŠ ŲµŁŁˆŁ Ų§Ł„Ų¬ŲÆŁˆŁ„ G1: M5

Ų±Ł‚Ł… 4 ŁŁ‡Ų±Ų³ Ų§Ł„ŲµŁ

Ų±Ł‚Ł… 0 Ų§Ų®ŲŖŲØŲ§Ų± ŲŖŁ‚Ų±ŁŠŲØŁŠ

Enter ŁˆŲØŲ§Ł„Ų¶ŲŗŲ· Ų¹Ł„Ł‰  

“Ų³ŁŠŲ¹ŁŠŲÆŁƒ Ų„Ł„Ł‰ ” Ł†ŁŠŁˆŁŠŁˆŲ±Łƒ  

ŁˆŁŁŠ Ų§Ł„Ų®ŲŖŲ§Ł…

Ł†Ų³ŲŖŲ®Ł„Ųµ Ł…Ł…Ų§ Ų³ŲØŁ‚ Ł…ŲÆŁ‰ ŁŲ§Ų¹Ł„ŁŠŲ© Ų„ŁƒŲ³Ł„ ŁŁŠ ŲŖŲ­Ł„ŁŠŁ„ Ų§Ł„ŲØŁŠŲ§Ł†Ų§ŲŖ ŁŲØŲŖŲ¹Ł„Ł…Łƒ ŲµŁŠŲŗŁ‡ ŁˆŁˆŲøŲ§Ų¦ŁŁ‡ ŁŠŁ…ŁƒŁ†Łƒ ŲŖŲ³Ł‡ŁŠŁ„ Ų§Ł„Ų¹Ł…Ł„ Ų¹Ł„ŁŠŁƒ ŁˆŲØŲ§Ł„ŲŖŲ§Ł„ŁŠ ŲŖŁˆŁŲ± Ų§Ł„ŁƒŲ«ŁŠŲ± Ł…Ł† Ų§Ł„ŁˆŁ‚ŲŖ ŁˆŲ§Ł„Ų¬Ł‡ŲÆ 

Advertisements

2 thoughts on “10Excel functions for data analysis”

  1. Thanks for your marvelous posting! I really enjoyed reading it, you can be a great author.I will make certain to bookmark your blog and will eventually come back at some point. I want to encourage that you continue your great writing, have a nice weekend!

    Liked by 1 person

    Reply

Leave a comment