Текстові функції та формули в excel. Робота з рядками в Excel

• знак «менше чи одно» – «<=»;

• знак «не одно» – «<>».

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

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

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. Excel може складати з урахуванням відразу кількох умов. Можна порахувати суму клітин першого стовпця, значення яких більше 2 і менше 6. І ту саму формулу можна встановити для другої колонки.

=СУМІСЛІМН(B3:B9;B3:B9;">2";B3:B9;"<6") =СУМІСЛІМН(C3:C9;C3:C9;">2";C3:C9;"<6")

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

=РАХУНОК(B3:B9;">3") =РАХУНОК(C3:C9;">3")

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

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

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

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

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

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

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

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

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

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

1. Вибираємо категорію "Математичні". Знаходимо функцію "СУМ" і натискаємо на "OK".
1. Можете спробувати перерахувати у будь-якому іншому редакторі. Процес відбуватиметься так само.

Висновок

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

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

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

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

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

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

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

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

ЗЧЕПИТИ

Для об'єднання вмісту осередків в Excel, поряд з оператором конкатенації можна використовувати текстову функцію ЗЧЕПИТИ. Вона послідовно поєднує значення вказаних осередків в одному рядку.

РЯДКОВ

Якщо Excel необхідно зробити все літери малими, тобто. перетворити їх у нижній регістр, допоможе прийде текстова функція РЯДКОВ. Вона не замінює знаки, що не є літерами.

ПРОПИСН

Текстова функція ПРОПИСНробить все букви великими, тобто. перетворює в верхній регістр. Так само як і РЯДКОВ, не замінює знаки, які не є літерами.

ПРОПНАЧ

Текстова функція ПРОПНАЧробить великою першу літеру кожного слова, а решта перетворює на малі.

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

ДЛСТР

ЛЕВСІМВ та ПРАВСИМВ

Текстові функції ЛЕВСІМВі ПРАВИМОповертають задану кількість символів, починаючи з початку або з кінця рядка. Пробіл вважається символом.

ПСТР

Текстова функція ПСТРповертає задану кількість символів, починаючи із зазначеної позиції. Пробіл вважається символом.

Збіг

Функція Збігдозволяє порівняти два текстові рядки в Excel. Якщо вони точно збігаються, то повертається значення ІСТИНА, інакше – БРЕХНЯ. Ця текстова функція враховує регістр, але ігнорує різницю у форматуванні.

Якщо регістр для Вас не грає великої ролі (так буває в більшості випадків), то можна застосувати формулу, яка просто перевіряє рівність двох осередків.

СЖПРОБІЛИ

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

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

Застосувавши функцію СЖПРОБІЛИдо значення осередку А1, ми видалимо з нього всі зайві прогалини та отримаємо коректний результат:

Функцію СЖПРОБІЛИкорисно застосовувати до даних, що імпортуються в робочі листи Excel із зовнішніх джерел. Такі дані дуже часто містять зайві прогалини та різні символи, що не друкуються. Щоб видалити всі символи, що не друкуються, з тексту, необхідно скористатися функцією ПЕЧСІМВ.

ПОВТОР

Функція ПОВТОРповторює текстовий рядок вказану кількість разів. Рядок задається як перший аргумент функції, а кількість повторів як другий.

ЗНАЙТИ

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

Ця функція чутлива до регістру.

… і може починати перегляд тексту із зазначеної позиції. На малюнку нижче формула починає перегляд із четвертого символу, тобто. з літери “ r“. Але навіть у цьому випадку положення символу вважається щодо початку тексту, що переглядається.

ПОШУК

Текстова функція ПОШУКдуже схожа на функцію ЗНАЙТИ, основне їх відмінність у тому, що ПОШУКне чутлива до регістру.

ПІДСТАВИТИ

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

Наведена нижче формула замінює всі входження слова “Excel” на “Word”:

Замінює лише перше входження слова “Excel”:

Видаляє всі прогалини з текстового рядка:

ЗАМЕНІТИ

Замінює символи, розташовані в заздалегідь відомому місці рядка, на потрібне значення. В Excel текстову функцію ЗАМЕНІТИзастосовують, коли відомо, де розташовується текст, при цьому сам він не важливий.

Формула у прикладі нижче замінює 4 символи, розташовані, починаючи з сьомої позиції, значення “2013”. Стосовно нашого прикладу, формула замінить “2010” на “2013”.

Замінює перші п'ять символів текстового рядка, тобто. слово "Excel", на "Word".

От і все! Ми познайомилися з 15 текстовими функціями Microsoft Excel і переглянули їхню дію на простих прикладах. Сподіваюся, що цей урок припав Вам якраз до речі, і Ви отримали від нього хоча б трохи корисної інформації. Усього доброго та успіхів у вивченні Excel!

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

Якщо просто спробувати вставити текст в один осередок з функцією, то при такій спробі Excel видасть повідомлення про помилку у формулі і не дозволить зробити таку вставку. Але є два способи все-таки вставити текст поруч із формульним виразом. Перший полягає у застосуванні амперсанда, а другий – у використанні функції ЗЧЕПИТИ.

Спосіб 1: використання амперсанд

Найпростіший спосіб вирішити це завдання – це застосувати символ амперсанда ( & ). Цей знак виробляє логічне відокремлення даних, які містить формула, від текстового виразу. Давайте подивимося, як можна застосувати цей спосіб на практиці.

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

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

Під час написання тексту перед формулою дотримуємося наступного синтаксису. Відразу після знака «=» відкриваємо лапки та записуємо текст. Після цього закриваємо лапки. Ставимо знак амперсанди. Потім, якщо потрібно внести пропуск, відкриваємо лапки, ставимо пропуск і закриваємо лапки. Клацаємо по клавіші Enter.

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

Спосіб 2: застосування функції зчеплення

Також для вставлення тексту разом із результатом підрахунку формули можна використовувати функцію ЗЧЕПИТИ. Цей оператор призначений для того, щоб з'єднувати в одному осередку значення, що виводяться в декількох елементах аркуша. Він відноситься до категорії текстових функцій. Його синтаксис наступний:

СТЕПИТИ(текст1;текст2;…)

У цього оператора може бути від 1 до 255 аргументів. Кожен із них представляє або текст (включаючи цифри та будь-які інші символи), або посилання на комірки, що його містять.

Подивимося, як працює ця функція практично. Для прикладу візьмемо ту саму таблицю, тільки додамо в неї ще один стовпець "Загальна сума витрат"з порожнім осередком.

1. Виділяємо порожню комірку стовпця "Загальна сума витрат". Клацаємо по піктограмі "Вставити функцію", розташовану ліворуч від рядка формул.



2. Проводиться активація Майстри функцій. Переміщуємось у категорію "Текстові". Далі виділяємо найменування «ЗЧЕПИТИ»і тиснемо на кнопку "OK".



3. Запускається віконце аргументів оператора ЗЧЕПИТИ. Це вікно складається з полів під назвою "Текст". Їхня кількість досягає 255 , але для нашого прикладу знадобиться лише три поля. У першому ми розмістимо текст, у другому – посилання на комірку, де міститься формула, й у третьому знову розмістимо текст.

   Встановлюємо курсор у полі «Текст1». Вписуємо туди слово «Разом». Писати текстові вирази можна без лапок, тому що програма простоїть їх сама.

   Потім переходимо у поле «Текст2». Встановлюємо курсор. Нам треба вказати те значення, яке виводить формула, отже, слід дати посилання на осередок, її містить. Це можна зробити, просто вписавши адресу вручну, але краще встановити курсор у полі та клікнути по комірці, що містить формулу на аркуші. Адреса відображається у вікні аргументів автоматично.

   У полі «Текст3»вписуємо слово "рублів".

   Після цього клацаємо по кнопці "OK".



4. Результат виведений в попередньо виділений осередок, але, як бачимо, як і в попередньому способі, всі значення записані разом без пробілів.



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

   Між лапками має бути пробіл. Загалом у рядку функцій має відобразитись наступний вираз:

   СЦЕПИТИ("Разом";" ";D2;" ";"рублів")

   Клацаємо по клавіші ENTER. Тепер наші значення розділені пробілами.



6. За бажання можна сховати перший стовпець "Загальна сума витрат"з вихідною формулою, щоб він не займав зайве місце на аркуші. Просто видалити його не вдасться, оскільки це порушить функцію ЗЧЕПИТИале прибрати елемент цілком можна. Клацаємо лівою кнопкою миші по сектору панелі координат того стовпця, який слід приховати. Після цього весь стовпець виділяється. Клацаємо по виділенню правою кнопкою миші. Запускається контекстне меню. Вибираємо у ньому пункт «Приховати».



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

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

Формули з текстовими функціями Excel

Спочатку розглянемо з прикладу 3 текстових функції Excel:

1. ПРОПІСН - дана текстова функція змінює всі літери в слові на великі, великі.
2. РЯДКОВ – ця функція перетворює всі символи тексту на малі, маленькі літери.
3. ПРОПНАЧ – функція змінює лише першу букву у кожному слові на заголовну, велику.

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

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

Щоб вирішити це популярне завдання, потрібно у формулі використовувати додаткові текстові функції Excel: ЛЕВСІМВ, ПРАВСІМВ і ДЛСТР.

Принцип дії формули для заміни першої літери у реченні

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

У лівій частині формули використовується додаткова функція ЛЕВСІМВ:

Завдання цієї частини формули змінити першу літеру на велику у вихідному текстовому рядку комірки A1. Завдяки функції ЛЕВСІМВ можна отримувати певну кількість символів, починаючи з лівого боку тексту. Функція вимагає заповнити 2 аргументи:

1. Текст – посилання на комірку з вихідним текстом.
2. Кількість знаків – число символів, що повертаються, з лівого боку (з початку) вихідного тексту.

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

Права частина формули після оператора дуже схожа за принципом дії на ліву частину, тільки вона вирішує інше завдання. Її завдання – перетворити всі символи тексту на маленькі літери. Але зробити це потрібно так, щоб не змінювати першу велику букву, за яку відповідає ліва частина формули. У місце функції ЛЕВСІМВ у правій частині формули застосовується функція ПРАВСІМВ:

Текстова функція ПРАВСІМВ працює обернено пропорційно функції ЛЕВСІМВ. Також вимагає запилення двох аргументів: вихідний текст та кількість знаків. Але повертає вона кілька літер, отриманих праворуч вихідного тексту. Однак у даному випадку ми як другий аргумент не можемо вказати фіксоване значення. Адже нам наперед невідома кількість символів у вихідному тексті. Крім того, довжина різних текстових рядків може відрізнятися. Тому нам необхідно попередньо підрахувати довжину рядка тексту та від отриманого числового значення відібрати -1, щоб не змінювати першу велику літеру в рядку. Адже перша літера обробляється лівою частиною формули і вже перетворена на вимоги користувача. Тому на неї не повинна впливати жодна функція з правої частини формули.

Для автоматичного підрахунку довжини вихідного тексту використовується текстова функція Excel - ДЛСТР (розшифруватися як довжина рядка). Ця функція вимагає для заповнення лише одного аргументу – посилання на вихідний текст. В результаті обчислення вона повертає числове значення, тому після функції = ДЛСТР (A1) забираємо -1. Що дає нам можливість не торкатися першої великої літери правою частиною формули. В результаті функція ПРАВСІМВ повертає текстовий рядок без одного першого символу для функції СТРОЧН, який замінює всі символи тексту на маленькі літери.

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