Tổng hợp bài tập Excel từ cơ bản đến nâng cao dành cho người mới | Aviationaustralia.asia

Ayyukan motsa jiki na Excel suna haɗa nau’ikan ƙididdiga da ƙididdiga lokacin da kuke amfani da ayyuka na asali a cikin Excel ko ci gaba lokacin da akwai nau’ikan motsa jiki da yawa waɗanda ke buƙatar masu amfani su haɗa ayyuka da yawa. Bari mu koyi game da shi tare da 123job.

Ga waɗanda galibi suna yin ayyuka tare da software na Excel, masu ba da lissafi, waɗanda ke da cikakkiyar fahimtar ayyuka, ƙididdigewa gami da amfani da buƙatu, ko ka’idar asali kamar tsara bayanai, Tsarin Fayil yana da mahimmanci. Da zarar kun mallaki ka’idar, za ku iya aiwatar da ita a aikace excel motsa jiki don ƙarin fahimtar yadda ake amfani da su. Wadannan Ayyukan motsa jiki na Excel Anan akwai darussan da mafita da wasu Advanced excel atisayen Babu wata mafita gare ku don amfani da ilimin ku don gudanar da aikin. Za a yi motsa jiki da aka raba ta ayyuka daban-daban kuma akwai excel motsa jikia cikin jimillar tsari tare da buƙatun abun ciki mafi girma.

I. Zazzage darasi na Excel tare da amsoshi

Basic excel exercises

Basic excel atisayen

1. Ayyukan lissafi

Ƙididdigar ƙididdiga a cikinAyyukan Excel tare da mafitaHakanan akwai da yawa, kamar ƙari a cikin Excel ko ƙarin ci gaba waɗanda zasu iya ƙididdige ƙimar juzu’i.

2. Ayyukan aikin Excel

Ayyukan Excel sun kasu kashi daban-daban na ayyuka daban-daban, irin su ayyukan ƙididdiga, ayyuka na lissafi da trigonometric, ayyuka na kwanan wata da lokaci kamar aikin DATE, aikin bayanai, aikin DCOUNT … Ayyuka a cikin aikin motsa jiki za a iya haɗa su tare da juna zuwa juna. warware teburin bayanai kamar ayyukan FIND haɗe tare da ayyukan HAGU da ayyukan MID don raba jerin bayanai….

2.1 Ayyukan Lissafi a cikin Excel

Tare da misalai kamar na ƙasa, mun riga mun sami dabarar aiki kuma kawai kuna buƙatar shigar da daidai matsayin sel don ƙimar da za a ƙidaya a ciki. Ayyukan Excel tare da mafita yi.

2.2 Aikin SUM ya haɗu da zanen gado da yawa

Ana ɗaukar aikin SUM azaman ainihin aiki a ciki Ayyukan Excel tare da mafita kuma aiwatar da aikin SUM shima mai sauqi ne. Misali da Ayyukan Excel tare da mafita A wannan yanayin, kawai kuna buƙatar ƙara ƙima a cikin kowane tantanin halitta ta cikin zanen gado don samun jimlar ƙimar abubuwan.

2.3 IF aiki a cikin Excel

Ayyukan IF aiki ne na sharadi a cikin Excel, gano ƙimar da ta dace da sharuɗɗan da aka bayar.

Akwai kuma da yawa excel motsa jiki don bayanin yadda ake warwarewa da kuma amfani da teburin bayanai da kuke buƙatar warwarewa

Zazzage motsa jiki na Excel tare da mafita NAN

II. Ayyukan motsa jiki na asali na Excel

Basic excel exercises 1

Basic excel atisayen

Ga taƙaitaccen bayanin Ayyukan motsa jiki na Excel Don tushen ayyuka a cikin Excel, da fatan za a duba su don sanin yadda ake amfani da ayyuka a cikin Excel da yadda ake haɗa ayyuka a cikin Excel.

  • ABS aikin samfurin motsa jiki

  • DA aikin samfurin motsa jiki

  • COUNT. aikin samfurin motsa jiki

  • Matsakaicin aikin samfurin motsa jiki

  • Misalin motsa jiki GASKIYA

  • COUPPD. aikin samfurin motsa jiki

  • DDB.samfurin aikin motsa jiki

  • INT. samfurin aikin motsa jiki

  • MAX. samfurin aikin motsa jiki

  • MIN. aikin samfurin motsa jiki

  • Misalin motsa jiki LOGNORMDIST.

  • NORMSDIST aikin samfurin motsa jiki.

  • Misalin motsa jiki ta amfani da aikin HLOOKUP hade da aikin IF.

  • Misalin motsa jiki ta amfani da aikin HLOOKUP hade da aikin HAGU.

  • Misalin motsa jiki ta amfani da aikin VLOOKUP hade da aikin HLOOKUP.

  • Misalin motsa jiki ta amfani da aikin VLOOKUP hade tare da IFERROR

  • Misalin motsa jiki ta amfani da aikin VLOOKUP hade da aikin RIGHT.

  • Misalin motsa jiki ta amfani da aikin VLOOKUP hade da aikin MATCH.

  • Misalin motsa jiki yana amfani da aikin VLOOKUP don ƙididdige maki.

  • Misalin motsa jiki ta amfani da aikin VLOOKUP don lissafin albashi.

READ  Soạn bài Lưu biệt khi xuất dương (Phan Bội Châu) | Aviationaustralia.asia

Zazzage 40+ ainihin darasi na ayyuka a cikin Excel nan.

Kuna iya zazzage fayiloli kuma buɗe su Ayyukan motsa jiki na Excel bisa ga kowane aikin da kuke buƙatar koya. Da fatan za ku iya fahimtar amfanin kowane aiki don ku iya amfani da shi zuwa wasu ayyuka Advanced Excel motsa jiki. Sa’a!

III. Basic Excel motsa jiki ta topic

Darasi na 1: Lissafin lissafin albashi

Advanced excel exercises 1

Advanced excel exercises 1

  1. Shigar da dabarar don yin lissafin masu zuwa:

    • LC = HS * 1260000

    • Inshorar zamantakewa = 0.05 * LC

    • Bonus:
      Idan NC> 25, Bonus = LC * 2
      Idan 23 Idan NC

    • Aminci = LC + Bonus – Social Insurance

    • Lissafin Jima’i, Matsakaici, Matsakaicin, Mafi qarancin layuka don ginshiƙan lamba.

  2. Tsara shi ta hanyar ginshiƙin NC mai saukowa

  3. Nemo mutane masu HS>= 3 da NC> 24

  4. Yi ƙirƙira jadawalin ginshiƙi na 3-D na ginshiƙan NC, HS

Dubi umarnin

Darasi na 2: Lissafin amfanin shinkafa

Advanced excel exercises 1

Advanced excel exercises 2

  1. Shigar da dabarar don yin lissafin masu zuwa:

    • Tsara ginshiƙai bisa ga tsare-tsare masu zuwa:
      Rubuta = ‘A’ idan Matsakaici> 1500
      Nau’in = ‘B’ idan 1300 Type = ‘C’ idan Matsakaici

    • Rukunin farashi: Yi amfani da aikin Vlookup don duba farashin da ke ƙasa don cike bayanai a cikin ginshiƙin Farashin

    • Yi lissafin Max, Min don ginshiƙai (sai dai Nau’in ginshiƙi).

  2. Tsara shi ta hanyar shafi Ns 99 mai saukowa

  3. Nemo nau’in shinkafa tare da Ns 99> 1400 da matsakaiciyar yawan amfanin ƙasa> 1300

  4. Zana jadawali a cikin hanyar layi don kwatanta yawan amfanin gonar shinkafa N1, N2, N3 a cikin shekaru 3.

Dubi umarnin

Darasi na 3: Kididdigar kudaden shiga

Advanced excel exercises 3

Advanced excel exercises 3

  1. Shigar da dabarar don yin lissafin masu zuwa:

    • Yi lissafin nau’in shafi bisa ga dabara mai zuwa:
      Rubuta = “A” idan Matsakaici> 1600
      Grade = “B” idan 1400 Grade = “C” idan Matsakaici

    • Ƙididdigar ginshiƙi na Zuba Jari: Yi amfani da aikin Vlookup don duba matakin saka hannun jari a ƙasa sannan a cika bayanai a cikin ginshiƙin Zuba jari.

    • Yi lissafin Max, Min don ginshiƙai (sai dai Nau’in ginshiƙi).

  2. Rarraba ta hanyar shiga ginshiƙai 95 da ke saukowa.

  3. Nemo rukunin tare da samun kudin shiga 97> 1600 da matsakaicin kudin shiga> 1300.

  4. Zane 3-D Pie na Abubuwan Da Aka Samu ginshiƙai 97.

Dubi umarnin

Darasi na 4: Lissafin maki

Advanced excel exercises 4

Advanced excel exercises 4

Rubuce-rubucen Jarrabawar Semester na 1st 1999

  1. Shigar da dabarar don yin lissafin masu zuwa:

    • Jimlar = Makin Lissafi * 5 + Makin Labarai * 5+ Makin Kimiyya * 4

    • Matsakaici = (Makin Lissafi * 5 + Makin Labarai * 5 + Makin Kimiyya * 4) / 14

    • Yi lissafin nau’in shafi bisa ga dabara mai zuwa:
      Class = “A” idan Matsakaici >= 9
      Grade = “B” idan 7 Grade = “C” idan Matsakaici

    • Yi ƙididdige ginshiƙin malanta: Yi amfani da aikin Vlookup don bincika matakin malanta da ke ƙasa don cike bayanai a cikin ginshiƙin Siyarwa.

    • Yi lissafin Max, Min don ginshiƙai (sai dai Nau’in ginshiƙi).

  2. Tsara ta hanyar saukowa ginshiƙan Makin Lissafi.

  3. Nemo ɗalibai masu Jima’i> 108 da Maki>= 7

  4. Hoton zane a cikin 3-D Tsarin Rukunin Lissafi na Makin Lissafi, Rukunin Makin Labarai.

READ  Ký hiệu điện tử cơ bản | Aviationaustralia.asia

Dubi umarnin

Darasi na 5: Kididdigar tallace-tallace

Advanced excel exercises 5

Advanced excel exercises 5

KIdiddigan Kayayyakin

  1. Shigar da waɗannan ƙididdiga don yin lissafin masu zuwa:

    • Sabis = 5% Babban Jari.

    • Haraji = 10% na Haraji.

    • Haqiqa kudaden shiga = Haraji – Babban Jari – Haraji – Sabis

    • Shagon nazari:
      Idan Haraji na Gaskiya> 0 rubuta “Riba”, tare da 0 rubuta “Break Even”, in ba haka ba rubuta “Asara”.

    • Kididdigar Jimillar Babban Jari da Harajin Shinkafa.

  2. Rarraba ta Babban ginshiƙan Masu Saukowa.

  3. Nemo kantuna masu Kuɗi> 60 da Babban> 70.

  4. Shirya jadawali a cikin nau’i na XY wanda ke wakiltar daidaitawa tsakanin ayyukan y shine Kuɗi kuma madaidaicin x babban jari ne.

Dubi umarnin

Darasi na 6: Kididdigar lamuni

Advanced excel exercises 6

Advanced excel exercises 6

KIdiddigan lamuni

  1. Shigar da waɗannan ƙididdiga don yin lissafin masu zuwa:

    • Shekarar biya = Shekarar lamuni + Lamuni

    • Adadin da za a biya = Shugaban makaranta + Shugaba * Riba * Lamuni.

    • Yi lissafin fifikon raka’o’in bisa ga dabara mai zuwa:
      Idan Term ɗin Lamuni>= 7, matakin fifiko shine “Tier 1”.
      Idan 4 Idan Loan Term

    • Jimlar da matsakaitan ginshiƙai: Babban, Adadin da za a biya

  2. Tsara bayanan ta asalin ginshiƙi cikin tsari mai saukowa.

  3. Nemo kamfani mai babba> 40 da lokacin lamuni>= 4.

  4. Zana ginshiƙi na layi don ginshiƙai 2 na Shugaban Makarantar da Lamuni.

Dubi umarnin

Darasi na 7: Lissafin maki a jami’a

Advanced excel exercises 7

Advanced excel exercises 7

HALAYEN COLLEGE

  1. Ƙididdiga jimlar adadin raka’a = Adadin kayan aiki na M1, M2, M3 hade

  2. Yi amfani da VLOOKUP don bincika raka’a na kowane batu kuma don ƙididdige matsakaicin maƙiya bisa ga maƙasudi masu zuwa: Matsakaicin maƙiya = (M1*Lambar majors na M1 + M2*N na majors na M2 + …)/Jimlar adadin Rukunin ilimi na M2 + …)

  3. Cika shafi game da Rating:

  • Madalla: Matsakaicin maki>= 8

  • Shafin: 6.5

  • TB: 5

  • Talauci: Matsakaicin Maki

  1. Yi ƙididdige jimillar ginshiƙai, da matsakaita, adadin (kasa) buƙatun M1, M2, M3 da Matsakaicin Maki (Madaidaici: Matsakaici Maki>=5, Mara gamsarwa: Matsakaicin Maki).

  2. Ranking: Matsayin ɗaliban da ke cikin jeri bisa matsakaicin maki (dalibi mai matsakaicin maƙi yana matsayi na 1st)

  3. Zana XY Scatter ginshiƙi don nuna alaƙa tsakanin maki M1 da M2

  4. Mu tace daliban da maki M1 ke tsakanin 6 da 8

  5. Tsara allon maki cikin tsari mai saukowa ta jimlar ginshiƙi da ginshiƙi na Rank

Dubi umarnin

Darasi na 8: Jerin farashin hayar otal

Advanced excel exercises 8

Advanced excel exercises 8

JERIN KUDIN HAYA Otel

  1. Yi lissafi:

  • Yawan kwanakin zama = Kwanan tashi – Ranar isowa

  • Za a ƙididdige ƙimar ɗaki ta hanyar bincike ta nau’in ɗaki daidai da baƙon zama tare da Matsakaicin Raka’a na yau da kullun na kowane ɗaki na kowane nau’in wanda aka ninka da adadin kwanakin zama.

  • Za a ƙididdige kuɗin sabis a cikin tebur na ƙimar ɗaki wanda aka ninka ta ƙimar ɗaki

  • Biyan zai kasance daidai da jimlar kuɗin ɗakin + kuɗin sabis

  1. Tsara ta Biyan kuɗi a cikin tsari mai saukowa

  2. Tace dakuna tare da Deposit>= 5000000

  3. Yi ƙirƙira hoto na 3-D Pie na biyan kuɗi ta nau’in ɗaki.

READ  Quản trị dự án là gì? Bí quyết vươn tới đỉnh cao khi quản trị dự án | Aviationaustralia.asia

Dubi umarnin

Darasi na 9: Rahoton sakamakon shigar jami’a

Advanced Excel Exercises 9

Advanced Excel Exercises 9

RAHOTO AKAN SAKAMAKON JAMI’A

Yi lissafi

  • Yin amfani da aikin IF don ƙididdige UT1 (makin fifiko bisa ga kabilanci), mutanen Kinh suna da UT1 = 0, Tay da Thai suna da UT1 = 1, sauran kabilu suna da UT1 = 1.5

  • Yi amfani da aikin VLOOKUP don ƙididdige UT2 (makin fifiko bisa ga yankuna): Yanki 1 yana da UT2 = 0, Yanki 2 yana da UT2 = 1, Yanki 3 yana da UT2 = 1.5

  • Idan ɗan takarar ya sami Mahimmin daraja, jimillar makin zai zama 1 .

  • Sakamakon shine Wucewa kuma idan Jimlar Maki>=20, ragowar ba za a yi nasara ba

Tsara zanen gado da ke gangarowa ta jimloli, Yankuna

Nemo ‘yan takarar da ba Kinh ba kuma sakamakon shine Do

Zana zanen layi a cikin salon lamba 4 don kwatanta Makin Gwaji da Jimillar Maki

IV. Basic Excel ayyuka don lissafin kudi da ofis

1. Buɗe littattafan aikin Excel da yawa a lokaci ɗaya

Maimakon buɗe kowane littafin aikin Excel kawai lokacin da akwai fayiloli da yawa don magance su, zaku iya buɗe maƙunsar bayanai na Excel da yawa a lokaci ɗaya. Don yin haka, zaɓi fayilolin da kuke son buɗewa kuma danna maɓallin Shigar, duk takaddun aiki za a buɗe a lokaci guda.

2. Nuna/ɓoye gajerun hanyoyin madannai

Waɗannan ayyuka ne masu ban sha’awa waɗanda ba a san su ba a cikin Excel 2007 da kuma daga baya. Lokacin da ka zaɓi danna maɓallin Alt, gajerun hanyoyin za su bayyana daidai a kan kayan aiki kuma su dace da waɗannan kayan aikin. Don amfani da shi, kawai kuna buƙatar danna gajeriyar hanyar keyboard Alt + daidai. Wannan yana taimaka muku baya buƙatar tunawa da maɓallai kuma har yanzu kuna iya amfani da su cikin sauƙi don haɓaka aikinku.

3. Zaɓi duk bayanai

Kuna iya sanin hanyoyi da yawa don zaɓar duk bayanai ta hanyar danna maballin gajeriyar hanya Ctrl + A, amma mutane kaɗan ne za su san hakan, kawai danna kusurwar hagu na sama na maƙunsar bayanai zai zaɓi duk bayanai.

4. Maida layuka zuwa ginshiƙai ko akasin haka

Wataƙila kun gama ƙirƙirar tebur kuma kuna son canza layuka zuwa ginshiƙai ko ginshiƙai zuwa layuka. Ga yadda za a yi:

Zaɓi layi (ko shafi) da kuke buƙatar motsawa, sannan danna Kwafi akan menu (Ctrl + C)

Zaɓi cell ɗin da kake son liƙa sabon tebur kuma zaɓi Manna Special akan menu (ko Alt + E -> sannan danna maɓallin S)

A cikin akwatin maganganu na Manna na Musamman, zaɓi Transpose kuma danna Ok.

5. Canja tsakanin takardun aikin Excel daban-daban

Lokacin da kuke buɗe maƙunsar maƙunsar bayanai a lokaci ɗaya, juyawa da baya da baya tsakanin maƙunsar bayanai daban-daban na iya zama abin takaici. Yi amfani da gajeriyar hanyar keyboard Ctrl + Tab don canzawa tsakanin maƙunsar bayanai cikin sauƙi. Hakanan za’a yi amfani da wannan aikin don canzawa tsakanin shafuka daban-daban na Windows a Firefox akan Windows 7 tsarin aiki.

Ainihin, akwai Menu na Gajerun hanyoyi 3: Ajiye, Gyara Buga da Maimaita Buga. Koyaya, idan kuna son amfani da ƙarin gajerun hanyoyin keyboard, kamar idan kuna son Kwafi ko Yanke, zaku iya saita shi kamar haka:

Fayil-> Zaɓuɓɓuka-> Kayan aikin Samun Sauri, ƙara Yanke da Kwafi daga ginshiƙin hagu zuwa ginshiƙin dama kuma adana. Za ku ga za ku iya ƙara waɗannan zaɓuɓɓukan gajerun hanyoyi guda biyu a cikin menu na sama.

7. Yadda ake ƙara layin diagonal a cikin tantanin halitta

Da zarar kun ƙirƙiri jerin kayayyaki ko kayayyaki, ƙila ku ma kuna buƙatar ƙirƙirar layin diagonal daga inda za ku raba halaye daban-daban don layuka da ginshiƙai. Yawancin lokaci kuna yin haka: A cikin tantanin halitta da kuke son zana diagonal, danna dama kuma zaɓi Format Cell> danna cikin Border tab, sannan danna maɓallin diagonal.

V. Kammalawa

Anan ga ayyukan game da Basic excel exercises amma mai matukar amfani wanda kuke buƙatar fahimta da koyon yadda ake amfani da ƙwarewa don taimakawa wajen adana lokaci, gami da ƙoƙari da haɓaka aikinku. Hakanan, haɓaka ƙwarewar ku akai-akai excel motsa jiki don inganta fasahar kwamfuta na ofis. 123job yana muku fatan nasara.

Trả lời

Email của bạn sẽ không được hiển thị công khai.

Protected with IP Blacklist CloudIP Blacklist Cloud