Прості формули

Зміст

Приклад 1

Приклад 2

Приклад 3

Приклад 4

Приклад 5

Вкладення:

simpleformulas.xls

[Прості формули]

29 kB

У прикладеному файлі кілька прикладів використання простих функцій Excel у нестандартний спосіб.

Показує можливість використання оператора% у рядку формули.

Спосіб застосування оператора в комірці C4 знайомий багатьом користувачам:

А ось використання % разом із посиланням на осередок зустрічається вкрай рідко:

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

Використання функціїSUM.

Зазвичай функцію SUM використовують для підсумовування лише безперервних діапазонів (B9).

Можна також додавати константи, оператори та складні вирази для окремих осередків - B11 та B12:

Використання функціїROUND.

Іноді виникає завдання округлення недо десятих часток, а до цілих розрядів числа. Для цього у стандартній функції ROUND другий параметр записується зі зворотним знаком. У прикладі в діапазоні B16:B19 числа округлені до десятків:

Інше цікаве завдання – це округлити число не до цілого розряду (до 0), а до половини (до 0 або 5). Стандартний ROUND так не вміє робити. Першою думкою у просунутих користувачів зазвичай виникає ідея спробувати виділити останній знак у цілому числі ізмінювати його вручну за допомогою перевірки умови на більше або дорівнює п'яти.

Є рішення простіше з використанням простої математики. Збільшимо вихідне число в 2 рази округлим до десятків, потім розділимо результат на 2 див. формулу в діапазоні C16:

Пошук результату в масиві справа наліво (аналогVLOOKUP ).

Всім економістам знайома функція вертикального пошуку діапазону VLOOKUP. Стандартний приклад використання в комірці С27:

Пошук в діапазоні $B$23:$C$26 здійснюється за стовпцем B, результат визначається третім параметром функції – номер 2 тут відповідає стовпцю C. Останній параметр для невідсортованих діапазонів вказується як БРЕХНЯ або просто 0 (Excel наводить число в логічну константу автоматично).

Якщо шуканий стовпець знаходиться праворуч від стовпця результату, то коректно застосовувати VLOOKUP не вдасться. У цьому випадку часто вдаються до редагування вихідних даних, додавання службового стовпця тощо. Можна обійтися без цього, замінивши VLOOKUP на вираз із двох функцій: MATCH та OFFSET (див. B27):

Функція ПОШУКПОЗ знаходить відносне зміщення, а потім OFFSET витягує результат із потрібного стовпця. На відміну від MATCH у виразі номер стовпця вказується негативним числом, причому початок визначається числом "0", а не "1" як VLOOKUP. Весь діапазон, що шукається, повинен бути представлений одним стовпцем. Перший параметр у OFFSET вказує на початок цього діапазону.

Використання логічної функціїOR з константами.

Стандартним способом запису логічного виразу є варіант, наведений у стовпці B діапазону B31:B35:

Перерахування умов здійснюється з використанням посилання на комірку. При цьому у прикладі її доводитьсяповторювати стільки разів, скільки чисел використовується для перевірки.

Є інший короткий спосіб запису подібного виразу – див. діапазон C31:C35:

Запис нагадує операторIN у мовах програмування. При перевірці умови на рівність не константам, а діапазону формулу доведеться вводити черезCtrl+Shift+Enter.

Коментарі

Михайло, дякую за допомогу. Поправлю про АБО обов'язково. Щось я сам себе заплутав, коли робив приклад. У файлі немає введення масиву, а пишу, що потрібен )

По ЗМІЩ не буду поки що чіпати, швидкість обчислень не така важлива саме в цій статті. Все збираюся з думками написати щось про алгоритми обчислень - там це буде важливо.

Невеликі уточнення:1. Про формулу СМЕЩ(. ПОИСКПОЗ) слід зазначити, що функція СМЕЩ є волатильной (перелічуваної чи " летючою " ), тобто. вона перераховується за будь-якої зміни на аркуші. Це стає відчутним за великої кількості подібних формул. У цьому випадку її можна замінити на =ІНДЕКС($A$23:$A$26;ПОШУКПОЗ(A27;$B$23:$B$26;0))

2. для формули =АБО(A31=) потужне введення не потрібно, т.к. використовується масив констант, а для формули =АБО(A31=A1:A3) вже потрібна комбінація з трьох пальців

Я б ще додав як приклад пошук останнього заповненого осередку або останнього не нульового осередку через ПЕРЕГЛЯД: =ПРОГЛЯД(9E+307;A1:A10) =ПРОГЛЯД(2;1/(A1:A100);A1 :A10)

А також приклади з використанням бінарного заперечення