ຊ່ວງໄດນາມິກດ້ວຍການປັບຂະໜາດອັດຕະໂນມັດ

ທ່ານມີຕາຕະລາງທີ່ມີຂໍ້ມູນໃນ 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 ເພື່ອວິເຄາະຂໍ້ມູນຈໍານວນຫຼວງຫຼາຍ

 

ອອກຈາກ Reply ເປັນ