Використовуйте Ctrl + Shift + Enter (формули CSE), щоб перезавантажити свої формули в Excel! Так, це правда - в Excel існує секретний клас формул. Якщо ви знаєте магічні три клавіші, ви можете отримати одну формулу масиву Excel, яка замінить тисячі інших формул.
95% користувачів Excel не знають про формули ПСЕ. Коли більшість людей чують своє справжнє ім’я, вони думають: „Це не звучить ні найменш корисно“ і ніколи не турбуються про те, щоб дізнатися про них. Якщо ви вважаєте SumIf та CountIf крутими, незабаром ви виявите, що формули Ctrl + Shift + Enter (CSE) будуть запускати кола навколо SumIf та CountIf. Формули CSE дозволяють буквально замінити 1000 клітинок формул однією формулою. Так, мої товариші гуру Excel, щось таке потужне сидить прямо у нас під носом, і ми ніколи цим не користуємось.
До того, як існував SumIf, ви могли використовувати формулу CSE, щоб робити те саме, що SumIf. Microsoft надала нам SumIf і CountIf, але формули CSE не застаріли. О ні, і вони можуть зробити набагато більше! Що робити, якщо ви хочете зробити AverageIf? Як щодо GrowthIf? AveDevIf? Навіть MultiplyByPiAndTakeTheSquareRootIf. Практично все, що ви можете собі уявити, можна зробити за допомогою однієї з цих формул CSE.
Ось чому, на мою думку, формули ПСЕ ніколи не траплялися. По-перше, їх справжнє ім’я відлякує всіх. По-друге, для того, щоб змусити їх працювати, їм потрібне чуже, протиінтуїтивне поводження. Після введення формули CSE ви не можете просто натиснути Enter. Ви не можете просто вийти з комірки, натиснувши клавішу зі стрілкою. Навіть якщо ви правильно введете формулу і натиснете клавішу Enter, Excel надасть вам абсолютно не зручну для користувача "ЦІННІСТЬ!" помилка. У ньому не сказано: "Ого - це прекрасно. Ви пройшли 99,1% шляху", яким ви, мабуть, були.
Ось секрет: Після того, як ви введете формулу CSE, вам потрібно утримувати клавіші Ctrl і Shift, а потім натиснути Enter. Візьміть липку нотатку і запишіть це: Ctrl Shift Enter. Користувачі Power Excel матимуть можливість використовувати ці формули приблизно раз на місяць. Якщо ви не напишете Ctrl Shift Enter прямо зараз, ви забудете про це, коли щось з’явиться знову.
Вивчіть цей приклад: скажімо, ви хотіли усереднити лише значення у стовпці C, де стовпець A знаходився у східному регіоні.
Формула в комірці A13 є прикладом формули CSE.
=AVERAGE(IF(A2:A10="East",C2:C10))
Підключіть це, і ви отримаєте 7452,667, середнє значення лише для східних значень. Круто? Ви щойно створили власну версію неіснуючої функції Excel AverageIf. Запам’ятайте: натисніть Ctrl + Shift + Enter, щоб ввести формулу.
Перегляньте наступний приклад нижче.
У цьому прикладі ми робимо формулу CSE більш загальною. Замість того, щоб вказати, що ми шукаємо "Схід", вкажіть, що ви хочете, яке б значення було в A13. Формула зараз =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).
Як не дивно, Excel дозволить вам копіювати та вставляти формули CSE без особливих натискань клавіш. Я скопіював C13 в C14: C15, і тепер у мене є середні значення для всіх регіонів.
Наша основна система зберігає кількість та одиницю ціни, але не розширену ціну. Звичайно, досить просто додати стовпець C і заповнити його, =A2*B2а потім загальний стовпець C, але це не потрібно!
Тут наша формула CSE така =SUM(A2:A10*B2:B10). Він бере кожну комірку в A2: A10, множить на відповідну комірку в B2: B10 і підсумовує результат. Введіть формулу, утримуючи Ctrl і Shift, натискаючи клавішу Enter, і у вас буде відповідь в одній формулі замість 10.
Ви бачите, наскільки це потужне? Навіть якщо у мене було 10000 рядків даних, Excel візьме цю єдину формулу, зробить 10000 множень і дасть мені результат.
Добре, ось правила: Ви повинні натиснути Ctrl + Shift + Enter будь-коли, коли вводите або редагуєте ці формули. Якщо цього не зробити, вийде абсолютно неоднозначна #VALUE! помилка. Якщо у вас кілька діапазонів, всі вони повинні мати однакову загальну форму. Якщо у вас діапазон, змішаний з одинарними клітинками, одиночні комірки будуть «відтворені» в пам'яті відповідно до форми вашого діапазону.
Після того, як ви успішно введете одну з них і подивитесь на неї у рядку формул, навколо формули ви повинні мати фігурні дужки. Ви ніколи не вводите фігурні дужки самі. Натискання Ctrl + Shift + Enter поміщає їх туди.
Ці формули важко освоїти. отримує головний біль, просто думаючи про них. Якщо мені важко ввести, я переходжу до Інструменти> Майстри> Майстер умовної суми та проходжу по діалогових вікнах. Результатом роботи майстра умовної суми є формула CSE, тому зазвичай це може дати мені достатньо підказок про те, як ввести те, що мені насправді потрібно.
Вам доводилося проходити BASIC в 11 класі у пана Ірвіна? Або, ще гірше, ви отримали "D" у лінійній алгебрі з місіс Дюшес у коледжі? Якщо так, то ви в хорошій компанії і здригнетеся, коли почуєте слово "масив". - Я біжу кричачи в інший бік, коли хтось каже масив. Я їх розумію, але це Excel, мені тут не потрібні масиви !. Злий секрет полягає в тому, що Excel і Microsoft називають ці формули "формулами масиву". Зупинись - не тікай. Це справді добре. перейменовав їх у формули CSE, оскільки це звучить менш страшно, і тому, що назва допомагає запам’ятати, як їх вводити - Ctrl Shift Enter. Я згадую тут справжнє ім’я лише на випадок, якщо ви зіткнетеся з кимось із Майкрософт, у якого ніколи не було пані Герцогині для лінійної алгебри, або, якщо ви вирішите переглянути файли довідки. Якщо ви підете допомогти,шукати під злим псевдонімом "формула масиву", але між нами, друзями, назвемо їх CSE - Добре?
Прочитайте Використовуйте спеціальну формулу масиву Excel для імітації SUMIF з двома умовами.
