Microsoft Excel маюць некаторыя цікавыя трукі і выкарыстоўваючы SUM і ДВССЫЛ формул дынамічнага дыяпазону толькі два шляхі, каб лёгка маніпуляваць вамі дадзеныя.
SUM - INDIRECT Формула Агляд
Выкарыстанне INDIRECT функцыі ў формулах Excel дазваляе лёгка змяняць дыяпазон спасылак на вочкі , якія выкарыстоўваюцца ў формуле , без неабходнасці рэдагаваць саму формулу.
Ускосна можна выкарыстоўваць з цэлым шэрагам функцый , якія прымаюць спасылку на вочка ў якасці аргументу , такіх як афсетныя і SUM функцыі.
У апошнім выпадку, выкарыстоўваючы INDIRECT ў якасці аргументу для функцыі SUM можа стварыць дынамічны дыяпазон спасылак на вочкі , што функцыя SUM затым складае.
Ускосна робіць гэта, спасылаючыся на дадзеныя ў клетках ўскосна праз прамежкавае становішча.
Прыклад: SUM - ускосна формула выкарыстоўваецца для Усіх дынамічнага дыяпазону значэнняў
Гэты прыклад заснаваны на дадзеных, паказаных на малюнку вышэй.
Сума - ускосна формула створана з дапамогай дзеянняў падручнік ніжэй з'яўляецца:
= Сум (ДВССЫЛ ( "D" , і Е1 & ": D" , & Е2))
У гэтай формуле аргумент укладзенай Ускосныя Function утрымлівае спасылкі на ячэйкі Е1 і Е2. Чысла ў гэтых клетках, 1 і 4, у спалучэнні з астатняй часткай аргументу непрамы-х, ўтвараюць спасылкі на ячэйкі D1 і D4.
У выніку дыяпазон лікаў склаў ад функцыі SUM гэта дадзеныя , якія змяшчаюцца ў дыяпазоне вочак D1 да D4 - які з'яўляецца 50.
Змяняючы лік размешчаных у вочках Е1 і Е2; Аднак дыяпазон для падсумоўвацца можа быць лёгка зменены.
У гэтым прыкладзе будзе спачатку выкарыстоўваць прыведзеную вышэй формулу, каб сумаваць дадзеныя ў вочках D1: D4, а затым змяніць сумарны дыяпазон да D3: D6 без рэдагавання формулы ў вочку F1.
01 з 03
Ўвод формулы - Параметры
Варыянты для ўводу формулы ўключаюць:
- увёўшы прыведзеную вышэй формулу непасрэдна ў вочку F1 і націснуўшы клавішу Enter на клавіятуры
- ўвод INDIRECT функцыю ў якасці аргументу з дапамогай функцыі сум у дыялогавым акне
Большасць функцый у Excel ёсць дыялогавае акно, якое дазваляе ўводзіць кожны з аргументаў функцыі на асобную радок без неабходнасці турбавацца аб сінтаксісе .
У гэтым выпадку дыялогавае акно функцыі сум можна выкарыстоўваць для спрашчэння формулы да некаторай ступені. Паколькі Ускосна функцыя быць укладзеная SUM, непрамая функцыя і яе аргументы яшчэ павінна быць уведзеныя ўручную.
Крокі ніжэй выкарыстоўваць дыялогавае акно SUM, каб увесці формулу.
Увод дадзеных Tutorial
Вочка дадзеных D1 - D2 , 5 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 Е1 - Е2 1 - 4- Калі ласка, увядзіце наступныя дадзеныя ў вочкі D1 Е2
Запуск SUM - INDIRECT Формула - Адкрыццё SUM функцыі Дыялогавае акно
- Націсніце на вочка F1 - гэта тое, дзе будуць адлюстраваны вынікі гэтага прыкладу
- Націсніце на ўкладцы Формулы ў стужцы меню
- Выберыце Math & Trig ад стужкі , каб адкрыць функцыю выпадальнага спісу
- Націсніце на SUM ў спісе , каб адкрыць дыялогавае акно функцыі ў
02 з 03
Ўвод INDIRECT функцыі - Націсніце, каб павялічыць малюнак
INDIRECT формула павінна быць уведзена ў якасці аргументу для функцыі SUM.
У выпадку ўкладзеных функцый, Excel не дазваляе адкрываць дыялогавае акно другой функцыі, каб увесці яе аргументы.
Ускосна функцыя, такім чынам, павінна быць уведзена ўручную ў радку Number1 дыялогавага акна сума функцыі.
- У дыялогавым акне, націсніце на лініі Number1
- Калі ласка, увядзіце наступную INDIRECT функцыі: ускосны ( "D" & E1 & ": D" & E2)
- Націсніце OK для завяршэння функцыі і зачыніць дыялогавае акно
- Нумар 50 павінен з'явіцца ў вочку F1 , так як гэта агульнае для дадзеных , размешчаных у вочках D1 да D4
- Пры націску на вочка F1 поўную формулу = SUM (Indirect ( «D» & E1 & «: D» і E2)) з'яўляецца ў радку формул над лістом
Breaking Down непрамога функцыі
Для таго, каб стварыць дынамічны дыяпазон у калонцы D з дапамогай ускосна, мы павінны аб'яднаць літару D у аргуменце непрамога функцыя з лікамі, якія ўтрымліваюцца ў вочках Е1 і Е2.
Гэта дасягаецца за кошт наступных умоў:
- Ампэрсанд (&) выкарыстоўваецца для канкатэнацыі або аб'ядноўваюцца тэкставыя дадзеныя (у дадзеным выпадку літара D) з спасылкай на вочка (E1 і E2)
- Акрамя таго, тэкставыя дадзеныя, якія аб'ядноўваюцца з выкарыстаннем спасылкамі на ячэйкі павінны быць акружаны падвойнымі двукоссямі ( «»)
- нарэшце, канчатковыя кропкі дыяпазону адлучаюцца сябар ад сябра двукроп'ем (:)
Такім чынам, пачатковая кропка дыяпазону вызначаецца знакаў: «D» , & Е1.
Другі набор знакаў: «: D» і E2 аб'ядноўвае ў сабе двукроп'е з канчатковай кропкай. Гэта робіцца таму, што тоўстая кішка ўяўляе сабой тэкставы характар і, такім чынам, павінны быць уключаны ўнутры двукоссяў.
Трэці Ампэрсанд ў сярэдзіне выкарыстоўваецца для канкатэнацыі двух частак у адзін аргумент :
"D" & E1 & ": D" і E203 з 03
Дынамічная змена дыяпазону сума функцыі ў
Увесь сэнс гэтай формулы, каб зрабіць яго лёгка змяніць дыяпазон налічваў функцыю SUM без неабходнасці рэдагаваць аргумент функцыі.
Уключыўшы INDIRECT функцыі ў формуле, змяняючы лік у вочках Е1 і Е2 змяняецца дыяпазон вочак для чытання з дапамогай функцыі SUM.
Як відаць на малюнку вышэй, гэта таксама прыводзіць да адказу сухі сумесі, размешчанага ў змене ячэйкі F1, як яна складае новы дыяпазон дадзеных.
- Націсніце на вочка E1
- Калі ласка, увядзіце лік 3
- Націсніце клавішу Enter на клавіятуры
- Націсніце на вочка E2
- Калі ласка, увядзіце лік 6
- Націсніце клавішу Enter на клавіятуры
- Адказ у вочку F1 павінен змяніцца на 90 - што сума лікаў, якія змяшчаюцца ў вочках D3 да D6
- Далей праверыць формулу шляхам змены змесціва ячэйкі B1 і B2 для любых лікаў ад 1 да 6
Ускосна і #REF! значэнне памылкі
#REF! значэнне памылкі будзе адлюстроўвацца ў вочку F1, калі аргумент Ускоснай функцыі ў:
- не з'яўляецца дапушчальнай спасылка на вочка
- змяшчае знешнюю спасылку на іншую кнігу і што рабочая кніга не адкрытая
- адносіцца да дыяпазону вочак па-за межамі працоўнага ліста ў (радок або слупок 1048576) XFD