У деяких випадках перед користувачем стоїть завдання повернути в цільову комірку з іншого осередку певну кількість символів, починаючи з зазначеного за рахунком знака зліва. З цим завданням чудово справляється функція ПСТР. Ще більше збільшується її функціонал, якщо в поєднання з нею застосовувати інші оператори, наприклад ПОШУК або ШУКАТИ. Давайте докладніше розберемо, в чому полягають можливості функції ПСТР і подивимося, як вона працює на конкретних прикладах.
Використання ПСТР
Основне завдання оператора ПСТР полягає в вилученні з зазначеного елемента листа певного числа друкованих знаків, враховуючи пробіли, починаючи з зазначеного за рахунком зліва символу. Ця функція відноситься до категорії текстових операторів. Її синтаксис приймає наступний вигляд:= ПСТР (текст; начальная_позіція; колічество_знаков)
Як бачимо, дана формула складається з трьох аргументів. Всі вони є обов'язковими.
Аргумент «Текст» містить адресу того елемента листа, в якому знаходиться текстове вираження з вилучаються знаками.
Аргумент «Початкова позиція» представлений у вигляді числа, яке вказує, з якого знака за рахунком, починаючи зліва, потрібно робити витяг. Перший знак вважається за «1», другий за «2» і т.д. У підрахунку враховуються навіть прогалини.
Аргумент «Кількість знаків» містить в собі числовий покажчик кількості символів, починаючи від початкової позиції, які потрібно витягти в цільову комірку. При підрахунку так само, як і у попереднього аргументу, враховуються прогалини.
Приклад 1: одиничне витяг
Описувати приклади застосування функції ПСТР почнемо з найпростішого випадку, коли потрібно витягти одиничне вираз. Звичайно, подібні варіанти на практиці застосовуються вкрай рідко, тому ми наводимо цей приклад тільки в якості ознайомлення з принципами роботи зазначеного оператора.
Отже, у нас є таблиця працівників підприємства. У першій колонці вказані імена, прізвища та по батькові співробітників. Нам потрібно за допомогою оператора ПСТР витягти тільки прізвище першої особи зі списку Петра Івановича Миколаєва в зазначену осередок.
- Виділяємо елемент листа, в який буде проводитися витяг. Клацаємо по кнопці «Вставити функцію», яка розташована біля рядка формул.
- Запускається віконце Майстра функцій. Переходимо в категорію «Текстові». Виділяємо там найменування «ПСТР» і клацаємо по кнопці «OK».
- Виробляється запуск вікна аргументів оператора «ПСТР». Як бачимо, в цьому вікні число полів відповідає кількості аргументів цієї функції.
В поле «Текст» вводимо координати осередку, яка містить ПІБ працівників. Щоб не забивати адресу вручну, просто встановлюємо курсор в поле і натискаємо лівою кнопкою миші по елементу на аркуші, в якому містяться потрібні нам дані.
В поле «Початкова позиція» потрібно вказати номер символу, вважаючи зліва, з якого починається прізвище працівника. При підрахунку враховуємо також прогалини. Буква «Н», з якої починається прізвище співробітника Миколаєва, є п'ятнадцятим по рахунку символом. Тому в поле ставимо число «15».
В поле «Кількість знаків» потрібно вказати кількість символів, з яких складається прізвище. Вона складається з восьми знаків. Але з огляду на, що після прізвища в осередку немає більше символів, ми можемо вказати і більшу кількість знаків. Тобто, в нашому випадку можна поставити будь-яке число, що дорівнює або більше восьми. Ставимо, наприклад, число «10». Але якби після прізвища в осередку були б ще слова, цифри або інші символи, то нам би довелося встановлювати тільки точне число знаків ( «8»).
Після того, як всі дані введені, тиснемо на кнопку «OK».
- Як бачимо, після цього дії прізвище працівника була виведена в зазначену нами в першому кроці Прикладу 1 осередок.
урок: Майстер функцій в Ексель
Приклад 2: групове витяг
Але, природно, в практичних цілях легше вручну вбивати одиночну прізвище, ніж застосовувати для цього формулу. А ось для перенесення групи даних використання функції буде цілком доцільним.
Маємо список смартфонів. Перед найменуванням кожної моделі варто слово «Смартфон». Нам потрібно винести в окремий стовпець тільки назви моделей без цього слова.
- Виділяємо перший порожній елемент стовпця, в який буде виводитися результат, і викликаємо вікно аргументів оператора ПСТР тим же способом, що і в попередньому прикладі.
В поле «Текст» вказуємо адресу першого елемента колонки з вихідними даними.
В поле «Початкова позиція» нам потрібно вказати номер символу, починаючи з якого будуть вилучатись дані. У нашому випадку в кожній клітинці перед найменуванням моделі варто слово «Смартфон» та пробіл. Таким чином, та фраза, яку потрібно вивести в окреме вічко всюди починається з десятого символу. Встановлюємо число «10» в цьому полі.
В поле «Кількість знаків» потрібно встановити то число символів, яке містить виведене словосполучення. Як бачимо, в найменуванні кожної моделі різну кількість символів. Але рятує ситуацію той факт, що після назви моделі, текст в осередках закінчується. Тому ми можемо встановити в цьому полі будь-яке число, що дорівнює або більше кількості символів в найдовшому найменуванні в даному списку. Встановлюємо будь-яку кількість знаків «50». Назва жодного з перерахованих смартфонів не перевищує 50 символів, тому вказаний варіант нам підходить.
Після того, як дані введені, тиснемо на кнопку «OK».
- Після цього найменування першої моделі смартфона виводиться в заздалегідь зазначену комірку таблиці.
- Для того, щоб не вводити в кожну комірку стовпчика формулу окремо, виробляємо її копіювання за допомогою маркера заповнення. Для цього ставимо курсор в нижній правий кут комірки з формулою. Курсор перетвориться в маркер заповнення у вигляді невеликого хрестика. Затискаємо ліву кнопку мишки і тягнемо його до самого кінця стовпчика.
- Як бачимо, вся колонка після цього буде заповнена потрібними нам даними. Секрет полягає в тому, що аргумент «Текст» являє собою відносне посилання і в міру зміни положення цільових осередків теж змінюється.
- Але проблема полягає в тому, що якщо ми вирішимо раптом змінити або видалити стовпець з початковими даними, то дані в цільовому стовпці стануть відображатися некоректно, так як вони пов'язані один з одним формулою.
Щоб «відв'язати» результат від первісної колонки, виробляємо наступні маніпуляції. Виділяємо стовпець, який містить формулу. Далі переходимо у вкладку «Головна» і тиснемо на піктограму «Копіювати», розташовану в блоці «Буфер обміну» на стрічці.
Як альтернативне дію, можна після виділення натиснути комбінацію клавіш Ctrl + C.
- Далі, не знімаючи виділення, клацаємо по колонці правою кнопкою миші. Відкривається контекстне меню. У блоці «Параметри вставки» клацаємо по піктограмі «Значення».
- Після цього замість формул в виділений стовпець будуть вставлені значення. Тепер ви можете без побоювання змінювати або видаляти вихідну колонку. На результат це вже ніяк не вплине.
Приклад 3: використання комбінації операторів
Але все-таки зазначений вище приклад обмежений тим, що перше слово у всіх вихідних осередків повинно мати рівну кількість символів. Застосування разом з функцією ПСТР операторів ПОШУК або ШУКАТИ дозволить значно розширити можливості використання формули.
Текстові оператори ПОШУК і ШУКАТИ повертають позицію зазначеного символу в просматриваемом тексті.
Синтаксис функції ПОШУК наступний:
= ПОШУК (іскомий_текст; текст_для_поіска; начальная_позіція)
Синтаксис оператора ШУКАТИ виглядає таким чином:
= ШУКАТИ (іскомий_текст; просматріваемий_текст; нач_позіція)
За великим рахунком аргументи цих двох функцій тотожні. Їх головна відмінність полягає в тому, що оператор ПОШУК при обробці даних не враховує регістр букв, а ШУКАТИ - враховує.
Подивимося, як використовувати оператор ПОШУК в поєднанні з функцією ПСТР. Маємо таблицю, в яку занесені найменування різних моделей комп'ютерної техніки із узагальнюючою назвою. Як і минулого разу, нам потрібно витягти найменування моделей без узагальнюючого назви. Складність полягає в тому, що якщо в попередньому прикладі узагальнююче найменування для всіх позицій було одне й те саме ( «смартфон»), то в цьому списку воно різне ( «комп'ютер», «монітор», «колонки» і т.д.) з різною кількістю символів. Щоб вирішити дану проблему нам і знадобиться оператор ПОШУК, який ми вкладемо в функцію ПСТР.
- Виробляємо виділення першої комірки стовпчика, куди будуть виводитися дані, і вже звичним способом викликаємо вікно аргументів функції ПСТР.
В поле «Текст», як зазвичай, вказуємо перший осередок шпальти з вихідними даними. Тут все без змін.
- А ось значення поля «Початкова позиція» буде задавати аргумент, який формує функція ПОШУК. Як бачимо, всі дані в списку об'єднує той факт, що перед назвою моделі варто пробіл. Тому оператор ПОШУК буде шукати перший пробіл в осередку вихідного діапазону і повідомляти номер цього символу функції ПСТР.
Для того, щоб відкрити вікно аргументів оператора ПОШУК, встановлюємо курсор в поле «Початкова позиція». Далі натискаємо на піктограму у вигляді трикутника, спрямованого кутом вниз. Дана піктограма розташована на тому ж горизонтальному рівні вікна, де знаходиться кнопка «Вставити функцію» і рядок формул, але зліва від них. Відкривається список останніх застосовуваних операторів. Так як серед них немає найменування «ПОШУК», то натискаємо по пункту «Інші функції ...».
- Відкривається вікно Майстра функцій. У категорії «Текстові» виділяємо найменування «ПОШУК» і тиснемо на кнопку «OK».
- Запускається вікно аргументів оператора ПОШУК. Так як ми шукаємо пробіл, то в поле «Бажаємий текст» ставимо пробіл, встановивши туди курсор і натиснувши відповідну клавішу на клавіатурі.
В поле «Текст для пошуку» вказуємо посилання на першу комірку колонки з вихідними даними. Це посилання буде тотожна тій, яку ми раніше вказали в полі «Текст» у вікні аргументів оператора ПСТР.
Аргумент поля «Початкова позиція» не обов'язковий до заповнення. У нашому випадку його заповнювати не потрібно або можна встановити число «1». При будь-якому з цих варіантів пошук буде здійснюватися з початку тексту.
Після того, як дані введені, що не поспішаємо тиснути на кнопку «OK», так як функція ПОШУК є вкладеною. Просто натискаємо по найменуванню ПСТР в рядку формул.
- Після виконання останнього зазначеного дії ми автоматично повертаємося до вікна аргументів оператора ПСТР. Як бачимо, поле «Початкова позиція» вже заповнене формулою ПОШУК. Але дана формула вказує на пропуск, а нам потрібен наступний символ після пробілу, з якого і починається найменування моделі. Тому до існуючих даних в поле «Початкова позиція» дописуємо вираз «+1» без лапок.
В поле «Кількість знаків», як і в попередньому прикладі, записуємо будь-яке число, яке більше або дорівнює кількості символів в найдовшому вираженні вихідного стовпця. Наприклад, ставимо число «50». У нашому випадку цього цілком достатньо.
Після виконання всіх зазначених маніпуляцій тиснемо на кнопку «OK» в нижній частині вікна.
- Як бачимо, після цього найменування моделі пристрою було виведено в окрему клітинку.
- Тепер за допомогою Майстра заповнення, як і в попередньому способі, копіюємо формулу на осередки, які розташовані нижче в даному стовпці.
- Найменування всіх моделей пристроїв виведені в цільові осередки. Тепер, у разі необхідності, можна обірвати зв'язок в цих елементах зі стовпцем вихідних даних, як і в попередній раз, застосувавши послідовно копіювання і вставку значень. Втім, вказане дія не завжди є обов'язковим.
Функція ШУКАТИ використовується в поєднанні з формулою ПСТР за тим же принципом, що і оператор ПОШУК.
Як бачимо, функція ПСТР є дуже зручним інструментом для виведення потрібних даних в заздалегідь зазначену осередок. Те, що вона не так сильно популярна серед користувачів, пояснюється тим фактом, що багато користувачів, використовуючи Excel, більшу увагу приділяють математичних функцій, а не текстовим. При використанні цієї формули в поєднанні з іншими операторами функціональність її ще більше збільшується.