• знак "менше або одно" - "<=»;

• знак "не рівно" - "<>».

Оператор об'єднання тексту

Для цього використовується спеціальний символ «&» (амперсанд). За допомогою його можна поєднати різні фрагменти в одне ціле – той самий принцип, що й з функцією «ЗЧЕПИТИ». Наведемо кілька прикладів:

1. Якщо ви хочете об'єднати текст у комірках, потрібно використовувати наступний код.

1. Для того, щоб вставити між ними якийсь символ або букву, потрібно використовувати таку конструкцію.

1. Об'єднувати можна як комірки, а й звичайні символи.

Будь-який текст, крім посилань, необхідно вказувати у лапках. Інакше формула дасть помилку.

Зверніть увагу, що лапки використовують саме такі, як на скріншоті.

Для визначення посилань можна використовувати такі оператори:

• для того, щоб створити просте посилання на потрібний діапазон осередків, достатньо вказати першу та останню клітинку цієї області, а між ними символ «:»;
• для поєднання посилань використовується знак «;»;
• якщо необхідно визначити клітини, які перебувають на перетині кількох діапазонів, між посиланнями ставиться «пробіл». У разі виведеться значення клітини «C7».

Оскільки тільки вона підпадає під визначення «перетину множин». Саме таку назву має даний оператор (пробіл).

Використання посилань

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

Прості посилання A1

Як правило, цей вид використовують найчастіше, оскільки їх складати набагато зручніше, ніж решта.

• стовпців - від A до XFD (не більше 16 384);
• рядків - від 1 до 1048576.

Наведемо кілька прикладів:

• осередок на перетині рядка 5 та стовпця B – «B5»;
• діапазон осередків у стовпці B починаючи з 5 по 25 рядок – «B5: B25»;
• діапазон осередків у рядку 5, починаючи зі стовпця B до F – «B5:F5»;
• усі осередки у рядку 10 – «10:10»;
• всі осередки у рядках з 10 по 15 – «10:15»;
• всі клітини в стовпці B - "B: B";
• всі клітини в стовпцях з B до K – «B:K»;
• діапазон осередків з B2 до F5 – «B2-F5».

Іноді у формулах використовується інформація з інших аркушів. Працює це в такий спосіб.

На другому аркуші наведено такі дані.

Якщо в назві листа є пробіл, то у формулі його потрібно вказувати в одинарних лапках (апострофи).

Абсолютні та відносні посилання

Редактор Ексель працює з трьома видами посилань:

• абсолютні;
• відносні;
• змішані.

Розглянемо їх уважніше.

Усі зазначені раніше приклади належать до відносної адреси осередків. Цей тип найпопулярніший. Головна практична перевага в тому, що редактор під час перенесення змінить посилання інше значення. Відповідно, куди саме ви скопіювали цю формулу. Для підрахунку враховуватиметься кількість клітин між старим і новим становищем.

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

1. Введемо формулу до розрахунку суми першої колонки.

1. Натисніть клавішу Ctrl + C . Щоб перенести формулу на сусідню клітину, необхідно перейти туди і натиснути на Ctrl +V .

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

1. Тепер подивіться нові формули. Зміна індексу стовпця відбулася автоматично.

Якщо ви хочете, щоб при перенесенні формул усі посилання зберігалися (тобто вони не змінювалися в автоматичному режимі), потрібно використовувати абсолютні адреси. Вони вказуються як «$B$2».

У результаті ми бачимо, що жодних змін не відбулося. У всіх стовпцях у нас відображається те саме число.

Цей тип адрес використовується тоді, коли необхідно зафіксувати тільки стовпець або рядок, а не одночасно. Використовувати можна такі конструкції:

• $D1, $F5, $G3 – для фіксації стовпців;
• D$1, F$5, G$3 – для фіксації рядків.

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

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

1. Як приклад використовуємо таке вираз.

1. Перенесемо цю формулу в іншу комірку. Бажано не на наступний і на іншому рядку. Тепер ви бачимо, що новий вираз містить той самий рядок (4), але іншу літеру, оскільки тільки він був відносною.

Тривимірні посилання

Під поняття «тривимірні» потрапляють адреси, у яких вказується діапазон аркушів. Приклад формули виглядає так.

У цьому випадку результат буде відповідати сумі всіх осередків «A5» на всіх аркушах, починаючи з 1 по 4. При складанні таких виразів необхідно дотримуватись наступних умов:

• у масивах не можна використовувати подібні посилання;
• тривимірні вирази забороняється використовувати там, де є перетин осередків (наприклад, оператор «пробіл»);
• при створенні формул з тривимірними адресами можна використовувати наступні функції: СРЗНАЧ, СТАНДОТКЛОНА, СТАНДОТКЛОН.В, СРЗНАЧА, СТАНДОТКЛОНПА, СТАНДОТКЛОН.Г, СУМ, РАХУНОК, РАХУНОК, МІН, МАКС, МІНА, МІН, МІНА, МІН, МІНА, МІНА, МІНА, МІНА, МІНА, МІНА, МІНА. та ДИСП.

Якщо порушити ці правила, то ви побачите якусь помилку.

Посилання формату R1C1

Цей тип посилань від «A1» відрізняється тим, що номер задається не тільки рядкам, а й стовпцям. Розробники вирішили замінити звичайний вид на цей варіант для зручності в макросах, але їх можна використовувати будь-де. Наведемо кілька прикладів таких адрес:

• R10C10 - абсолютне посилання на клітину, яка розташована на десятому рядку десятого стовпця;
• R - Абсолютне посилання на поточну (в якій вказується формула) посилання;
• R[-2] - відносне посилання на рядок, яка розташована на дві позиції вище цієї;
• R[-3]C - відносне посилання на клітину, яка розташована на три позиції вище в поточному стовпці (де ви вирішили прописати формулу);
• RC - відносне посилання на клітину, яка розташована на п'ять клітин правіше і п'ять рядків нижче поточної.

Використання імен

Програма Excel для позначення діапазонів осередків, одиночних осередків, таблиць (звичайні та зведені), констант та виразів дозволяє створювати свої унікальні імена. При цьому для редактора жодної різниці під час роботи з формулами немає – він розуміє все.

Імена ви можете використовувати для множення, поділу, додавання, віднімання, розрахунку відсотків, коефіцієнтів, відхилення, округлення, ПДВ, іпотеки, кредиту, кошторису, табелів, різних бланків, знижки, зарплати, стажу, ануїтетного платежу, роботи з формулами «ВПР» , «ВСД», «ПРОМІЖНІ.ПІДСУМКИ» і так далі. Тобто можете робити будь-що.

Головною умовою можна назвати лише одну – ви повинні заздалегідь визначити це ім'я. Інакше Ексель про нього нічого не знатиме. Робиться це так.

1. Виділіть якийсь стовпець.
2. Викличте контекстне меню.
3. Виберіть пункт «Призначити ім'я».

1. Вкажіть бажане ім'я цього об'єкта. При цьому слід дотримуватися наступних правил.

1. Щоб зберегти, натисніть кнопку «OK».

Так само можна присвоїти ім'я якомусь осередку, тексту чи числу.

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

А якщо спробувати замість адреси D4:D9 вставити наше ім'я, то ви побачите підказку. Достатньо написати кілька знаків, і ви побачите, що найбільше підходить (з бази імен).

У нашому випадку все просто – «стовпець_3». А уявіть, що у вас таких імен буде безліч. Усі напам'ять ви запам'ятати не зможете.

Використання функцій

У редакторі Excel вставити функцію можна кількома способами:

• вручну;
• за допомогою панелі інструментів;
• за допомогою вікна "Вставка функції".

Розглянемо кожен метод уважніше.

У цьому випадку все просто – ви за допомогою рук, власних знань та умінь вводите формули у спеціальному рядку або прямо в комірці.

Якщо ж у вас немає робочого досвіду в цій галузі, то краще спочатку використовувати полегшені методи.

В цьому випадку необхідно:

1. Перейти на вкладку Формули.
2. Клікнути на якусь бібліотеку.
3. Вибрати потрібну функцію.

1. Відразу після цього з'явиться вікно «Аргументи та функції» із вже обраною функцією. Вам залишається лише проставити аргументи та зберегти формулу за допомогою кнопки «OK».

Майстер підстановки

Застосувати його можна так:

1. Зробіть активним будь-яку комірку.
2. Натисніть на іконку «Fx» або натисніть клавішу SHIFT +F3 .

1. Відразу після цього з'явиться вікно «Вставка функції».
2. Тут ви побачите великий список різних функцій, відсортованих за категоріями. Крім того, можна скористатися пошуком, якщо ви не можете знайти потрібний пункт.

Достатньо забити якесь слово, яким можна описати те, що ви хочете зробити, а редактор спробує вивести всі відповідні варіанти.

1. Виберіть якусь функцію із запропонованого списку.
2. Щоб продовжити, потрібно натиснути кнопку «OK».

1. Потім вас попросять вказати «Аргументи та функції». Зробити це можна вручну або легко виділити необхідний діапазон осередків.
2. Щоб застосувати всі налаштування, потрібно натиснути на кнопку «OK».

1. В результаті цього ми побачимо цифру 6, хоча це було так зрозуміло, оскільки у вікні «Аргументи і функції» виводиться попередній результат. Дані перераховуються моментально за зміни будь-якого з аргументів.

Використання вкладених функцій

Як приклад використовуватимемо формули з логічними умовами. Для цього нам потрібно буде додати якусь таблицю.

Потім дотримуйтесь наступної інструкції:

1. Клацніть на перший осередок. Викличте вікно «Вставка функції». Виберіть «Якщо». Натисніть «OK», щоб вставити.

1. Потім потрібно буде скласти якесь логічне вираження. Його необхідно записати у перше поле. Наприклад, можна скласти значення трьох осередків в одному рядку і перевірити, чи буде сума більша за 10. У разі «істини» вказуємо текст «Більше 10». Для хибного результату – «Менше 10». Потім для повернення до робочого простору натискаємо на «OK».

1. У результаті ми бачимо наступне – редактор видав, що сума осередків у третьому рядку менша за 10. І це правильно. Отже, наш код працює.

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

1. У результаті редактор перераховує наш вираз для кожного рядка.

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

Зробити це можна кількома способами: використовувати рядок формул або спеціальний майстер. У першому випадку все просто – клацаєте у спеціальне поле та вручну вводите потрібні зміни. Але писати там не зовсім зручно.

Єдине, що ви можете зробити, це збільшити поле для введення. Для цього достатньо клікнути на вказану іконку або натиснути клавіші Ctrl + Shift + U .

Це єдиний спосіб, якщо ви не використовуєте у формулі функції.

У разі використання функцій все стає набагато простіше. Для редагування необхідно дотримуватися наступної інструкції:

1. Зробіть активною клітину із формулою. Натисніть на іконку Fx.

1. Після цього з'явиться вікно, в якому ви зможете у дуже зручному вигляді змінити потрібні аргументи функції. Крім цього, тут можна дізнатися, яким буде результат перерахунку нового висловлювання.

1. Щоб зберегти внесені зміни, потрібно використовувати кнопку «OK».

Для того, щоб видалити якийсь вираз, достатньо зробити таке:

1. Клацніть на будь-яку комірку.

1. Натисніть кнопку Delete або Backspace. Внаслідок цього клітина виявиться порожньою.

Домогтися такого самого результату можна і за допомогою інструмента «Очистити все».

Можливі помилки при складанні формул у редакторі Excel

Нижче наведено найпопулярніші помилки, які допускаються користувачами:

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

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

• не зазначаються обов'язкові аргументи функцій. Зверніть увагу, що необов'язкові аргументи вказуються в квадратних дужках. Все, що без них – необхідно для повноцінної роботи формули;

• неправильно вказуються діапазони осередків. Для цього необхідно використовувати оператор «:» (двокрапка).

Коди помилок під час роботи з формулами

При роботі з формулою ви можете побачити такі варіанти помилок:

• #ЗНАЧ! - Ця помилка показує, що ви використовуєте неправильний тип даних. Наприклад, замість числового значення намагаєтеся використати текст. Зрозуміло, Ексель зможе обчислити суму між двома фразами;
• #ІМ'Я? – подібна помилка означає, що ви припустилися помилки у написанні назви функції. Або ж намагаєтеся запровадити щось неіснуюче. Так робити не можна. Крім цього проблема може бути і в іншому. Якщо ви впевнені в імені функції, спробуйте подивитися на формулу уважніше. Можливо, ви забули якусь дужку. Крім цього, слід враховувати, що текстові фрагменти вказуються в лапках. Якщо нічого не допомагає, спробуйте скласти вираз наново;
• #КІЛЬКІСТЬ! – відображення подібного повідомлення означає, що у вас якась проблема з аргументами чи результатом виконання формули. Наприклад, число вийшло надто величезним або навпаки – маленьким;
• #ДІЛ/0! - Ця помилка означає, що ви намагаєтеся написати вираз, в якому відбувається розподіл на нуль. Excel не може скасувати правила математики. Тому такі дії також заборонені;
• #Н/Д! – редактор може показати це повідомлення, якщо якесь значення недоступне. Наприклад, якщо ви використовуєте функції ПОШУК, ПОШУК, ПОШУКПОЗ, та Excel не знайшов шуканий фрагмент. Або даних взагалі немає і формулі немає з чим працювати;
• Якщо ви намагаєтеся щось порахувати, і Excel пише слово #ПОСИЛКА!, значить, в аргументі функції використовується неправильний діапазон осередків;
• #ПУСТО! – ця помилка з'являється в тому випадку, якщо у вас використовується формула, що не узгоджується, з діапазонами, що перетинаються. Точніше – якщо насправді подібні осередки відсутні (які перебувають на перетині двох діапазонів). Часто така помилка виникає випадково. Достатньо залишити одну прогалину в аргументі, і редактор сприйме її як спеціальний оператор (про нього ми розповідали раніше).

При редагуванні формули (комірки підсвічуються) ви побачите, що вони насправді не перетинаються.

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

Для того щоб побачити значення, що міститься там, достатньо змінити розмір стовпця.

Крім цього, можна використовувати форматування осередків. Для цього необхідно виконати декілька простих кроків:

1. Викличте контекстне меню. Виберіть «Формат комірок».

1. Вкажіть тип "Загальний". Для продовження натисніть кнопку «OK».

Завдяки цьому редактор Ексель зможе перевести це число в інший формат, який міститься в цьому стовпці.

Приклади використання формул

Редактор Microsoft Excel дозволяє обробляти інформацію будь-яким зручним для вас способом. Для цього є всі необхідні умови та можливості. Розглянемо кілька прикладів формул за категоріями. Так вам простіше буде розібратися.

Щоб оцінити математичні можливості Экселя, потрібно виконати такі дії.

1. Створіть таблицю з умовними даними.

1. Щоб вирахувати суму, введіть наступну формулу. Якщо хочете додати лише одне значення, можна використовувати оператор додавання («+»).

1. Як не дивно, у редакторі Excel не можна відібрати за допомогою функцій. Для відрахування використовується стандартний оператор «-». І тут код вийде наступний.

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

Зверніть увагу, що символ відсотка ставиться наприкінці, а чи не на початку. Крім цього, під час роботи з відсотками не потрібно додатково множити на 100. Це відбувається автоматично.

1. Для визначення середнього значення використовуйте таку формулу.

1. В результаті описаних вище виразів ви побачите наступний підсумок.

1. Для цього збільшимо нашу таблицю.

1. Наприклад, складемо ті осередки, у яких значення більше трьох.

1. Excel може складати з урахуванням відразу кількох умов. Можна порахувати суму клітин першого стовпця, значення яких більше 2 і менше 6. І ту саму формулу можна встановити для другої колонки.

1. Також можна порахувати кількість елементів, які задовольняють якусь умову. Наприклад, нехай Ексель порахує, скільки в нас чисел більше ніж 3.

1. Результат усіх формул буде наступним.

Математичні функції та графіки

За допомогою Екселя можна розраховувати різні функції та будувати за ними графіки, а потім проводити графічний аналіз. Як правило, подібні прийоми використовуються у презентаціях.

Як приклад спробуємо побудувати графіки для експоненти та якогось рівняння. Інструкція буде наступною:

1. Створимо таблицю. У першій графі ми матимемо вихідне число «X», у другій – функція «EXP», у третій – зазначене співвідношення. Можна було б зробити квадратичний вираз, але тоді результуюче значення на тлі експоненти на графіці практично зникло б.

1. Щоб перетворити значення «X», потрібно вказати такі формулы.

1. Дублюємо ці висловлювання аж до кінця. У результаті одержуємо наступний результат.

1. Виділяємо всю таблицю. Переходимо на вкладку "Вставка". Клацаємо на інструмент «Рекомендовані діаграми».

1. Вибираємо тип "Лінія". Для продовження натискаємо на «OK».

1. Результат вийшов досить гарний і акуратний.

Як ми й казали раніше, приріст експоненти відбувається набагато швидше, ніж у звичайного кубічного рівняння.

Подібним чином можна уявити графічно будь-яку функцію чи математичне вираз.

Все описане вище підходить для сучасних програм 2007, 2010, 2013 та 2016 років. Старий редактор Ексель значно поступається у плані можливостей, кількості функцій та інструментів. Якщо відкриєте офіційну довідку від Microsoft, то побачите, що вони додатково вказують, у якій версії програми з'явилася ця функція.

У всьому іншому все виглядає практично так само. Як приклад, порахуємо суму кількох осередків. Для цього необхідно:

1. Вказати якісь дані для обчислення. Клацніть на будь-яку клітинку. Натисніть на іконку Fx.

1. Вибираємо категорію "Математичні". Знаходимо функцію «СУМ» та натискаємо на «OK».

1. Вказуємо дані у потрібному діапазоні. Щоб відобразити результат, потрібно натиснути на «OK».

1. Можете спробувати перерахувати у будь-якому іншому редакторі. Процес відбуватиметься так само.

Висновок

У цьому самовчителі ми розповіли про все, що пов'язано з формулами в редакторі Excel, – від найпростішого до дуже складного. Кожен розділ супроводжувався докладними прикладами та поясненнями. Це зроблено для того, щоб інформація була доступна навіть для повних чайників.

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

Крім цього важливо пам'ятати, що формули повинні починатися з символу «=» (рівно). Багато користувачів-початківців забувають про це.

Файл прикладів

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

Відеоінструкція

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

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

Використовуючи виділення

Найпростіший – дозволить скласти всі числа, які будуть у вибраній області. Виділіть необхідний діапазон та подивіться на рядок стану. Тут буде вказано «Середнє» для всіх значень, «Кількість» виділених осередків та «Сума» значень. Виділяти можете порожні блоки і ті, де є текст, оскільки додаються лише числа.

Якщо у Вашій таблиці є рядок «Підсумки» – такий спосіб не підійде. Звичайно, можна вписати туди число, але якщо дані для таблиці зміняться, потрібно не забути змінити їх і в блоці «Підсумки» . Це не зовсім зручно, та й у Екселі можна застосувати інші способи, які автоматично перераховуватимуть результат у блоці.

За допомогою автосуми

Підсумувати числа в стовпці можна за допомогою кнопки «Автосума» . Для цього виділіть порожню комірку в стовпці, відразу під значеннями. Потім перейдіть на вкладку Формули і натисніть Автосума . Ексель автоматично виділить верхні блоки до першого порожнього. Натисніть "Enter", щоб все порахувалося.

Також можете спочатку виділити блоки в стовпці, враховуючи і порожні, і з текстом - вони просто не будуть враховані при розрахунку, а потім натиснути "Автосумма". Результат з'явиться в першому порожньому осередку виділеного стовпця.

Використовуючи формулу

Зробити потрібний нам розрахунок у Excel можна використовуючи всім знайому математичну формулу. Поставте «=» у потрібній комірці, потім мишкою виділяйте всі потрібні. Між ними не забувайте ставити знак плюс. Потім натисніть "Enter".

Найзручніший спосіб розрахунку – це використання функції СУММ. Поставте «=» у комірці, потім наберіть «СУМ», відкрийте дужку «(» і виділіть потрібний діапазон. Поставте «)» і натисніть «Enter».

Також прописати функцію можна прямо у рядку формул.

Якщо в якості аргументу функції вказати діапазон блоків, в яких, крім чисел, містяться і текст, і порожні блоки – функція поверне правильний результат. Це тому, що для розрахунку враховуватимуться лише осередки з числами.