DIANGKAH SPREADSHEETS ING EXCEL

Nalika nindakake tugas tartamtu ing Excel, kadhangkala sampeyan kudu nangani sawetara tabel, sing uga ana hubungane karo siji liyane. Dadi, data saka siji tabel ditarik menyang sing liya, lan nalika owah-owahan, angka-angka ing kabeh kisaran tabel sing gegandhengan bakal dihitung maneh.

Pirembagan pranala banget migunani kanggo ngolah informasi akeh. Iku ora trep banget kanggo duwe kabeh informasi ing salah siji meja, lan yen ora kabeh podho. Iku angel bisa digarap obyek kasebut lan nggoleki. Masalah iki diarani kanggo ngilangi tabel sing gegandhengan, informasi antarane sing disebarake, nanging ing wektu sing padha interrelated. Jangkoan tabel kasebut bisa ditemokake ora mung ana ing siji lembar utawa siji buku, nanging uga ana ing buku kapisah (file). Ing praktik, rong opsi pungkasan sing paling kerep digunakake, amarga tujuan teknologi iki kanggo nyingkirake saka akumulasi data, lan piling padha ing kaca sing padha ora mesthine ngatasi masalah kasebut. Ayo sinau carane nggawe lan cara kanggo nggarap jenis manajemen data iki.

Nggawe tabel sing disambung

Kaping pisanan, ayo padha ngrampungake pitakonan kepriye bisa nggawe link antarane beda-beda tabel.

Cara 1: Nggabung langsung tabel kanthi rumus

Cara paling gampang kanggo ngubungake data yaiku nggunakake formula sing nyambung menyang tabel liyane. Iki diarani langsung ngiket. Cara iki intuisi, amarga kanthi kasebut pengikatan dilakokake kanthi meh padha karo cara nggawe referensi kanggo data ing sawijining tabel tabel.

Ayo kita ndeleng carane conto bisa mbentuk ikatan kanthi ngiket langsung. Kita duwe rong tabel ing rong lembar. Ing siji tabel, upah diwajibake nggunakake rumus kanthi ngalikan tingkat buruh kanthi tingkat siji kanggo kabeh.

Ing lembar kapindho ana jangkoan tabular sing ana dhaptar karyawan karo gaji sing padha. Dhaptar karyawan ing loro kasus kasebut diwenehi urutan sing padha.

Sampeyan kudu nggawe supaya data ing tarif saka sheet kapindho bakal ditarik ing sel sing cocog sing kapisan.

Ing sheet pisanan, pilih sel asli kolom. "Bet". We sijine ing dheweke tandha "=". Sabanjure, klik label "Lembar 2"Kang dumunung ing sisih kiwa antarmuka Excel ing sadhuwure baris status.

Pindhah menyang area kapindho ing document. Klik ing sel pisanan ing kolom. "Bet". Banjur klik tombol. Ketik ing keyboard kanggo nindakake entri data ing sel sing wis ditemtokake sadurunge padha karo.

Banjur ana transisi otomatis menyang lembaran kapisan. Minangka sampeyan bisa ndeleng, tingkat karyawan pisanan saka meja kapindho bakal ditarik menyang sel sing cocog. Sawise nglebokake kursor ing sèl sing ngemot taruhan, kita sumurup rumus biasa digunakake kanggo nampilake data ing layar. Nanging sadurunge koordinat sèl endi data ditampilake, ana ekspresi "Sheet2!"kang nuduhake jeneng wilayah saka papan ing ngendi panggonan kasebut. Rumus umum ing kasus kita yaiku kaya mangkene:
= Sheet2! B2

Saiki sampeyan kudu ngirim data ing tingkat kabeh karyawan perusahaan liyane. Mesthi, iki bisa dilakoni kanthi cara sing padha supaya kita bisa nglampahi tugas kanggo karyawan sing kapisan, ananging diwenehake yen dhaptar karyawan kasebut disusun kanthi urutan sing padha, tugas kasebut bisa disederhanakaké lan bisa nyepetake solusi kasebut. Iki bisa dilakoni kanthi mung nyalin rumus menyang sawetara ngisor iki. Amarga kasunyatane menawa pranala ing Excel relatif sacara standar, nalika disalin, nilai-nilai sing diganti, yaiku apa sing kita butuhake. Prosedur nyalin kasebut bisa uga ditindakake kanthi nggunakake marker isi.
Dadi, sijine kursor ing sisih tengen ngisor unsur kanthi formula. Sawise kuwi, kursor kudu dikonversi dadi isi salib ireng. Kita nindakake penjepit tombol mouse kiwa lan seret kursor menyang dhasar banget kolom.

Kabeh data saka kolom sing padha ing Lembar 2 padha ditarik menyang meja Lembar 1. Nalika data diganti Lembar 2 bakal kanthi otomatis ngganti dhisik.

Metode 2: migunakake sekelompok operator INDEX - MATCH

Nanging apa menawa dhaftar karyawan ing tataran tabular ora disusun kanthi urutan sing padha? Ing kasus iki, kaya kasebut ing ngisor iki, salah sijine opsi kanggo nyetel sambungan antarane saben sel sing kudu disambung kanthi manual. Nanging iki mung cocok kanggo tabel cilik. Kanggo jangkoan sing gedhé, pilihan iki, paling apik, bakal njupuk wektu akeh kanggo ngleksanakake, lan ing paling awon - ing praktik kasebut ora bisa dilakoni. Nanging sampeyan bisa ngatasi masalah iki karo akeh operator INDEKS - MATCH. Ayo ndeleng carane iki bisa dilakoni kanthi ngubungaké data ing kisaran tabular, sing dibahas ing cara sadurungé.

Pilih item sing pisanan ing kolom kasebut. "Bet". Bukak Wisaya Fungsikanthi ngeklik ing lambang "Pasang fungsi".

Ing Wisaya fungsi ing grup "Pranala lan susunan" golek lan pilih jeneng INDEKS.

Operator iki duwe rong wujud: sawijining wangun kanggo nggarap tataran lan referensi. Ing kasus kita, pilihan pisanan dibutuhake, supaya ing jendhela sabanjure kanggo milih wujud sing bakal mbukak, kita milih lan klik tombol "OK".

Jendhela argumen operator wis mlaku. INDEKS. Tugas saka fungsi kasebut yaiku kanggo nampilake angka sing ana ing jangkoan sing dipilih ing baris karo nomer kasebut. Rumus operator umum INDEKS iki:
= INDEX (array; line_number; [column_number])
"Array" - pitakonan sing ngemot alamat jangkah saka ngendi kita bakal ngekstrak informasi kanthi nomer senar sing kasebut.
"Nomer baris" - pitakonan sing nomer saka baris kasebut. Penting kanggo ngerti yen nomer baris kudu ora ditemtokake relatif marang kabeh dokumen, nanging mung relatif marang array sing dipilih.
"Nomer kolom" - Argumentasi punika opsional. Kanggo ngatasi masalah kita sacara khusus, kita ora bakal nggunakake, lan mulane ora perlu kanggo njlentrehake esensi kasebut kanthi kapisah.
Sijine kursor ing lapangan "Array". Sawise iku pindhah menyang Lembar 2 lan, nyekel tombol mouse kiwa, pilih kabeh isi kolom "Bet".

Sawise koordinat dituduhake ing jendhela operator, sijine kursor ing lapangan "Nomer baris". Kita bakal nampilake pitakonan iki nggunakake operator MATCH. Mulane, klik ing segitiga sing ana ing sisih kiwa garis fungsi. Dhaptar operator sing mentas dienggo mbukak. Yen sampeyan nemokake jeneng kasebut "MATCH"banjur sampeyan bisa klik ing. Yen ora, klik ing item paling anyar ing dhaptar - "Fitur liyane ...".

Jendhela standar diwiwiti. Fungsi master. Bukak kana ing grup sing padha. "Pranala lan susunan". Wektu iki ing dhaptar, pilih item kasebut "MATCH". Nindakake klik ing tombol kasebut. "OK".

Ngaktifake bantahan jendhela operator MATCH. Fungsi kasebut dimaksudkan kanggo nampilake angka sing ana ing array khusus kanthi jenenge. Thanks kanggo kesempatan iki, kita bakal ngetung nomer baris saka nilai tartamtu kanggo fungsi kasebut. INDEKS. Sintaks MATCH presented as:
= MATCH (nelusur nilai; array panggilange; [match_type])
"Nilai" - pitakonan sing ngemot jeneng utawa alamat sel kisame pihak katelu. Iku posisi jeneng kasebut ing kisaran target sing kudu diwilang. Ing kasus kita, argumen pisanan bakal referensi sèl kanggo Lembar 1ing ngendi jenenge karyawan.
"Deleng" - sawijining pitakonan sing makili sawijining pranala menyang array sing nemtokake nilai kasebut kanggo nemtokake sawijining posisi. Kita bakal muter kolom alamat peran iki "Jeneng pisanan ing Lembar 2.
"Pemetaan Tipe" - pitakonan sing opsional, nanging, ora kaya pandhuan sadurungé, kita butuh argumen opsional. Iku nuduhake carane operator bakal cocog karo nilai sing dikarepake kanthi array. Argumentasi iki bisa duwe salah siji saka telung nilai: -1; 0; 1. Kanggo tataran tanpa urutan, pilih opsi kasebut "0". Pilihan iki cocok kanggo kasus kita.
Dadi, ayo mulai ngisi ing kolom saka jendhela argumen. Sijine kursor ing lapangan "Nilai", klik ing sel pisanan kolom kasebut "Jeneng" ing Lembar 1.

Sawise koordinat katon, setel kursor ing lapangan "Deleng" lan pindhah ing trabasan "Lembar 2"kang dumunung ing ngisor jendhela Excel ing sadhuwure baris status. Tahan tombol mouse kiwa lan nyorot kabeh sel ing kolom kasebut. "Jeneng".

Sawise koordinat sing ditampilake ing lapangan "Deleng"pindhah menyang lapangan "Pemetaan Tipe" lan nyetel nomer saka keyboard "0". Sawise iki, kita bali menyang lapangan maneh. "Deleng". Kasunyatan punika kita bakal nyalin rumus, kaya sing kita ing cara sadurunge. Ana alamat sing diimbangi, nanging kita kudu ndandani koordinat ing array sing ditonton. Sampeyan kudu ora ngalih. Pilih koordinat kursor lan klik tombol fungsi F4. Minangka sampeyan bisa ndeleng, tandha dollar katon ing ngarep koordinat, sing tegese pranala saka relatif wis dadi mutlak. Banjur klik tombol "OK".

Asil ditampilake ing kolom pisanan kolom. "Bet". Nanging sadurunge nyalin, kita kudu ndandani area liyane, yaiku argumen pisanan saka fungsi kasebut INDEKS. Kanggo nindakake iki, pilih elemen kolom sing ngemot rumus, lan pindhah menyang garis formula. Pilih argumen pisanan operator INDEKS (B2: B7) lan klik tombol F4. Minangka sampeyan bisa ndeleng, tandha dollar katon cedhak koordinat sing dipilih. Klik tombol Ketik. Umumé, rumus njupuk formulir ing ngisor iki:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))

Saiki sampeyan bisa nyalin nganggo tandha isi. Nelpon kanthi cara sing padha kita guneman bab sadurungé, lan ngegung nganti pungkasan tabel.

Minangka sampeyan bisa ndeleng, senadyan kasunyatan manawa dhaptar baris saka rong tabel sing gegandhengan ora cocog, manawa kabeh nilai wis diencerake miturut jeneng buruh. Iki ditindakake kanthi nggunakake kombinasi operator INDEKS-MATCH.

Deleng uga:
Fungsi Excel INDEX
Fungsi match ing Excel

Cara 3: Nindakake Operasi Matematika karo Data sing Digabung

Pengikatan data langsung uga apik amarga ora mung kanggo nampilake nilai-nilai sing ditampilake ing tabel liyane ing salah sawijining tabel, nanging uga kanggo nindakake macem-macem operasi matematika kasebut (tambahan, divisi, subtraction, perkalian, lan liya-liyane).

Ayo ndeleng carane iki rampung ing laku. Ayo nglakoni sing Lembar 3 data gaji perusahaan umum bakal ditampilake tanpa rincian karyawan. Iki, tarif staf bakal ditarik saka Lembar 2, jumlah (nggunakake fungsi kasebut SUM) lan dikalikan dening koefisien kanthi nggunakake formula.

Pilih sel ngendi jumlah payroll bakal ditampilake Lembar 3. Klik tombol "Pasang fungsi".

Sampeyan kudu miwiti jendhela Fungsi master. Pindhah menyang grup "Matematika" lan milih jeneng ing kana "SUMM". Sabanjure, klik tombol "OK".

Pindhah menyang jendhela argumen fungsi SUMsing dirancang kanggo ngetung sak nomer sing dipilih. Nduweni sintaks ing ngisor iki:
= SUM (nomer1; nomer2; ...)
Kothak ing jendhela cocog karo argumen fungsi kasebut. Senajan jumlah kasebut bisa nganti 255 lembar, kanggo tujuan kita mung siji bakal cukup. Sijine kursor ing lapangan "Number1". Klik ing label "Lembar 2" ndhuwur garis status.

Sawise kita pindhah menyang bagean sing dikarepake saka buku, pilih kolom sing kudu diringkes. Kita nggawe kursor, nyekel tombol mouse kiwa. Minangka sampeyan bisa ndeleng, koordinat wilayah sing dipilih bakal langsung ditampilake ing lapangan jendhela argumentasi. Banjur klik tombol. "OK".

Sawise kuwi, kita kanthi otomatis pindhah Lembar 1. Minangka sampeyan bisa ndeleng, jumlah total upah buruh wis katon ing unsur sing cocog.

Nanging ora kabeh. Nalika kita elinga, gaji wis diitung kanthi nikelake angka regane kanthi koefisien. Mulane, maneh kita milih sel sing angka summed ana. Sawise iku pindhah menyang garis formula. We nambah tandha ping pindho menyang formula (*), banjur klik ing elemen kang koefisien dumunung. Kanggo nindakake pitungan klik Ketik ing keyboard. Minangka sampeyan bisa ndeleng, program diwilang upah total kanggo perusahaan.

Bali menyang Lembar 2 lan ngganti ukuran tingkat karyawan apa wae.

Sawise iki, pindhah menyang kaca kanthi jumlah total. Minangka sampeyan bisa ndeleng, amarga owah-owahan ing tabel sing gegandhengan, asil saka upah total otomatis diakali.

Cara 4: insert khusus

Sampeyan uga bisa nandhani arrays tabel ing Excel karo insert khusus.

Pilih angka sing perlu "diperketat" menyang meja liyane. Ing kasus kita, iki jangkoan kolom. "Bet" ing Lembar 2. Klik ing fragmen sing dipilih kanthi tombol mouse tengen. Ing dhaptar sing mbukak, pilih item kasebut "Salinan". Kombinasi tombol alternatif yaiku Ctrl + C. Sawise pindhah menyang Lembar 1.

Pindhah menyang area sing dikarepake saka buku kasebut, pilih sel sing arep ditarik. Ing kasus kita, iki minangka kolom. "Bet". Klik ing fragmen sing dipilih kanthi tombol mouse tengen. Ing menu konteks ing toolbar "Pilihan Opsi" klik ing lambang "Pasang Link".
Ana uga alternatif. Muga-muga, mung siji kanggo Excel versi lawas. Ing menu konteks, mindhah kursor menyang item kasebut "Tempel Khusus". Ing menu tambahan sing mbukak, pilih item kanthi jeneng sing padha.

Sawise kuwi, jendhela insert khusus mbukak. Kita pencet tombol "Pasang Link" ing sudhut kiwa ngisor sel.

Opsi apa wae sing sampeyan pilih, nilai saka siji dhaptar meja bakal dilebokake menyang liyane. Nalika sampeyan ngganti data ing sumber kasebut, bakal uga kanthi otomatis ngganti jangkoan sing dilebokake.

Pelajaran: Tempel Khusus ing Excel

Cara 5: Hubungan antar tabel ing macem-macem buku

Kajaba iku, sampeyan bisa ngatur sambungan antarane tabel ing macem-macem buku. Iki nggunakake alat insert khusus. Tindakan bakal bener-bener padha karo sing kita dianggep ing cara sadurungé, kajaba pandhu arah nalika introduksi rumus kasebut ora kudu kedadeyan antarane wilayah siji buku, nanging antarane file. Alami, kabeh buku sing gegandhengan kudu mbukak.

Pilih sawetara data sing pengin dikirim menyang buku liyane. Klik kanthi nggunakake tombol mouse tengen lan milih posisi ing menu sing mbukak "Salinan".

Banjur kita pindhah menyang buku sing data iki kudu disisipake. Pilih sawetara sing dipengini. Klik tombol mouse sisih tengen. Ing menu konteks ing grup "Pilihan Opsi" milih item "Pasang Link".

Sawise iki, nilai kasebut bakal dilebokake. Nalika sampeyan ngganti data ing buku sumber, array saka buku kasebut bakal narik munggah kanthi otomatis. Lan ora perlu kanggo loro buku mbukak kanggo iki. Iku cukup kanggo mbukak mung siji workbook, lan bakal kanthi otomatis narik ing data saka dokumen sing kagayut ditutup, yen owah-owahan sadurunge digawe ing.

Nanging kudu dicathet menawa ing kasus iki, insertion bakal digawe ing wangun array sing ora langgeng. Yen nyoba ngowahi sel wae karo data sing diselipake, pesen bakal muncul nyritakake yen sampeyan ora bisa nindakake iki.

Owah-owahan sajroning rangkéan kaya kasebut ing buku liya mung bisa digawe kanthi ngilangi tautan.

Pemisahan ing antarane tabel

Kadhangkala perlu ngeculake pranala antarane sawetara tabel. Alesan kasebut mungkin, kaya sing kasebut ing ndhuwur, yen sampeyan pengin ngganti arus sing diselipake saka buku liya, utawa mung amarga pangguna ora pengin data ing salah siji tabel supaya otomatis dianyari saka liyane.

Cara 1: medhot antarane buku

Sampeyan bisa ngilangi sambungan antarane buku ing kabeh sel kanthi nglakoni sakbenere operasi. Ing wektu sing padha, data ing sel bakal tetep, nanging bakal dadi nilai ora dianyari statis sing ora gumantung ing dokumen liyane.

Ing kitab, ing endi angka-angka saka file liyane ditarik, pindhah menyang tab "Data". Klik ing lambang "Sunting pranala"sing dumunung ing tape ing pamblokiran alat "Sambungan". Perlu dicatet yen manawa buku saiki ora ngemot pranala menyang file liyane, tombol iki ora aktif.

Jendhela kanggo ngganti pranala diluncurake. Pilih saka dhaptar buku sing ana hubungane (yen ana sawetara) file sing arep dibusak. Klik tombol "Ngilangi tautan".

Jendhela informasi mbukak, ing ngendi ana bebaya babagan konsekuensi tumindak lanjut. Yen sampeyan yakin apa sing arep kowe lakoni, banjur klik tombol. "Break hubungan".

Sawise kuwi, kabeh referensi kanggo file kasebut ing dokumen saiki bakal diganti karo nilai statis.

Cara 2: Pasang Nilai

Nanging cara kasebut mung cocok yen sampeyan kudu ngilangi kabeh pranala antarane rong buku kasebut. Apa sing kudu dilakoni yen sampeyan pengin medhot sambungan tabel sing ana ing file sing padha? Sampeyan bisa nindakake iki kanthi nyalin data, banjur dipungkasi menyang panggonan sing padha karo nilai-nilai kasebut.Miturut cara iki, cara sing padha bisa digunakake kanggo ngilangi hubungan antarane kisaran data kapisah saka buku sing beda tanpa mbebasake sambungan umum antarane file. Ayo ndeleng carane cara iki digunakake ing laku.

Pilih jarak sing arep dibusak kanggo tabel liyane. Klik kanthi nggunakake tombol mouse tengen. Ing menu sing mbukak, pilih item kasebut "Salinan". Tinimbang tumindak kasebut, sampeyan bisa ngetik kombinasi tombol panas alternatif. Ctrl + C.

Banjur, tanpa nyopot pilihan saka potongan sing padha, klik maneh nganggo tombol mouse tengen. Wektu iki ing dhaptar tumindak kita klik ing lambang "Nilai"sing diselehake ing grup alat "Pilihan Opsi".

Sawise kuwi, kabeh pranala ing sawetara sing dipilih bakal diganti karo nilai statis.

Minangka sampeyan bisa ndeleng, Excel duwe cara lan alat kanggo nggabungake sawetara tabel bebarengan. Ing kasus iki, data tabular bisa uga ana ing lembaran liya lan ing buku-buku liyane. Yen perlu, sambungan iki bisa gampang rusak.