MS Excel Formulas In Hindi – Advance Excel Tutorial Part 1

Excel formulas with examples in Hindi sikhe

Iss advance MS Excel tutorial in Hindi mein hum aapko Excel formulas ke baare mein examples ke saath batayenge jisse aap MS Excel mein expert ban jaoge.

Yeh Advance MS Excel tutorial baaki excel courses se behtar hai kyonki:

• Yeh hindi excel tutorial 100% FREE hai
• Iss course mein humne aapko simple examples se excel ke baare mein bataya hai

Yeh MS Excel full course tutorial in Hindi ka third part hai. Isko complete karne ke baad aap baaki 3 tutorial bhi kar sakte hain. Niche diye gaye links par click karke aap uss tutorial pe ja sakte hai –

MS Excel in Hindi full tutorial Beginner course part 1 – Agar excel bilkul nahi aati

MS Excel in Hindi full tutorial Beginner course part 2 – MS Excel ko use karke data analysis kaise karte hai (Sort aur Filter kaise kare)

MS Excel in Hindi full tutorial Advance course part 2 – MS Excel ke advance formulas

Yeh ms excel formulas in Hindi ko humne aapke liye level wise divide kar diye hain-

Beginner level formulas aur functions

Intermediate level formulas aur functions

Advanced level formulas aur functions

Ye sab level ke formulas aur functions hum examples ke through samjhenge kyonki maine pehle bhi kaha hai ki bina examples aur practice ke aap excel efficiently nahi sikh sakte.

Josh talks ke courses harazo logo ko help kar rahe hai career mai aage badne ke liye. Iss link par click karke App download kare aur janiye courses ke baare mein

Ye saare formulas aapko Formula Tab mein bhi miljayenge ya phir aap manually bhi ye formulas laga sakte hain jaise maine iss article mein kiya hai.

SYNTAX

Sabse pehle mein bata chahungi ki har formula ya function ka ek unique syntax hota hai – ye excel mein ek function ka layout hota hai. Isse ek function ke arguments ka order pata chalta hai. Kisi bhi function mein syntax follow karna bahut important hota hai aur humein apni arguments ko isi order mein likhna hota hai.

Chaliye ab start kare pehle beginner level formulas aur functions se.

Beginner level formulas aur functions

Beginner level mein hum niche diye gaye formulas aur functions apply karna sikhenge –

Text functions:

TRIM- removes leading spaces, extra spaces and trailing spaces

Date and time functions:

DATE- for entering date in date format

TIME- for entering time with hours to am/pm format

TODAY- for entering today’s date

NOW- for entering both current date and current time in a single cell

Autosum functions:

SUM – for summing up selected cells

SUMIF- for summing cells with a criteria

SUMIFS- for summing cells with more than one criteria

COUNT- for counting the number of cells that contain numbers

COUNTA- for counting cells that contain numbers, text, logical values, error values, and empty text (“”).

COUNTIF-for counting cells based on one criteria

COUNTIFS- for counting cells with more than one criteria

AVERAGE- for finding average of selected cells

AVERAGEIF- for finding average of cells with a criteria

AVERAGEIFS- for finding average of cells with more than one criteria

MAX- for finding maximum no. amongst selected cells

MIN- for finding minimum no. amongst selected cells

Chaliye ab ye saare formulas aur functions hum examples ke sath dekhte hai.

Main yahan teachers ke class record ki report ka ek simple example liya hai.

Ye mera raw data hai-

1.TEXT FUNCTIONS

TRIM function

Aap dekh saktein hai humare data mein bahut saari unnecessary extra spaces hain-

Aap TRIM function use karke apne data mein ye extra spaces remove karsakte hain. Isse use karne ke liye pehle aap kisi random cell me jakar likhein-

=TRIM(

Ab ismein vo cell no. likhein jiske aap extra spaces remove karna chahtein hain jaise mein pehle “S.no.”  waale cell ko select kiya hai –

=TRIM(C4

Phir bracket close karde-

=TRIM(C4)

Press ENTER. Ab aap dekhein ki aapke cell mein koi extra spaces nahi hai.

Ab issi cell ko left mein drag karde. Isse vahan tak drag kare jahan tak aap apne raw data ke spaces ko remove karna chahte hain jaise meri total seven columns thi isliye maine iss cell ko seven columns tak drag kiya aise-

Ab aap dekhenge ki TRIM function automatically inn cell mein bhi apply hojayega aur extra spaces remove hojayegi.

Ab inn saari trimmed cells ko select karke niche drag karde. Kyonki mujhe twelve rows tak ke data ko TRIM karna tha isliye maine isse twelve cells tak drag kardiya.

Ab aap apne old data ko new cleaned data se compare karke dekh sakte hain TRIM function ka awesome use.

Aur ab apne purane data ko select karke DELETE kar sakte aur apne new clean data ko CUT karke upar ke cells mein PASTE kar saktein hain. Ismein aap wordart (here “Teacher’s Class Report”) karne ke liye ye kare-

INSERT>WORDART>SELECT THE WORDART YOU WANT

2. DATE,TIME AND AUTOSUM FUNCTIONS

Ab hum dekhenge autosum function ka use upar wale new cleaned data use karke.

Aur isme humara aim hai –

NOTE: Koi bhi function lagaane se pehle aap apni cell range dhyan se check karle aur accordingly lagaye kyonki meri aur aapki cell range different ho sakti hain.

a.Total marks

SUM function

Total marks nikalne ke liye aap jiss cell mein total marks nikalna chahte hain uss cell mein ye function lagaye –

=SUM(

Ab select kare cell range jiska sum aapko nikalna hai, jaise yahan sab subjects ke marks. Aap apni cell range type bhi kar sakte hain. Cell range enter karne ke baad aap apne SUM function ka bracket close karde.

=SUM(D10:H10)

Press ENTER aur aapko apna sum miljayega.

Aap isi cell ko niche drag karke ye function baaki students ke liye bhi kar sakte hain aise-

b. Average marks

AVERAGE function

Average marks nikalne ke liye aap ye function lagaye-

=AVERAGE(

Enter range D10 to H10.

=AVERAGE(D10:H10)

Press ENTER.

Ab ise niche drag karke baaki cells ke liye bhi yahi functions apply kare.

c. Grand Total

SUM function

Grand total ke liye bhi hum SUM function use karenge bas yahan humari range change hojayegi aise –

=SUM(D10:D20)

Press ENTER.

Ab ise right side drag karke aap ye function baaki cells mein bhi apply kar sakte hain.

c. Enter date and time of writing report

Agar aap apne report mein date and time enter karna chahte hai toh aap Date and Time functions ka use kare.

DATE function

Pehle cell mein ye likhein-

=DATE(

Phir ismein jo date aapko date format mein enter karni hai vo aap DATE function mein iss order enter kare

Year, month, day

=DATE(2019, 06, 20)

Aur phir bracket close karke ENTER press kare aur aapko apni date miljayegi.

TIME function

Agar aapko time hrs, mins, secs ki form mein pata hai toh aap usse cell me 12 hr cycle format mein enter kar sakte hain aise-

=TIME(

Ismein pehle hours phir minutes aur phir seconds enter kare aur bracket close karke Enter press karde.

=TIME(16,36,5)

Press ENTER.

TODAY function

Today function hum current date enter karne ke liye karte hain aise-

=TODAY()

And press ENTER.

NOW function

Now function ka use hum current date aur time enter karne ke liye karte hain.

Ise aise lagate hain-

=NOW()

Press Enter.

d. No. of students

COUNTA function

Agar aap cells jinme sirf text hai unhe count karna chahte hain toh hai aap iss function ko use kar sakte hain.

Isse aise use karte hai-

=COUNTA(

Select students names range (excluding Name).

=COUNTA(C10:C20)

Press ENTER aur phir aapko ye milega –

COUNT function

No. of students ke liye aap COUNT function bhi use kar sakte hain aise-

=COUNT(

Ab select kare range jismein sirf numbers ho maine yahan sabke English ke marks leliye hain kyonki jitne no. of marks utne students.

=COUNT(D10:D20)

Press Enter.

e. No. of subjects

COUNTA function

Agar aap cells jinme sirf text hai unhe count karna chahte hain toh hai aap iss function ko use kar sakte hain.

Isse aise use karte hai-

=COUNTA(

Select students names range.

=COUNTA(D9:J9)

Aur phir aapko ye milega –

f. Maximum Marks

MAX function

MAX function ka use hum yahan subject ke maximum marks nikalne ke liye use karenge.
Pehle hum nikalte hain English mein max. marks.

Apne desired cell par ye function lagayein-

=MAX(

Select kare vo range jismein sabke English ke marks hain aise-

=MAX(D10:D20)

Press ENTER.

Ab isse right side drag karke baaki subjects ke liye bhi apply kare.

Ye aisa lagega-

g. Minimum Marks

MIN function ka use hum yahan ek subject ke minimum marks nikalne ke liye use karenge.
Pehle hum nikalte hain English mein min. marks.

Apne desired cell par ye function  lagayein-

=MIN(

Select kare vo range jismein sabke English ke marks hain aise-

=MIN(D10:D20)

Press Enter.

Ab isse right side drag karke baaki subjects ke liye bhi apply kare-

Ye aisa lagega-

h. No. of students > 50 marks in English

COUNTIF function

COUNTIF function hum cells ko count karne ke liye use karte hai but ek condition ya criteria par.

Isse use karne ke liye pehle hum pehle likhte hain-

=COUNTIF(

Ab pehle ismein hum range insert karenge. Yahan humari range English ke marks hain.

NOTE: Apni range dhyan se apne aim ke according add kare.

Ab ismein aap comma insert kare.
Iske baad aap apna criteria likhe in inverted commas and bracket close kare.

=COUNTIF(D10:D20,”>50”)

Press Enter.

j. No. of students > 50 marks

COUNTIF function

Isse use karne ke liye pehle hum COUNTIF function jaise humne upar lagaya hai lagayenge aise-

Ab likhein range aur criteria.

=COUNTIF(D10:D20,”>50″)

Press ENTER.

Ab iss cell ko right side drag kare aur baaki subjects ke liye bhi aapka function apply hojayega.

k. No. of students failed

COUNTIF function

No. of failed students nikalne ke liye humari condition ya criteria <34 hai. Isse nikalne ke liye hum COUNTIF function ka use kar sakte hain aise-

=COUNTIF(D10:D20,

Iske baad insert criteria-

=COUNTIF(D10:D20,”<34”)

Press Enter.

Baaki subjects mein bhi yahi apply karne ke liye apne cell ko select kare jismein aapne function lagaya hai aur usse right side drag karde aise-

l. Total marks of students > 50 in English

SUMIF function

SUMIF function hum cells ko sum karne ke liye use karte hai but ek condition ya criteria par.

Isse use karne ke liye pehle hum likhte hain-

=SUMIF(

Ab pehle ismein hum range insert karenge. Yahan humari range English ke marks hain.

Ab ismein aap comma insert kare.
Iske baad aap apna criteria likhe inverted commas mein aur bracket close karde.

=SUMIF(D10:D20,”>50”)

Press Enter.

m. Avg. marks of students > 50 in English

AVERAGEIF function

AVERAGEIF function hum cells ka average nikalne ke liye use karte hai but ek condition ya criteria par.

Isse use karne ke liye pehle hum likhte hain-

=AVERAGEIF(

Ab pehle ismein hum range insert karenge. Yahan humari range English ke marks hain.

=AVERAGEIF(D10:D20

Ab ismein aap comma lagayein. Iske baad aap apna criteria likhe inverted commas mein aur bracket close karde.

=AVERAGEIF(D10:D20,”>50”)

Press Enter.

n. No. of students > 50 marks in all subjects

COUNTIFS function

COUNTIFS function hum cells ko count karne ke liye karte jab humein ek se jyada condition/ criteria add karni hoti hain.

Isse use karne ke liye pehle hum likhte hain-

=COUNTIFS(

Iske baad aap first criteria ke liye range select karke ek comma lagayein aur phir inverted commas mein apna criteria likhdein aise-

=COUNTIFS(D10:D20, “>50”,

Ab vaapis comma lagayein aur phir apni second criteria ki range select kare. Phir se comma lagayein aur apna second criteria likhdein aise-

=COUNTIFS(D10:D20, “>50″,E10:E20,”>50″,

Aise hi baaki criterias add karte rahein-

=COUNTIFS(D10:D20, “>50″,E10:E20,”>50″,F10:F20,”>50″,

Aapka complete function kuch aise hoga –

=COUNTIFS(D10:D20, “>50″,E10:E20,”>50″,F10:F20,”>50″,G10:G20,”>50″,H10:H20,”>50″)

Press ENTER aur aapka apna count function with conditions/criterias jo apne insert kiye hain miljayega.

o. Total marks of students > 50 in all subjects

SUMIFS function

SUMIFS function hum cells ka sum nikalne ke liye karte jab humein ek se jyada condition or criteria add karni hoti hain.

Isse use karne ke liye pehle hum likhte hain-

=SUMIFS(

SUMIFS function mein humein sabse pehle apni vo range select karni  hoti hai jismein humara marks ka TOTAL hai.

=SUMIFS(J10:I20,

Iske baad aap comma lagyein aur phir apne first criteria ke liye range select karke phir se ek comma lagayein aur phir inverted commas mein apna criteria likhdein aise-

=SUMIFS(I10:I20,D10:D20,”>50″,

Aise hi aap iske baad comma lagakar second criteria ki range select kare aur phir ek comma lagakar uska criteria inverted commas mein likhdein. Aise hi apni saari criteria range and criteria add karde aise-

=SUMIFS(I10:I20,D10:D20,”>50″,E10:E20,”>50″,F10:F20,”>50″

Aapka function kuch finally kuch aisa hoga-

=SUMIFS(I10:I20,D10:D20,”>50″,E10:E20,”>50″,F10:F20,”>50″,G10:G20,”>50″,H10:H20,”>50″)

Press ENTER aur aapko apna sum with inserted criterias miljayega.

p. Avg. marks of students > 50 in all subjects

AVERAGEIFS function

AVERAGEIFS function hum cells ka average nikalne ke liye karte jab humein ek se jyada condition or criteria add karni hoti hain.

Ise use karne ke liye pehle hum likhte hain-

=AVERAGEIFS(

AVERAGEIFS function mein humein sabse pehle apni vo range select karni  hoti hai jismein humare marks ka AVERAGE hai aise-

=AVERAGEIFS(J10:J20,

Iske baad aap comma lagyein aur phir apne first criteria ke liye range select karke phir se ek comma lagayein aur phir inverted commas mein apna criteria likhdein aise-

=AVERAGEIFS(J10:J20,D10:D20,”>50″,

Aise hi aap iske baad comma lagakar second criteria ki range select kare aur phir ek comma lagakar uska criteria inverted commas mein likhdein.. Aise hi apni saari criteria range and criteria add karde aise-

=AVERAGEIFS(J10:J20,D10:D20,”>50″,E10:E20,”>50″,F10:F20,”>50″,G10:G20,”>50″,H10:H20,”>50″

Aapka function kuch finally kuch aisa hoga-

=AVERAGEIFS(J10:J20,D10:D20,”>50″,E10:E20,”>50″,F10:F20,”>50″,G10:G20,”>50″,H10:H20,”>50″)

Press ENTER aur aapko apna sum with inserted criterias miljayega.

Isse humare Excel ke beginner level formulas aur functions khatm hote hain. Intermediate aur advanced level formulas ke liye aap jaye ADVANCE COURSE PART 2 par jaye- MS Excel in Hindi full tutorial Advance course part 2 – MS Excel ke advance formulas

Agar aap chahein toh ye raw data aur final excel data sheet download kar sakte hain aur check kar sakte hain apne answers.
Sheets ka link niche diya hua hai.

I hope yeh beginner level functions aapko samajh aaye honge. Aur agar aapki koi query ya feedback ho toh aap niche comments sections mein zarur reply kare, hum aapki poori tarah se help karenge.

Josh talks ke courses harazo logo ko help kar rahe hai career mai aage badne ke liye. Iss link par click karke App download kare aur janiye courses ke baare mein

Yeh tutorial Hindi MS Excel full course in Hindi ka third part tha. Isko complete karne ke baad aap baaki 3 bhi kar sakte hain. Niche diye gaye links par click karke aap uss tutorial par ja sakte hain.

MS Excel in Hindi full tutorial Beginner course part 1 – Agar excel bilkul nahi aati

MS Excel in Hindi full tutorial Beginner course part 2 – MS Excel ko use karke data analysis kaise karte hai (Sort aur Filter kaise kare)

MS Excel in Hindi full tutorial Advance course part 2 – MS Excel ke advance formulas

Join Josh Skills Basic Computer Skills Course

Aaj kal computer ka zamana hai. Har voh kaam jisme pehle bahut time,effort aur money waste hota tha abh asani se computer pe kiya ja sakta hai. Computer ka istamaal aaj kal har taraf kiya jata hai. Basic computer skills ana aaj ke waqt mai bahat hi zaruri hai. Basic computer skills sikhna isliye zaruri hai take aap apne worklife ko easy aur efficient bana sako. Aur is computer technology ka wisely istamaal kar sako.

Is course mai aap Computer ke Basics pe gaur denge jaise:

• Basics of a Computer system and it’s operation.
• Internet Technology
• Email and Communication
• Use of Microsoft word, Microsoft Powerpoint and Microsoft Excel
• Introduction to Advanced Computer skills and tools
• Jobs that require computer skills

Toh aaj hi shuru kare apni learning journey Josh Skill ke courses ke sath.