1. Пошук по мітках

Чи часто Вам доводилося розбирати чужий файл з незрозумілими на перший погляд формулами? Начебто вважають, але як? Начебто і розібратися хочеться як працює якась мега-формула - але як це зробити? Я хочу розповісти про пару простих кроків, які необхідно зробити, щоб розібратися в роботі будь-якої формули. Давайте спробуємо розібратися на прикладі формули з моєї статті: Як отримати список унікальних (не повторюються) значень? :
= ІНДЕКС ($ A $ 2: $ A $ 51; НАЙМЕНШИЙ (ЯКЩО (СЧЁТЕСЛІ ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; СТРОКА ($ A $ 1: $ A $ 50)); 1))
Що нам знадобиться для початку:

1. знати що таке формула
2. знати що таке формула масиву
3. Чи не лінуватися заглядати в довідку з невідомої функції. Як це зробити: ставимо курсор миші на потрібну формулу і тиснемо F1 (в Excel 2003 і попередніх версіях тільки так можна). Починаючи з Excel 2007 можна ще й інакше: ставимо курсор всередину функції - з'явиться підказка по функції. Після чого натискаємо на ім'я функції з підказки:
   
   Чим це допоможе? Щоб зрозуміти як працює формула в цілому, необхідно знати, що робить кожна функція в неї вкладена і для чого призначені її аргументи хоча б в загальних рисах.
4. Не обов'язково, але бажано завантажити файл, прикладений до статті Як отримати список унікальних (не повторюються) значень? , Щоб наочно пройти всі кроки, описані нижче
   Завантажити приклад:

   Tips_All_ExtractUnique.xls (108,0 KiB, 14 142 завантажень)

Якщо Ви не знайомі з функціями, використовуваними в наведеній вище формулі і хочете розібратися - необхідно переглянути довідку щодо ним, інакше роботу формули не зрозумієте навіть з поясненнями

Ось тепер можна почати потрошити формулу. В принципі, найскладніший етап вже пройдено. Тепер залишається тільки скористатися вбудованим засобом Excel - вікно перегляду етапів обчислень формули. Виділяємо осередок з потрібною формулою і:
для користувачів Excel 2007 і пізніших версій:
вкладка Формули-група кнопок Залежності формул - Обчислити формулу (Formulas - Formula Auditing - Evaluate Formula)
для користувачів Excel 2003:
Сервіс - Залежності формул - Обчислити формулу

з'явиться форма

Після кожного натискання на кнопку Обчислити (Evaluate) буде проведений черговий етап обчислень формули і в вікні форми буде відображений цей етап. Обчислюється в поточний момент частина формули (етап) підкреслюється одинарної лінією.
Що слід знати: спочатку обчислюється сама глибоко вкладена функція, а вже потім найперша. Найперша і основна функція у нас буде ІНДЕКС, а сама глибоко вкладена - СЧЁТЕСЛІ. Тому на прикладі нашої формули наступним етапом буде обчислення функції СЧЁТЕСЛІ і в дужках буде показаний результат для цієї функції: {0: 0: 0: 0: 0 ... 0: 0: 0}. Тобто для кожного значення діапазону $ A $ 2: $ A $ 51 буде виведено кількість - скільки разів це значення зустрічається в діапазоні $ C $ 1: C1. Оскільки це перший рядок формули - то будуть всі нулі:

Далі буде вироблено обчислення логічного виразу = 0: порівняння результату функції СЧЁТЕСЛІ з нулем. Результатом буде ІСТИНА або БРЕХНЯ.

Цей результат (ІСТИНА, БРЕХНЯ) обробляється далі функцією ЯКЩО. А в ЯКЩО у нас умова: якщо СЧЁТЕСЛІ дорівнює нулю (тобто якщо результат ІСТИНА), то в ЯКЩО повертаємо номер рядка (СТРОКА ($ A $ 1: $ A $ 50)), якщо немає - то поверне БРЕХНЯ.

Оскільки функція НАЙМЕНШИЙ працює тільки з числами, ігноруючи будь-які інші значення, то вона не буде враховувати БРЕХНЯ (т.к. це логічне значення, а не число), а відбиратиме тільки числа - що і лягає в основу формули.

Щоб в цьому прикладі було простіше розібратися (наскільки це можливо), коротко розкажу про принцип роботи цієї формули: якщо значення з діапазону $ A $ 2: $ A $ 51 зустрічається в діапазоні виведення формули (на рядок вище) $ C $ 1: C1, то СЧЁТЕСЛІ повернеться не нульове значення і вийде БРЕХНЯ. Якщо такого значення ще немає - буде нуль і в НАЙМЕНШИЙ буде переданий номер рядка. А вже номер рядка передається в ІНДЕКС, яка повертає безпосередньо значення за номером рядка. Щоб більш точно зрозуміти подібні формули треба розглянути не тільки формулу з першого осередку, а й пару наступних.

Крім кнопки Обчислити в цьому вікні є й інші: Крок із заходом (Step In) і Крок з виходом (Step Out). Роблять вони майже те ж саме, але доступні не для всіх видів формул, а лише для тих, у яких беруть участь посилання на комірки з іншими функціями. Якщо обчислюється зараз функція містить усередині посилання на осередок, в якій записана інша функція або формула - то Крок із заходом (Step In) виводить у вікно обчислення цю функцію (формулу) і активує осередок з цією формулою. При цьому доступна ця кнопка стає лише тоді, коли при обчисленні основний формули крок обчислення доходить до цієї самої посилання на вкладену формулу. Крок з виходом (Step Out) при цьому повертає до обчислення попередньої формули.

Невеликий практична порада: якщо використовуєте інструмент Обчислити формулу для пошуку помилки в своїй формулі для пошуку помилки і в формулі використовуються занадто великі діапазони, то переглядати по кроках таку формулу незручно. Щоб було простіше - можна зменшити діапазони осередків до 10, виділити осередок з помилковим результатом і подивитися етап обчислення - всі беруть участь осередки будуть на увазі і простіше буде зрозуміти де помилка.
Звичайно, якщо формулу створив хтось інший такий підхід не завжди справедливий для складних формул, тому що зміна діапазонів без розуміння для чого вони можуть призвести до неробочої формулою і в цьому випадку дивитися етапи обчислення марно.

Є ще одна можливість аналізувати етапи обчислень. Необхідно виділити осередок з потрібною формулою, перейти в рядок формул і там виділити фрагмент формули, результат обчислення якого потрібно отримати:

після чого, не знімаючи виділення натискаємо клавішу F9. Виділений блок формули буде вирахувано і результат буде поміщений на місце виділеного блоку формули:

Мені цей метод подобається менше, тому що він не показує саме кроки обчислення, а обчислює разом виділений блок. Тому його можна застосовувати у випадках, коли порядок обчислення відомий і треба лише переконатися, що цікавить блок формули працює правильно.

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках