ເນື້ອໃນ
ທ່ານມີຕາຕະລາງທີ່ມີຂໍ້ມູນໃນ Excel ທີ່ສາມາດປັບຂະຫນາດໄດ້, ເຊັ່ນວ່າຈໍານວນແຖວ (ຄໍລໍາ) ສາມາດເພີ່ມຂຶ້ນຫຼືຫຼຸດລົງໃນການເຮັດວຽກ? ຖ້າຕາຕະລາງຂະຫນາດ "ລອຍ", ຫຼັງຈາກນັ້ນທ່ານຈະຕ້ອງຕິດຕາມເວລານີ້ຢ່າງຕໍ່ເນື່ອງແລະແກ້ໄຂມັນ:
- ການເຊື່ອມໂຍງໃນສູດການລາຍງານທີ່ອ້າງອີງໃສ່ຕາຕະລາງຂອງພວກເຮົາ
- ຂອບເຂດເບື້ອງຕົ້ນຂອງຕາຕະລາງ pivot ທີ່ສ້າງຂຶ້ນຕາມຕາຕະລາງຂອງພວກເຮົາ
- ໄລຍະເບື້ອງຕົ້ນຂອງຕາຕະລາງທີ່ສ້າງຂຶ້ນຕາມຕາຕະລາງຂອງພວກເຮົາ
- ຊ່ວງສໍາລັບ dropdowns ທີ່ໃຊ້ຕາຕະລາງຂອງພວກເຮົາເປັນແຫຼ່ງຂໍ້ມູນ
ທັງໝົດນີ້ຈະບໍ່ເຮັດໃຫ້ເຈົ້າເບື່ອ 😉
ມັນຈະສະດວກກວ່າແລະຖືກຕ້ອງໃນການສ້າງໄລຍະ "ຢາງ" ແບບເຄື່ອນໄຫວ, ເຊິ່ງຈະປັບຂະຫນາດໂດຍອັດຕະໂນມັດກັບຈໍານວນແຖວແລະຖັນຂອງຂໍ້ມູນຕົວຈິງ. ເພື່ອເຮັດສິ່ງນີ້, ມີຫຼາຍວິທີ.
ວິທີການ 1. ຕາຕະລາງອັດສະລິຍະ
ເນັ້ນຂອບເຂດຂອງເຊວຂອງທ່ານແລະເລືອກຈາກແຖບ ຫນ້າທໍາອິດ – Format as Table (ຫນ້າທໍາອິດ – Format as Table):
ຖ້າທ່ານບໍ່ຕ້ອງການການອອກແບບເສັ້ນດ່າງທີ່ເພີ່ມໃສ່ຕາຕະລາງເປັນຜົນຂ້າງຄຽງ, ຫຼັງຈາກນັ້ນທ່ານສາມາດປິດມັນຢູ່ໃນແຖບທີ່ປາກົດ. ຜູ້ກໍ່ສ້າງ (ອອກແບບ). ແຕ່ລະຕາຕະລາງທີ່ສ້າງຂຶ້ນດ້ວຍວິທີນີ້ໄດ້ຮັບຊື່ທີ່ສາມາດຖືກແທນທີ່ດ້ວຍວິທີທີ່ສະດວກກວ່າຢູ່ໃນສະຖານທີ່ດຽວກັນຢູ່ໃນແຖບ ຜູ້ກໍ່ສ້າງ (ອອກແບບ) ໃນພາກສະຫນາມ ຊື່ຕາຕະລາງ (ຊື່ຕາຕະລາງ).
ໃນປັດຈຸບັນພວກເຮົາສາມາດນໍາໃຊ້ການເຊື່ອມຕໍ່ແບບເຄື່ອນໄຫວກັບ "ຕາຕະລາງ smart" ຂອງພວກເຮົາ:
- ຕາລາງ 1 - ການເຊື່ອມຕໍ່ກັບຕາຕະລາງທັງຫມົດຍົກເວັ້ນສໍາລັບແຖວຫົວຫນ້າ (A2:D5)
- ຕາຕະລາງ 1[#ທັງໝົດ] - ການເຊື່ອມຕໍ່ກັບຕາຕະລາງທັງຫມົດ (A1:D5)
- ຕາຕະລາງ 1 [ເປໂຕ] - ອ້າງອີງເຖິງ range-column ໂດຍບໍ່ມີການແບ່ງປັນຫົວຫ້ອງທໍາອິດ (C2:C5)
- ຕາຕະລາງ 1[#Headers] - ການເຊື່ອມຕໍ່ກັບ "ຫົວຂໍ້" ທີ່ມີຊື່ຂອງຖັນ (A1:D1)
ການອ້າງອີງດັ່ງກ່າວເຮັດວຽກໄດ້ດີໃນສູດ, ຕົວຢ່າງ:
= SUM (ຕາຕະລາງ 1 [ມອດໂກ]) - ການຄິດໄລ່ຂອງຜົນລວມສໍາລັບຖັນ "Moscow"
or
=VPR(F5;ຕາລາງ 1;3;0) – ຄົ້ນຫາຢູ່ໃນຕາຕະລາງສໍາລັບເດືອນຈາກເຊລ F5 ແລະອອກຜົນລວມທີ່ St. Petersburg ສໍາລັບມັນ (VLOOKUP ແມ່ນຫຍັງ?)
ການເຊື່ອມຕໍ່ດັ່ງກ່າວສາມາດຖືກນໍາໃຊ້ຢ່າງສໍາເລັດຜົນໃນເວລາສ້າງຕາຕະລາງ pivot ໂດຍການເລືອກໃນແຖບ ແຊກ – ຕາຕະລາງ Pivot (Insert – Pivot Table) ແລະໃສ່ຊື່ຂອງຕາຕະລາງ smart ເປັນແຫຼ່ງຂໍ້ມູນ:
ຖ້າທ່ານເລືອກຊິ້ນສ່ວນຂອງຕາຕະລາງດັ່ງກ່າວ (ຕົວຢ່າງ, ສອງຄໍລໍາທໍາອິດ) ແລະສ້າງແຜນວາດຂອງປະເພດໃດກໍ່ຕາມ, ເມື່ອເພີ່ມເສັ້ນໃຫມ່, ພວກມັນຈະຖືກເພີ່ມເຂົ້າໃນແຜນວາດໂດຍອັດຕະໂນມັດ.
ເມື່ອສ້າງລາຍການແບບເລື່ອນລົງ, ການເຊື່ອມຕໍ່ໂດຍກົງກັບອົງປະກອບຕາຕະລາງອັດສະລິຍະບໍ່ສາມາດນໍາໃຊ້ໄດ້, ແຕ່ທ່ານສາມາດເຂົ້າຫາຂໍ້ຈໍາກັດນີ້ໄດ້ຢ່າງງ່າຍດາຍໂດຍໃຊ້ trick tactical - ໃຊ້ຫນ້າທີ່ ປະຕິບັດຕາມ (ໂດຍອີງໃສ່), ເຊິ່ງປ່ຽນຂໍ້ຄວາມໃຫ້ເປັນລິ້ງ:
ເຫຼົ່ານັ້ນ. ການເຊື່ອມຕໍ່ກັບຕາຕະລາງ smart ໃນຮູບແບບຂອງຂໍ້ຄວາມ (ໃນເຄື່ອງຫມາຍວົງຢືມ!) ກາຍເປັນການເຊື່ອມຕໍ່ທີ່ສົມບູນ, ແລະບັນຊີລາຍຊື່ເລື່ອນລົງຕາມປົກກະຕິຮັບຮູ້ມັນ.
ວິທີທີ່ 2: ໄລຍະທີ່ມີຊື່ແບບໄດນາມິກ
ຖ້າການປ່ຽນຂໍ້ມູນຂອງທ່ານເຂົ້າໄປໃນຕາຕະລາງອັດສະລິຍະແມ່ນບໍ່ຕ້ອງການສໍາລັບເຫດຜົນບາງຢ່າງ, ຫຼັງຈາກນັ້ນທ່ານສາມາດນໍາໃຊ້ວິທີການທີ່ສັບສົນເລັກນ້ອຍ, ແຕ່ລະອຽດອ່ອນກວ່າແລະມີຄວາມຫລາກຫລາຍ - ສ້າງຂອບເຂດທີ່ມີຊື່ແບບເຄື່ອນໄຫວໃນ Excel ທີ່ຫມາຍເຖິງຕາຕະລາງຂອງພວກເຮົາ. ຫຼັງຈາກນັ້ນ, ໃນກໍລະນີຂອງຕາຕະລາງ smart, ທ່ານສາມາດນໍາໃຊ້ຊື່ຂອງຂອບເຂດທີ່ສ້າງຂື້ນໃນສູດໃດກໍ່ຕາມ, ບົດລາຍງານ, ຕາຕະລາງ, ແລະອື່ນໆ. ໃຫ້ເລີ່ມຕົ້ນດ້ວຍຕົວຢ່າງງ່າຍໆ:
Task: ສ້າງໄລຍະທີ່ມີຊື່ແບບເຄື່ອນໄຫວທີ່ຈະອ້າງອີງໃສ່ລາຍຊື່ເມືອງ ແລະຂະຫຍາຍ ແລະຫຍໍ້ລົງໂດຍອັດຕະໂນມັດເມື່ອເພີ່ມເມືອງໃໝ່ ຫຼືລຶບພວກມັນອອກ.
ພວກເຮົາຈະຈໍາເປັນຕ້ອງມີສອງຫນ້າທີ່ Excel ໃນຕົວທີ່ມີຢູ່ໃນສະບັບໃດ − POICPOZ (ການແຂ່ງຂັນ) ເພື່ອກໍານົດຕາລາງສຸດທ້າຍຂອງໄລຍະ, ແລະ INDEX (INDEX) ເພື່ອສ້າງການເຊື່ອມຕໍ່ແບບເຄື່ອນໄຫວ.
ຊອກຫາຕາລາງສຸດທ້າຍໂດຍໃຊ້ MATCH
MATCH(lookup_value, range, match_type) – ຟັງຊັນທີ່ຊອກຫາຄ່າທີ່ໃຫ້ໄວ້ໃນໄລຍະໃດໜຶ່ງ (ແຖວ ຫຼືຖັນ) ແລະສົ່ງຄືນຕົວເລກຕາມລຳດັບຂອງຕາລາງທີ່ພົບເຫັນ. ຕົວຢ່າງ, ສູດ MATCH(“ມີນາ”;A1:A5;0) ຈະສົ່ງຜົນເປັນເລກ 4, ເພາະວ່າຄຳສັບ “ມີນາ” ຕັ້ງຢູ່ໃນຕາລາງທີ 1 ໃນຖັນ A5:A0. argument ຟັງຊັນສຸດທ້າຍ Match_Type = XNUMX ຫມາຍຄວາມວ່າພວກເຮົາກໍາລັງຊອກຫາການຈັບຄູ່ທີ່ແນ່ນອນ. ຖ້າອາກິວເມັນນີ້ບໍ່ໄດ້ຖືກລະບຸ, ຫຼັງຈາກນັ້ນ, ຟັງຊັນຈະປ່ຽນເປັນຮູບແບບການຄົ້ນຫາສໍາລັບຄ່າທີ່ນ້ອຍທີ່ສຸດທີ່ໃກ້ທີ່ສຸດ - ນີ້ແມ່ນສິ່ງທີ່ສາມາດຖືກນໍາໃຊ້ຢ່າງສໍາເລັດຜົນເພື່ອຊອກຫາຕາລາງທີ່ຄອບຄອງສຸດທ້າຍໃນອາເຣຂອງພວກເຮົາ.
ໂດຍເນື້ອແທ້ແລ້ວຂອງ trick ແມ່ນງ່າຍດາຍ. MATCH ຄົ້ນຫາເຊລຢູ່ໃນຂອບເຂດຈາກເທິງຫາລຸ່ມສຸດແລະ, ໃນທາງທິດສະດີ, ຄວນຢຸດເຊົາເມື່ອມັນຊອກຫາຄ່າທີ່ນ້ອຍທີ່ສຸດທີ່ໃກ້ທີ່ສຸດກັບຄ່າທີ່ໃຫ້. ຖ້າທ່ານລະບຸຄ່າທີ່ເຫັນໄດ້ຊັດເຈນຫຼາຍກວ່າທີ່ມີຢູ່ໃນຕາຕະລາງເປັນຄ່າທີ່ຕ້ອງການ, ຫຼັງຈາກນັ້ນ MATCH ຈະໄປຮອດທ້າຍສຸດຂອງຕາຕະລາງ, ຊອກຫາບໍ່ມີຫຍັງແລະໃຫ້ຕົວເລກລໍາດັບຂອງຕາລາງທີ່ເຕັມໄປສຸດທ້າຍ. ແລະພວກເຮົາຕ້ອງການມັນ!
ຖ້າມີພຽງແຕ່ຕົວເລກໃນ array ຂອງພວກເຮົາ, ຫຼັງຈາກນັ້ນ, ພວກເຮົາສາມາດກໍານົດຕົວເລກເປັນຄ່າທີ່ຕ້ອງການ, ເຊິ່ງແນ່ນອນຈະໃຫຍ່ກວ່າຈໍານວນໃດໆໃນຕາຕະລາງ:
ສໍາລັບການຮັບປະກັນ, ທ່ານສາມາດນໍາໃຊ້ຕົວເລກ 9E + 307 (9 ຄູນ 10 ກັບພະລັງງານຂອງ 307, ie 9 ກັບ 307 ສູນ) - ຈໍານວນສູງສຸດທີ່ Excel ສາມາດເຮັດວຽກກັບຫຼັກການ.
ຖ້າຫາກວ່າມີຄ່າຂໍ້ຄວາມໃນຖັນຂອງພວກເຮົາ, ຫຼັງຈາກນັ້ນທຽບເທົ່າຂອງຕົວເລກທີ່ໃຫຍ່ທີ່ສຸດທີ່ເປັນໄປໄດ້, ທ່ານສາມາດໃສ່ການກໍ່ສ້າງ REPEAT(“i”, 255) – ສະຕຣິງຂໍ້ຄວາມທີ່ປະກອບດ້ວຍ 255 ຕົວອັກສອນ “i” – ຈົດຫມາຍສະບັບສຸດທ້າຍຂອງ ຕົວອັກສອນ. ເນື່ອງຈາກຕົວຈິງແລ້ວ Excel ປຽບທຽບລະຫັດຕົວອັກສອນໃນເວລາຄົ້ນຫາ, ຂໍ້ຄວາມໃດໆໃນຕາຕະລາງຂອງພວກເຮົາທາງດ້ານວິຊາການຈະ "ນ້ອຍກວ່າ" ຫຼາຍກວ່າເສັ້ນຍາວ "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" line:
ສ້າງລິ້ງໂດຍໃຊ້ INDEX
ໃນປັດຈຸບັນທີ່ພວກເຮົາຮູ້ຕໍາແຫນ່ງຂອງອົງປະກອບທີ່ບໍ່ຫວ່າງສຸດທ້າຍໃນຕາຕະລາງ, ມັນຍັງຄົງເປັນຕົວເຊື່ອມຕໍ່ໄປຫາຂອບເຂດທັງຫມົດຂອງພວກເຮົາ. ເພື່ອເຮັດສິ່ງນີ້, ພວກເຮົາໃຊ້ຫນ້າທີ່:
INDEX(ໄລຍະ; row_num; column_num)
ມັນໃຫ້ເນື້ອໃນຂອງເຊນຈາກໄລຍະໂດຍແຖວແລະຖັນ, ຕົວຢ່າງເຊັ່ນ, ຫນ້າທີ່ =INDEX(A1:D5;3;4) ໃນຕາຕະລາງຂອງພວກເຮົາກັບເມືອງແລະເດືອນຈາກວິທີການທີ່ຜ່ານມາຈະໃຫ້ 1240 - ເນື້ອໃນ. ຈາກແຖວທີ 3 ແລະຖັນທີ 4, ເຊັ່ນ: ເຊລ D3. ຖ້າມີພຽງຖັນດຽວ, ຕົວເລກຂອງມັນສາມາດຖືກຍົກເລີກໄດ້, ເຊັ່ນວ່າ ສູດ INDEX(A2:A6;3) ຈະໃຫ້ “Samara” ໃນພາບໜ້າຈໍສຸດທ້າຍ.
ແລະມີ nuance ທີ່ບໍ່ຊັດເຈນທັງຫມົດ: ຖ້າ INDEX ບໍ່ພຽງແຕ່ເຂົ້າໄປໃນເຊລຫຼັງຈາກເຄື່ອງຫມາຍ =, ຕາມປົກກະຕິ, ແຕ່ຖືກນໍາໃຊ້ເປັນສ່ວນສຸດທ້າຍຂອງການອ້າງອີງເຖິງໄລຍະຫຼັງຈາກຈໍ້າສອງເມັດ, ຫຼັງຈາກນັ້ນມັນຈະບໍ່ອອກ. ເນື້ອໃນຂອງຈຸລັງ, ແຕ່ທີ່ຢູ່ຂອງມັນ! ດັ່ງນັ້ນ, ສູດຄຳນວນເຊັ່ນ $A$2:INDEX($A$2:$A$100;3) ຈະໃຫ້ຄຳອ້າງອີງເຖິງຊ່ວງ A2:A4 ຢູ່ທີ່ຜົນຜະລິດ.
ແລະນີ້ແມ່ນບ່ອນທີ່ຟັງຊັນ MATCH ເຂົ້າມາ, ເຊິ່ງພວກເຮົາໃສ່ພາຍໃນ INDEX ເພື່ອກໍານົດຈຸດສິ້ນສຸດຂອງລາຍຊື່ແບບເຄື່ອນໄຫວ:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“ຂ້ອຍ”;255);A2:A100))
ສ້າງໄລຍະທີ່ມີຊື່
ມັນຍັງຄົງທີ່ຈະຫຸ້ມຫໍ່ທັງຫມົດເຂົ້າໄປໃນດຽວທັງຫມົດ. ເປີດແຖບ ສູດ (ສູດ) ແລະກົດປຸ່ມ ຜູ້ຈັດການຊື່ (ຜູ້ຈັດການຊື່). ໃນປ່ອງຢ້ຽມທີ່ເປີດ, ໃຫ້ຄລິກໃສ່ປຸ່ມ ສ້າງ (ໃຫມ່), ໃສ່ຊື່ຊ່ວງຂອງພວກເຮົາແລະສູດໃນພາກສະຫນາມ ລະດັບ (ກະສານອ້າງອີງ):
ມັນຍັງຄົງໃຫ້ຄລິກໃສ່ OK ແລະລະດັບຄວາມພ້ອມສາມາດຖືກນໍາໃຊ້ໃນສູດໃດກໍ່ຕາມ, ບັນຊີລາຍຊື່ເລື່ອນລົງຫຼືຕາຕະລາງ.
- ການນໍາໃຊ້ຟັງຊັນ VLOOKUP ເພື່ອເຊື່ອມຕໍ່ຕາຕະລາງແລະການຊອກຫາຄ່າ
- ວິທີການສ້າງບັນຊີລາຍຊື່ແບບເລື່ອນລົງອັດຕະໂນມັດ
- ວິທີການສ້າງຕາຕະລາງ pivot ເພື່ອວິເຄາະຂໍ້ມູນຈໍານວນຫຼວງຫຼາຍ