Lokacin aiwatar da wasu ayyuka a cikin Excel, wani lokacin dole ne ku yi mu'amala tare da tebur da yawa, waɗanda kuma suke da alaƙa. Wannan shine, bayanai daga tebur ɗaya an ja zuwa wani kuma lokacin da aka canza su, ana sake tara abubuwa a duk matakan tebur masu dangantaka.
Tebur da aka haɗa suna da sauƙin amfani don sarrafa bayanai mai yawa. Don sanya duk bayanan a cikin tebur guda, banda, idan ba a yi kama da juna ba, bai dace ba. Yana da wuya a yi aiki da irin waɗannan abubuwan kuma a neme su. Matsalar da aka nuna an tsara don kawar da ta hanyar teburin da aka haɗa, bayanin tsakanin wanda aka rarraba, amma a lokaci guda yana haɗin gwiwa. Haɗin jerin tebur masu haɗin za a iya kasancewa ba a tsakanin takarda ɗaya ba ko littafi guda ba, amma kuma ana iya kasancewa a cikin littattafai daban (fayiloli). Zaɓuɓɓuka biyu na ƙarshen ƙarshe ana amfani da su sau da yawa a aikace, tunda manufar wannan fasaha ita ce ta nisanta kansu daga tarin bayanai, kuma tattara su a shafi ɗaya baya magance matsalar. Bari mu koyi yadda ake kirkira da kuma yadda ake aiki da wannan nau'in gudanar da bayanan.
Kirkirar Tables
Da farko dai, bari mu zurfafa tunani kan menene hanyoyin da ake da damar samar da alaƙa tsakanin jeri daban-daban.
Hanyar 1: teburin haɗin kai tsaye tare da dabara
Hanya mafi sauki don ɗaure bayanai ita ce amfani da dabarun amfani da jigon sauran jeri na tebur. Ana kiran shi dauri na madaidaici. Wannan hanyar tana da hankali, tunda tare dashi ana yin haɗi a kusan kamar yadda ake ƙirƙirar hanyar haɗi zuwa bayanai a tebur ɗaya.
Bari mu ga yadda, ta hanyar misali, za a iya kafa bond ta hanyar ɗaure kai tsaye. Muna da tebur biyu a kan zanen gado biyu. A tebur ɗaya, ana yin lada ta hanyar amfani da dabarar amfani ta hanyar ninka yawan ma'aikaci ta mai ba guda ɗaya don duka.
A takardar na biyu akwai jerin tebur, wanda ya ƙunshi jerin ma'aikata tare da albashin su. Jerin ma'aikata a bangarorin biyu an gabatar dasu a tsari iri daya.
Wajibi ne a tabbata cewa bayanai akan ragin daga takardar na biyu an ja su zuwa cikin sel masu dacewa.
- A saman takardar, zaɓi sel na farko a cikin shafi Biya. Mun sanya alama a ciki "=". Bayan haka, danna kan gajeriyar hanya "Shege 2", wanda yake a gefen hagu na kayan aikin Excel a saman sandar matsayin.
- Matsar zuwa yanki na biyu na takaddar. Mun danna kan tantanin farko a cikin shafi Biya. Saika danna maballin Shigar a kan madannin don shigar da bayanai a cikin tantanin da aka sa saiti a baya daidai.
- Sannan akwai canjin atomatik zuwa farkon takardar. Kamar yadda kake gani, ragin ma'aikaci na farko daga tebur na biyu an ja shi zuwa cikin tantanin da ya dace. Ta hanyar sanya siginar siginar a jikin tantanin da ke dauke da fare, mun ga cewa ana amfani da sigar da aka saba amfani da ita don nuna bayanai akan allon. Amma kafin daidaitawar tantanin halitta daga inda bayanan ke fitarwa, akwai magana "Sheet2!", wanda ke nuna sunan yankin daftarin aiki inda suke. Babban tsari a cikin yanayinmu yana kama da wannan:
= Sheet2! B2 - Yanzu kuna buƙatar canja wurin bayanai akan farashin duk sauran ma'aikatan kamfanin. Tabbas, ana iya yin hakan ta wannan hanyar da muka kammala aikin don ma'aikaci na farko, amma idan aka ba duk jerin biyun ma'aikata an tsara su a cikin tsari guda, za a iya sauƙaƙe aikin da saurin warwarewa. Ana iya yin wannan ta hanyar kwafin dabara zuwa kewayon da ke ƙasa. Saboda gaskiyar cewa hanyoyin haɗin cikin Excel suna da alaƙa da tsoho, lokacin da aka kwafa su, an ƙididdige halayen, wanda shine abin da muke buƙata. Za'a iya yin amfani da hanyar kwafin kanta ta amfani da alamar cikawa.
Don haka, sanya siginan kwamfuta a cikin ƙananan dama na kashi tare da dabara. Bayan haka, ya kamata a canza siginan kwamfuta alamar mai cike da alama ta hanyar giciye baƙar fata. Lamallon maɓallin linzamin kwamfuta na hagu ka ja siginar kwamfuta zuwa ƙarshen layin.
- Dukkanin bayanai daga wannan shafi mai kama akan Sheet 2 an jawo shi cikin tebur akan Sheet 1. Lokacin canza bayanai zuwa Sheet 2 za su canza ta atomatik a farkon.
Hanyar 2: ta amfani da tarin ayyukan INDEX - ITA
Amma idan jerin ma'aikata a cikin jerin teburin ba su cikin tsari ɗaya ba? A wannan yanayin, kamar yadda aka ambata a baya, ɗayan zaɓin shine a kafa haɗi tsakanin kowane ɗayan waɗancan sel ɗin da yakamata a haɗa su da hannu. Amma wannan ya dace da ƙananan allunan. Don manyan layuka, irin wannan zaɓi mafi kyawun zai ɗauki lokaci mai yawa don aiwatarwa, kuma a mafi munin, a aikace ba zai yuwu ba. Amma ana iya magance wannan matsalar ta amfani da tarin masu aiki INDEX - Neman. Bari mu ga yadda za a yi wannan ta hanyar haɗa bayanai a cikin jerin teburin da aka tattauna a cikin hanyar da ta gabata.
- Zaɓi kashi na farko na kashi Biya. Je zuwa Mayan fasalinta danna kan gunkin "Saka aikin".
- A Mayen aiki a cikin rukunin Tunani da Arrays Nemo da kuma nuna sunan INDEX.
- Wannan ma'aikacin yana da siffofi guda biyu: tsari don aiki tare da keɓaɓɓun bayanai da ɗayan juzu'i. A cikin lamarinmu, ana buƙatar zaɓi na farko, sabili da haka, a taga na gaba don zaɓar fom ɗin da zai buɗe, zaɓi shi kuma danna maɓallin. "Ok".
- An fara gwajin mai amfani da Operator INDEX. Aikin wannan aikin shine fitar da ƙimar da ke cikin zaɓin da aka zaɓa a cikin layin tare da lambar da aka ƙayyade. Janar janar na tsarin aiki INDEX kamar haka:
= INDEX (tsararru; jere_number; [column_number])Shirya - gardama mai dauke da adireshin kewayon wanda zamu tattara bayanai ta lambar layin da aka ƙayyade.
Lambar layi - gardamar, ita ce lambar wannan layin. Yana da mahimmanci a sani cewa lambar layi ba za'a kayyade shi daidai da duk takaddar ba, amma kawai dangi ne ga zaɓin da aka zaɓa.
Lambar Harafi - wata mahawara wacce ba tilas ba ce. Ba za mu yi amfani da shi don warware takamaiman matsalarmu ba, don haka ba lallai ba ne mu bayyana ainihin jigon shi dabam.
Sanya siginan kwamfuta a cikin filin Shirya. Bayan haka, je zuwa Sheet 2 kuma rike maɓallin linzamin kwamfuta na hagu, zaɓi duk abubuwan da ke cikin shafin Biya.
- Bayan an nuna ayyukan daidaitawa a cikin taga mai aiki, sanya siginan kwamfuta a fagen Lambar layi. Zamu fitar da wannan mahawara ta amfani da ma'aikaci Neman. Sabili da haka, mun danna kan alwatika, wanda ke gefen hagu na layin aikin. Jerin masu aiki da aka yi amfani da su kwanan nan ya buɗe. Idan kaga suna a cikinsu "Neman"sannan zaka iya danna shi. In ba haka ba, danna kan abu na ƙarshe akan jerin - "Sauran sifofin ...".
- Daidai taga yana farawa Wizards na Aiki. Mun wuce a ciki ga rukuni guda Tunani da Arrays. Wannan lokacin, zaɓi abu a cikin jerin "Neman". Latsa maballin. "Ok".
- Ana kunna yanayin mai muhawara mai aiki Neman. An ƙaddara aikin da aka ƙaddara don nuna adadin ƙimar a cikin takamaiman tsararru ta sunansa. Godiya ga wannan fasalin, zamu lissafta lambar layin takamaiman darajar don aikin INDEX. Syntax Neman wakilta kamar haka:
= SEARCH (bincike_value; lookup_array; [wasa_type])"Neman darajar" - gardamar mai ɗauke da suna ko adireshin tantanin ɓangaren ɓangare na uku wanda acikinta yake. Matsayin wannan sunan a cikin kewayon manufa wanda yakamata a lissafta. A cikin halinmu, gardama ta farko za ta kasance nassoshi kan sel Sheet 1inda sunayen ma'aikata suke.
Ganin Array - gardama mai wakiltar tunani game da tsararren ra'ayi wanda ake bincika ƙimar takamaiman don sanin matsayin sa. Adireshin shafi "zai taka wannan rawar anan."Sunan farko a kunne Sheet 2.
Nau'in Match - wata mahawara, wacce ba na tilas ba ce, amma, ba kamar bayanin da ta gabata ba, zamu buƙaci wannan hujja ta zaɓin. Yana nuna yadda mai aiki zai dace da ƙimar bincike tare da tsararru. Wannan hujja tana iya samun ɗayan dabi'u uku: -1; 0; 1. Don jerin gwanon waɗanda ba a kiyaye su ba, zaɓi "0". Wannan zabin ya dace da shari'ar mu.
Don haka, bari mu fara cike filayen taga muhawara. Sanya siginan kwamfuta a cikin filin "Neman darajar"danna wayar farko ta shafi "Suna" a kunne Sheet 1.
- Bayan an nuna alamun daidaitawa, saita siginan kwamfuta a fagen Ganin Array kuma danna kan gajeriyar hanya "Shege 2", wanda yake kasan windowasan window na Excel sama da matsayin matsayin. Riƙe maɓallin linzamin kwamfuta na hagu kuma zaɓi duk sel a cikin sashin tare da siginan kwamfuta "Suna".
- Bayan an daidaita abubuwan gudanarwarsu a fagen Ganin Arrayje filin Nau'in Match sannan saita lamba a wurin daga maballin "0". Bayan haka, mun sake dawowa filin daga Ganin Array. Gaskiyar ita ce za mu kwafa dabarar, kamar yadda muka yi a cikin hanyar da ta gabata. Canjin adireshin zai faru, amma a nan muna buƙatar gyara daidaitawar hanyoyin da ake kallo. Bai kamata a tarwatsa shi ba. Zaɓi masu daidaitawa tare da siginan kwamfuta kuma latsa maɓallin aikin F4. Kamar yadda kake gani, alamar dala ta bayyana a gaban daidaitawa, wanda ke nufin cewa hanyar haɗi ta juye daga dangi zuwa gaba ɗaya. Saika danna maballin "Ok".
- An nuna sakamakon a cikin tantanin farko na shafi. Biya. Amma kafin yin kwafin, muna buƙatar gyara wani yanki, wanda shine farkon gardamar aikin INDEX. Don yin wannan, zaɓi ɓangaren shafi wanda ya ƙunshi tsarin, sannan matsa zuwa layin dabara. Mun zaɓi farkon hujja na mai aiki INDEX (B2: B7) kuma danna maballin F4. Kamar yadda kake gani, alamar dala ta bayyana kusa da waɗanda aka zaɓa. Latsa maballin Shigar. Gabaɗaya, dabarar ta ɗauki tsari mai zuwa:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; Neman (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0)) - Yanzu zaku iya kwafin ta amfani da alamar mai cikawa. Muna kiranta ta wannan hanyar da muka yi magana game da farko, kuma shimfiɗa ta har zuwa ƙarshen kewayon tebur.
- Kamar yadda kake gani, duk da gaskiyar cewa jerin layi biyu na teburin da ke da alaƙa ba su dace ba, duk da haka, an ɗaga duk dabi'u bisa ga sunayen ma'aikata. An cimma wannan ta hanyar amfani da haɗin gwiwar masu aiki INDEX-Neman.
Karanta kuma:
Aikin EXEX a cikin Excel
Aikin EXCEL a cikin Excel
Hanyar 3: aiwatar da lissafi tare da bayanai masu alaƙa
Haɗin bayanan kai tsaye kuma yana da kyau saboda yana ba ku damar ba kawai nuna ƙimar da aka nuna a cikin sauran tebur a cikin ɗayan teburin ba, har ma suna yin ayyukan lissafi daban-daban tare da su (ƙari, rarrabuwa, rarrabuwa, ƙari, da sauransu).
Bari mu ga yadda ake aiwatar da wannan a aikace. Bari mu sa wancan Sheet 3 za a nuna bayanan albashin janar na kamfanin ba tare da rushewar ma'aikata ba. Don yin wannan, za a ja farashin ma'aikata Sheet 2, an taƙaita (ta amfani da aikin SAURARA) da kuma ninka ta hanyar matsala ta amfani da dabara.
- Zaɓi gidan tantance inda sakamakon ƙididdigar kuɗin fito zai nuna. Sheet 3. Latsa maballin. "Saka aikin".
- Window ya kamata farawa Wizards na Aiki. Je zuwa rukunin "Ilmin lissafi" kuma zaɓi sunan a wurin SAURARA. Bayan haka, danna maballin "Ok".
- An matsar da muhawara ɗin aikin zuwa taga SAURARA, wanda aka tsara don ƙididdige adadin lambobin da aka zaɓa. Ya na da Syntax mai zuwa:
= SUM (lamba1; lamba2; ...)Filayen da ke cikin taga sun dace da muhawara na aikin da aka ƙayyade. Dukda cewa adadinsu zai iya kaiwa 255, mutum daya ne zai isa ya zama dalilin mu. Sanya siginan kwamfuta a cikin filin "Lambar1". Danna maballin "Shege 2" sama da matsayin matsayin.
- Bayan mun matsa zuwa sashin da ake so na littafin, zaɓi allon da ya kamata a taƙaita. Muna yin wannan tare da siginan kwamfuta yayin riƙe maɓallin linzamin kwamfuta na hagu. Kamar yadda kake gani, daidaitawar yankin da aka zaɓa nan take ana nuna shi a filin taga muhawara. Saika danna maballin "Ok".
- Bayan haka, muna motsawa ta atomatik zuwa Sheet 1. Kamar yadda kake gani, an riga an nuna adadin kuɗin ma'aikaci a cikin kashi daidai.
- Amma wannan ba duka bane. Kamar yadda muke tunawa, ana kirga albashi ta hanyar ninka darajar kudin ta hanyar wani dalili. Sabili da haka, muna sake zaɓar tantanin da a taƙaice darajar yake. Bayan haka mun wuce zuwa layin tsari. Toara wa fola ɗin a ciki alamar nuna alama ce (*), sannan kuma danna kan abu wanda yake nuna alamar abu. Don aiwatar da lissafin, danna maɓallin Shigar a kan keyboard. Kamar yadda kake gani, shirin ya kirkiri jimlar albashin ma’aikatan.
- Koma baya Sheet 2 kuma canza rarar kowane ma'aikaci.
- Bayan haka, mun sake matsawa shafi tare da jimlar. Kamar yadda kake gani, saboda canje-canje a cikin teburin da aka haɗa, sakamakon jimlar albashin an sake tara shi ta atomatik.
Hanyar 4: shigarwar al'ada
Hakanan zaka iya danganta matakan tebur a cikin Excel ta amfani da saka ta musamman.
- Mun zaɓi dabi'u waɗanda zasu buƙaci "ja" zuwa wani tebur. A cikin yanayinmu, wannan shine kewayon shafi Biya a kunne Sheet 2. Mun danna kan guntun da aka zaɓa tare da maɓallin linzamin kwamfuta na dama. Cikin jeri dake buɗe, zaɓi Kwafa. Wani zaɓi gajerar hanyar rubutu ita ce Ctrl + C. Bayan haka mun matsa zuwa Sheet 1.
- Bayan mun matsa zuwa yankin littafin da muke buƙata, za mu zaɓi ƙwayoyin da za mu buƙatar ɗaga darajar. A cikin lamarinmu, wannan shafi ne Biya. Mun danna kan guntun da aka zaɓa tare da maɓallin linzamin kwamfuta na dama. A cikin menu na mahallin a toshe kayan aiki Saka Zabi danna alamar Manna hanyar haɗi.
Akwai kuma wani madadin. Ba zato ba tsammani, shi kadai ne na tsoffin juzu'i na Excel. A cikin mahallin menu, hau sama "Saka ta musamman". A ƙarin menu wanda yake buɗe, zaɓi wuri tare da sunan guda.
- Bayan haka, taga shigar ta musamman tana buɗewa. Latsa maballin Manna hanyar haɗi a cikin ƙananan kusurwar hagu na tantanin halitta.
- Koyaya zaɓi da kuka zaɓa, za'a ƙididdige abubuwa masu kyau daga tebur ɗaya a cikin wani. Lokacin canza bayanai a cikin tushen, suma zasu canza ta atomatik a cikin saitin da aka saka.
Darasi: Saka ta Musamman a cikin Excel
Hanyar 5: haɗi tsakanin tebur a cikin littattafai da yawa
Bugu da kari, zaku iya tsara sadarwa tsakanin bangarorin teburi a cikin litattafai daban-daban. Ana amfani da kayan aiki na musamman. Ayyukan za su yi daidai da waɗanda muka yi la’akari da su a hanyar da ta gabata, sai dai in da za ku bincika yayin samar da dabaru ba tsakanin wuraren littafin guda ba, amma tsakanin fayiloli. Ta halitta, duk littattafan da suka shafi ya kamata su buɗe.
- Zaɓi kewayon bayanan da kake son turawa zuwa wani littafin. Danna-dama akansa kuma zaɓi matsayi a cikin menu wanda ya buɗe. Kwafa.
- Sannan mun matsa zuwa littafin wanda wannan bayanan zai buƙaci shigar dashi. Zaɓi kewayon da ake so. Danna dama. A cikin mahallin menu a cikin rukuni Saka Zabi zaɓi abu Manna hanyar haɗi.
- Bayan haka, za'a shigar da dabi'u. Lokacin da bayanai a cikin littafin aikin asalin ya canza, tebur ɗin jeri daga littafin aikin zai ɗaga shi kai tsaye. Haka kuma, ba lallai ba ne dukkanin littattafan su kasance a buɗe don wannan. Ya isa a buɗe littafin aiki guda kawai, kuma za ta zazzage bayanai ta atomatik daga takaddar haɗin da aka rufe idan an yi canje-canje a da.
Amma ya kamata a lura cewa a wannan yanayin ana sanya siginar azaman mai ɗaukar hoto. Lokacin da kake ƙoƙarin sauya kowane tantanin halitta tare da bayanan da aka saka, saƙon zai nuna maka cewa ba zai yiwu a yi wannan ba.
Canje-canje a cikin irin wannan tsari da ke da alaƙa da wani littafin za a iya yin shi ta hanyar fasa hanyar haɗi.
Fitowa tsakanin tebur
Wani lokaci kuna buƙatar warware haɗin tsakanin jeri na tebur. Dalilin haka na iya zama ko bayanin da aka bayyana a sama, lokacin da kake buƙatar canza tsararren daka saka daga wani littafin, ko kuma kawai rashin yarda da mai amfani cewa bayanan da ke cikin tebur ɗaya an sabunta su ta atomatik daga wani.
Hanyar 1: warware haɗin tsakanin littattafai
Kuna iya karya haɗin tsakanin littattafai a cikin dukkanin ƙwayoyin cuta ta hanyar yin kusan aiki ɗaya. A lokaci guda, bayanan da ke cikin sel za su wanzu, amma za su riga sun zama ƙa'idodi marasa ɗaukakawa, wanda a wata hanya ba ya dogara da wasu takardu.
- A cikin littafin, wanda aka jawo dabi'u daga wasu fayiloli, je zuwa shafin "Bayanai". Danna alamar "Canza sadarwa"wacce take akan kintinkiri a cikin akwatin kayan aiki Haɗin kai. Ya kamata a lura cewa idan littafin yanzu ba ya ƙunshi hanyoyin haɗi zuwa wasu fayiloli, to wannan maɓallin ba ya aiki.
- Ana buɗe taga don canza hanyoyin haɗin yanar gizo. Mun zaɓi fayil ɗin da muke so mu karya haɗin daga jerin littattafan da aka danganta (idan akwai da yawa). Latsa maballin Yanke hanyar haɗin.
- Ana buɗe taga bayani, a ciki akwai gargadin game da sakamakon ƙarin ayyuka. Idan kun tabbatar da abin da zaku yi, to danna maballin "Yankan hulda".
- Bayan haka, duk hanyar haɗi zuwa fayil ɗin da aka ƙayyade a cikin takaddun yanzu za a maye gurbinsu da ƙimin almara.
Hanyar 2: Sanya Ka'idodi
Amma hanyar da ke sama ta dace ne kawai idan kuna buƙatar lalata duk hanyoyin haɗin tsakanin littattafan guda biyu. Me za ku yi idan kuna buƙatar raba allunan da ke da alaƙa waɗanda ke tsakanin fayil ɗin ɗaya? Kuna iya yin wannan ta hanyar kwafa bayanai sannan kuma wuce shi ta wuri guda da dabi'u. Af, a cikin hanyar, za ku iya karya haɗin tsakanin jigon bayanan bayanan littattafai daban-daban ba tare da keta haɗin gaba ɗaya tsakanin fayilolin ba. Bari mu ga yadda wannan hanya take aiki a aikace.
- Zaɓi kewayon da muke so mu cire hanyar haɗi zuwa wani tebur. Mun danna shi tare da maɓallin linzamin kwamfuta na dama. A menu na buɗe, zaɓi Kwafa. Madadin waɗannan ayyuka, zaku iya buga madadin madadin maɓallan wuta Ctrl + C.
- Ci gaba, ba tare da cire zaɓi daga guntun yanki ɗaya ba, sake danna kan dama. Wannan lokacin a jerin ayyukan, danna kan gunki "Dabi'u"wanda ke cikin rukunin kayan aiki Saka Zabi.
- Bayan haka, duk hanyar haɗi a cikin kewayon da aka zaɓa za a maye gurbinsu da ƙimar lambobi.
Kamar yadda kake gani, a cikin Excel akwai hanyoyi da kayan aikin haɗin haɗin tebur da yawa tare. A lokaci guda, bayanan tabular na iya zama akan wasu zanen gado har ma a cikin litattafai daban-daban. Idan ya cancanta, ana iya raba haɗin wannan haɗin kai tsaye.
