1. Excel Functions_Basic

쉬운 목차

EXCEL FUNCTIONS_Basic

https://www.w3schools.com/excel/

AND

returns “TRUE” or “FALSE” based on two or more conditions.

EXCEL

=AND(B2=”Fire”;C2>70)

=IF(AND(B10=”Fire”;C10>70);”Yes”;”No”)

AVERAGE

calculates the average

=AVERAGE(B2:E2)

AVERAGEIF

calculates the average of a range based on a true or false condition.

=AVERAGEIF(range, criteria, [average_range])

https://www.w3schools.com/excel/excel_averageif.php

AVERAGEIFS

the average of a range based on one or more true or false condition.

=AVERAGEIFS(average_range, criteria_range1, criteria1, …)

https://www.w3schools.com/excel/excel_averageifs.php

CONCAT

to link something together.

=CONCAT(A2,” “,A3)

https://www.w3schools.com/excel/excel_concat.php

=CONCAT(A2,”and”,A3)

https://www.w3schools.com/excel/excel_concat.php

COUNT

only counts cells with numbers, not cells with letters.

=COUNT(D2:D21)

https://www.w3schools.com/excel/excel_count.php

COUNTA

counts all cells in a range that has values, both numbers and letters.

=COUNTA(A2:A21)

https://www.w3schools.com/excel/excel_counta.php

COUNTBLANK

counts blank cells in a range.

https://www.w3schools.com/excel/excel_countblank.php

COUNTIF

counts cells as specified.

COUNTIFS

counts cells in a range based on one or more true or false condition.

https://www.w3schools.com/excel/excel_countifs.php

IF

returns values based on a true or false condition

https://www.w3schools.com/excel/excel_if.php

IFS

returns values based on one or more true or false conditions.

https://www.w3schools.com/excel/excel_ifs.php

https://www.w3schools.com/excel/excel_ifs.php

XOR

returns TRUE or FALSE based on two or more conditions.

https://www.w3schools.com/excel/excel_xor.php

https://www.w3schools.com/excel/excel_xor.php

RIGHT

use the RIGHT function with a defined length of characters

=RIGHT(A2,3)

https://www.w3schools.com/excel/excel_right.php

STDEV.P 표준편차

calculates the Standard Deviation (Std) for the entire population.

=STDEV.P(E2:E755)

https://www.w3schools.com/excel/excel_stdevp.php

STDEV.S

calculates the Standard Deviation (Std) for a sample.

=STDEV.P(E2:E21) range of the sample

https://www.w3schools.com/excel/excel_stdevs.php

SUMIF

the sum of values in a range based on a true or false condition.

https://www.w3schools.com/excel/excel_sumif.php

SUMIFS

calculates the sum of a range based on one or more true or false condition.

https://www.w3schools.com/excel/excel_sumifs.php

TRIM

remove irregular text spacing and keep single spaces between words.

=TRIM(A2:C21)

https://www.w3schools.com/excel/excel_trim.php

VLOOKUP

allows searches across columns.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

=VLOOKUP(H3;A2:E21;2;1)

for selecting col_index_number 2.
next – 1 (True) is entered as range_lookup. This is because the most left column has numbers only. If it was text, 0 (False) would have been used.

https://www.w3schools.com/excel/excel_vlookup.php