Формуле Екцел низа су изузетно моћне. Једном када научите трик Цтрл + Схифт + Ентер, можете да замените хиљаде формула. Данас једна формула низа врши 86.000 прорачуна.
Трискаидекафобија је страх од петка 13. Ова тема неће излечити ништа, али показаће вам апсолутно невероватну формулу која замењује 110.268 формула. У стварном животу никада не морам да рачунам колико се петка 13. догодило у мом животу, али снага и лепота ове формуле илуструју снагу Екцел-а.
Реците да имате пријатеља који је сујеверјан око петка 13.. Желите да илуструјете колико је петка тринаестог проживео ваш пријатељ.
Поставите једноставни радни лист у наставку, са датумом рођења у Б1 и =TODAY()у Б2. Тада дивља формула у Б6 процењује сваки дан да је ваш пријатељ жив како би схватила колико је тих дана било у петак и пало 13. у месецу. За мене је тај број 86. Нема чега да се плашим.
Иначе, 17.2.1965. Је заиста мој рођендан. Али не желим да ми пошаљете рођенданску честитку. Уместо тога, за мој рођендан желим да ми допустите да објасним како функционише та невероватна формула, један по један мали корак.
Да ли сте икада користили функцију ИНДИРЕЦТ? Када затражите =INDIRECT("C3"), Екцел ће прећи на Ц3 и вратити све што се налази у тој ћелији. Али ИНДИРЕЦТ је моћнији када у тренутку израчунате референцу на ћелију. Можете да поставите наградни точак где неко бира слово између А и Ц, а затим бира број између 1 и 3. Када спојите два одговора, добићете адресу ћелије, а шта год да је на тој адреси ћелије, награда је . Изгледа да сам уместо боравка у одмаралишту освојио књигу фотографија.
Да ли знате како Екцел чува датуме? Када вам Екцел покаже 17.2.1965., У ћелији чува 23790, јер је 17.2.1965. Био 23790. дан 20. века. У срцу формуле је спајање које спаја датум почетка и двотачку и датум завршетка. Екцел не користи форматирани датум. Уместо тога, користи серијски број иза сцене. Тако B3&":"&B4постаје 23790: 42167. Веровали или не, то је важећа референца на ћелију. Ако желите да саберете све у редовима од 3 до 5, могли бисте да користите =SUM(3:5). Дакле, када пренесете 23790: 42167 у индиректну функцију, она показује на све редове.
Следеће што формула убице ради је да затражи ROW(23790:42167). Обично прођете једну ћелију: =ROW(D17)је 17. Али у овом случају пролазите хиљаде ћелија. Када затражите ROW(23790:42167)и завршите формулу помоћу Цтрл + Схифт + Ентер, Екцел заправо враћа сваки број од 23790, 23791, 23792 и тако даље до 42167.
Овај корак је невероватан корак. У овом кораку прелазимо са два броја и „искачемо“ низ од 18378 бројева. Сада морамо нешто да урадимо са тим низом одговора. Ћелија Б9 претходне слике само броји колико одговора добијамо, што је досадно, али доказује да ROW(23790:42167)враћа 18378 одговора.
Драматично поједноставимо оригинално питање како бисте могли да видите шта се дешава. У овом случају наћи ћемо број петка у јулу 2015. Формула приказана испод у Б7 даје тачан одговор у Б6.
У основи формуле је ROW(INDIRECT(B3&":"&B4)). Ово ће вратити 31 датум у јулу 2015. Али формула затим прослеђује тих 31 датума WEEKDAY(,2)функцији. Ова функција ће вратити 1 за понедељак, 5 за петак итд. Дакле, велико питање је колико од тих 31 датума враћа петицу када се проследи WEEKDAY(,2)функцији.
Можете да гледате израчунавање формуле у успореном режиму помоћу наредбе Процени формулу на картици Формула на траци.
То је након што ИНДИРЕЦТ претвори датуме у референцу реда.
У следећем кораку Екцел ће ускоро проследити 31 број у функцију ВИКЕНД. Сада би у формули убице прошло 18.378 бројева уместо 31.
Ево резултата 31 ВЕЧНОГ ДАНА. Запамтите, желимо да избројимо колико је 5.
Провера да ли је претходни низ 5 враћа читав низ тачних / нетачних вредности. Постоји 5 истинских вредности, по једна за сваки петак.
Не могу да вам покажем шта се даље дешава, али могу да објасним. Екцел не може Збројити гомилу тачних и нетачних вредности. То је против правила. Али ако помножите те вредности Труе и Фалсе са 1 или ако користите двоструку негативу или функцију Н (), претворите Труе вредности у 1, а Фалсе вредности у 0. Пошаљите их у СУМ или СУМПРОДУЦТ и добићете добити број тачних вредности.
Ево сличног примера да се изброји колико месеци има дан 13. О овоме је тривијално размишљати: Сваки месец има 13., па би одговор за целу годину требао бити 12. Екцел врши математику, генерише 365 датума, шаље их у функцију ДАИ () и утврђује колико их завршава до 13. у месецу. Одговор је, очекивано, 12.
Следећа слика је радни лист који чини сву логику формула убојице приказана на почетку ове теме. Створио сам ред за сваки дан да сам жив. У колони Б добијам ДАИ () тог датума. У колони Ц добијам ВИКЕНД () датума. Да ли је у колони Д једнако 13? Да ли је у колони Е Ц = 5? Затим помножим Д * Е да претворим тачно / нетачно у 1/0.
Сакрио сам пуно редова, али показујем вам три случајна дана у средини која су и петак и 13.
Укупан износ у Ф18381 је исти 86 који је вратила моја првобитна формула. Одличан знак. Али овај радни лист има 110.268 формула. Моја оригинална формула убица чини сву логику ових 110.268 формула у једној формули.
Чекати. Желим да појасним. У оригиналној формули нема ничег магичног што постаје паметно и скраћује логику. Та оригинална формула заиста ради 110.268 корака, вероватно чак и више, јер оригинална формула мора двапут израчунати РОВ () низ.
Нађите начин да ово искористите ROW(INDIRECT(Date:Date))у стварном животу и пошаљите ми то е-поштом (пуб на дот цом). Послаћу награду првих 100 људи да одговоре. Вероватно није одмаралиште. Вероватније Биг Мац. Али то тако иде са наградама. Пуно Биг Маца и мало одмаралишта.
Први пут сам видео ову формулу објављену на Мессаге Боард-у 2003. године од стране Еким-а. Заслуга је дата Харлан Гровеу. Формула се појавила и у књизи Боба Умласа Тхис ис нот Екцел, Ит'с Магиц. Мике Деланеи, Мени Порат и Тим Схеетс сви су предложили трик минус / минус. СУМПРОДУЦТ су предложили Аудреи Линн и Стевен Вхите. Хвала свима.
Погледајте видео
- Постоји тајна класа формула под називом Арраи Формулас.
- Формула низа може да изврши хиљаде посредних прорачуна.
- Често захтевају да притиснете Цтрл + Схифт + Ентер, али не увек.
- Најбоља књига о формулама низа је Мике Гирвин Цтрл + Схифт + Ентер.
- ИНДИРЕЦТ вам омогућава да користите спајање за изградњу нечега што изгледа као референца на ћелију.
- Датуми су лепо обликовани, али се чувају као број дана од 1. јануара 1900. године.
- Спајање два датума указаће на низ редова у програму Екцел.
- Тражење
ROW(INDIRECT(Date1:Date2))воље „искочи“ низом узастопних бројева - Помоћу функције ВЕЕКДАИ утврдите да ли је датум петак.
- Колико се петка дешава овог јула?
- Да бисте гледали успоређивање израчунавања формуле, користите алатку за процену формуле
- Колико се 13-их догоди ове године?
- Колико се петка 13. догодило између два датума?
- Проверите сваки датум да бисте видели да ли је ВИКЕНД петак
- Проверите сваки датум да ли је ДАН 13
- Помножите те резултате помоћу СУМПРОДУЦТ
- Употреба - за претварање Тачно / Нетачно у 1/0
Видео транскрипт
Научите Екцел из подцаста, епизода 2026 - Моја омиљена формула у целом програму Екцел!
Подкастујући целу ову књигу, кликните на „и“ у горњем десном углу да бисте дошли до листе песама!
У реду, била је то 30. тема у књизи, били смо некако на крају одељка за формулу или усред одељка за формуле и рекао сам да морам да уврстим своју омиљену формулу свих времена. Ово је само невероватна формула, без обзира да ли морате рачунати број у петак 13. или не, она отвара свет у читаво тајно подручје Екцел-а под називом Арраи Формулас! Ставите датум почетка, датум завршетка и ова формула израчунава број петка 13. који се догодио између та два датума. Заправо ради пет прорачуна сваког дана између та два датума, 91895 прорачуна + СУМ, 91896 прорачуна који се дешавају унутар ове једне мале формуле, у реду. Сада, до краја ове епизоде, толико ћете се заинтригирати формулама низа. Желим да нагласим,мој пријатељ Мике Гирвин има најбољу књигу о формулама низа под називом „Цтрл + Схифт” Ентер ”, ово је недавно штампање са плавом корицом, некада жутом и зеленом корицом. Сад, која год да се нађе, то је сјајна књига, истог садржаја и у жутој и у зеленој.
У реду, кренимо са унутрашње стране овог, са формулом коју можда нисте чули под називом ИНДИРЕЦТ. ИНДИРЕЦТ нам омогућава да спојимо или на неки начин направимо део текста који изгледа као референца на ћелију. У реду, рецимо да овде имамо наградни точак и управо сам вас замолио да бирате између А, Б и Ц. У реду, па одаберите ово и одаберите Ц, а затим одаберите ово и одаберите 3, у реду, и вашу награду је одмаралиште, јер је то оно што се чува у Ц3. А формула овде је спојена заједно било од Ц5 и било чега у Ц6 користећи & и затим прослеђује то ИНДИРЕЦТ-у. Дакле = ИНДИРЕКТ (Ц5 и Ц6), у овом случају, је Ц3, то мора бити уравнотежена референца. ИНДИРЕЦТ каже „Хеј, ићи ћемо на Ц3 и вратити одговор са тога, у реду?“ Још у Лотусу 1-2-3 ово се звало @@ функција,у Екцелу су га преименовали у ИНДИРЕЦТ. У реду, дакле, имали сте у ИНДИРЕЦТ-у, ево ево невероватне ствари која се дешава тамо.
Имамо два датума, како Екцел складишти датуме, 17.2.1965., То је заправо само форматирање. Ако бисмо отишли и погледали стварни број иза тога, он је 23790, што значи да је од 1/1/1900 23790 дана, а од 1/1/1980 42167 дана. На Мац-у ће то бити од 1/1/1904, тако да ће датуми бити око 3000. У реду, тако га Екцел чува, приказује нам га, додуше, захваљујући овом формату бројева као датум, али ако бисмо спојили Б3 и а: и Б4, то би нам заправо дало бројеве сачуване иза кулиса. Дакле = Б3 & ”:” & Б4, и ако бисмо то проследили ИНДИРЕЦТ-у, то ће заправо усмерити на све редове од 23790 до 42167.
Дакле, постоји ИНДИРЕКТ Б6, тражио сам РЕД тога, то ће ми дати читав низ одговора и схватити колико одговора сам користио, у реду. А да би ово функционисало, ако само притиснем Ентер, то не ради, морам да држим Цтрл и Схифт и притиснем Ентер и видим, то додаје () око формуле овде горе. То Екцел-у говори да пређе у режим супер формуле, режим формуле низа и направи све рачуне за све што је искочило из тог поља, 18378, у реду. Дакле, то је невероватан трик, индиректан од дате1: дате2, проследите то функцији РЕД и ево малог примера.
Дакле, само желимо да схватимо колико се петка догодило овог јула, ево датума почетка, датума завршетка и за сваки од тих редова затражит ћу ВИКЕНД. ВИКЕНД нам говори који је дан у недељи, а овде, у аргументу 2, петак ће имати вредност 5. Дакле, тражим одговор, а ми ћемо изабрати ову формулу, идемо на Формуле, и Процените формулу, а Процените формулу је одличан начин да гледате како се формула израчунава у успореном кретању. Дакле, ту је Б3, 1. јула, и видите да се то мења у број, а онда се придружујемо двотачки, тачно, ту је Б4, који ће се променити у број, и сада добијамо текст, 42186: 42216. У овом тренутку преносимо то у РЕД и тај мали мали израз претвориће се овде у 31 вредност.
Сад, у примеру где сам имао све од 1965. до 2015. године, испало би 86000 вредности, зар не, а ви то не желите да радите и процењујете формулу, јер би то било некако лудо, у реду? Али можете видети шта се овде дешава са 31, и сада тих 31 дан преносим на функцију ВЕЕКДАИ и добићемо 3-4-5. Дакле 3 значи да је била сриједа, а онда 4 значи да је био четвртак, а онда 5 значи да је био петак. Узмите свих оних 31 вредности и погледајте да ли су = 5, што је петак, а ми ћемо добити гомилу НЕТОЧНИХ И ИСТИНИТИХ, тако да ће среда, четвртак, петак, а затим 7 ћелија касније бити следећа ИСТИНА, супер!
У реду, тако да у овом случају имамо 5 ИСТИНА и 26 НЕТОЧНИХ. Да бих их сабрао, морам претворити НЕТОЧНО у 0, а ИСТИНИЦЕ у 1, а врло уобичајен начин да се то уради је коришћење - . У реду, на жалост, тамо није приказан одговор, где смо видели читаву гомилу 1 и 0, али то се заправо и догађа, а онда СУМПРОДУЦТ то сабира и доводи нас до 5. Овде доле, ако желимо схватите колико је било 13. у месецу ове године, од овог датума почетка до овог датума завршетка, врло сличан процес. Иако ћемо имати 365, проследите то функцији ДАИ и проверите да ли је 13, у реду. За пример 92000 реда, знате, добијамо дан, добијамо дан у дану, проверавамо да ли је ДАИ = 13, проверавамо да ли је ВЕЕКДАИ = ФАЛСЕ, множећи ово * ово,и само у случајевима када је петак 13. то завршава као ТАЧНО. СУМПРОДУЦТ тада каже „Зброј све оне“, и тако ћемо добити 86, дословно 91895 прорачуна + СУМ, 91896 који се дешавају унутар ове једне формуле, то је лудо моћно! Идите, купите Микеову књигу, то је невероватна књига, отвориће вам читав свет Екцел формула, и заправо, требало би да купите обе књиге. Купите моју књигу, купите Микеову књигу и имаћете сјајну колекцију која ће вас провести кроз остатак године.отвориће вам читав свет Екцел формула, и заправо, требало би да купите обе књиге. Купите моју књигу, купите Микеову књигу и имаћете сјајну колекцију која ће вас провести кроз остатак године.отвориће вам читав свет Екцел формула, и заправо, требало би да купите обе књиге. Купите моју књигу, купите Микеову књигу и имаћете сјајну колекцију која ће вас провести кроз остатак године.
Alright, so recap: there's a secret class of formulas called array formulas, and array formula can do thousands of intermediate calculations. They usually require you to press Ctrl+Shift+Enter, but not always, and the best book on array formulas is Mike Girvin’s “Ctrl+Shift+Enter” book. Alright, so INDIRECT lets you use concatenation to build something that looks like a cell reference, and then INDIRECT goes to that cell reference. Concatenating two dates with a colon will point to a range of rows in Excel, and then asking for the ROW of the INDIRECT of date1:date2 will pop out an array of many consecutive numbers, maybe 31, maybe 365, or maybe 85000. Check each date to see if the WEEKDAY=Friday, check each day to see if the DAY=13, multiply those two arrays of TRUEs and FALSEs using the SUMPRODUCT. In many cases we'll use -- to convert the TRUE/FALSE to 1’s and 0’s to allow the SUMPRODUCT to work. It's an awesome formula, I didn't create it, I found it on the message board, as I worked through it, I'm like “Wow, this is really cool!”
У реду, желим да вам захвалим што сте навратили, видимо се следећи пут за још једно емитовање од!
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2026.клск
