Застосування функції ПСТР в Microsoft Excel

Зміст

• Використання ПСТР
  • Приклад 1: одиничний Витяг
  • Приклад 2: Груповий Витяг
  • Приклад 3: використання комбінації операторів
• Питання та відповіді

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

Використання ПСТР

Основне завдання оператора ПСТР полягає у витяганні із зазначеного елемента листа певного числа друкованих знаків, включаючи прогалини, починаючи з зазначеного за рахунком зліва символу. Ця функція відноситься до категорії текстових операторів. Її синтаксис приймає наступний вигляд:

=ПСТР (текст; початкова_позиція;кількість_знаків)

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

Аргумент " Текст» містить адресу того елемента листа, в якому знаходиться текстовий вираз з витягуються знаками.

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

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

Приклад 1: одиничний Витяг

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

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

1. Виділяємо елемент листа, в який буде проводитися Витяг. Клацаємо по кнопці " Вставити функцію» , яка розташована біля рядка формул.

2. Запускається віконце майстри функцій . Переходимо в категорію »текстові" . Виділяємо там найменування «ПСТР» і клацаємо по кнопці «OK» .

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

   У полі " Текст» вводимо координати осередку, яка містить ПІБ працівників. Щоб не забивати адресу вручну, просто встановлюємо курсор в поле і натискаємо лівою кнопкою миші по елементу на аркуші, в якому містяться потрібні нам дані.

   У полі " Початкова позиція» потрібно вказати номер символу, вважаючи зліва, з якого починається прізвище працівника. При підрахунку враховуємо також прогалини. Буква «Н» , з якої починається прізвище співробітника Миколаєва, є п'ятнадцятим за рахунком символом. Тому в поле ставимо число «15» .

   У полі " кількість знаків» потрібно вказати кількість символів, з яких складається прізвище. Вона складається з восьми знаків. Але враховуючи, що після Прізвища в осередку немає більше символів, ми можемо вказати і більшу кількість знаків. Тобто, в нашому випадку можна поставити будь-яке число, яке дорівнює або більше восьми. Ставимо, наприклад, число «10» . Але якби після Прізвища в осередку були б ще слова, цифри або інші символи, то нам би довелося встановлювати тільки точне число знаків ( «8» ).

   Після того, як всі дані введені, тиснемо на кнопку «OK» .


4. Як бачимо, після цієї дії прізвище працівника було виведено в зазначену нами в першому кроці прикладу 1 клітинку.

Урок: Майстер функцій в Ексель

Приклад 2: Груповий Витяг

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

Маємо список смартфонів. Перед найменуванням кожної моделі стоїть слово " Смартфон» . Нам потрібно винести в окремий стовпець тільки назви моделей без цього слова.

1. Виділяємо перший порожній елемент стовпця, в який буде виводитися результат, і викликаємо вікно аргументів оператора ПСТР тим же способом, що і в попередньому прикладі.

   У полі " Текст» вказуємо адресу першого елемента колонки з вихідними даними.

   У полі " Початкова позиція» нам потрібно вказати номер символу, починаючи з якого будуть витягуватися дані. У нашому випадку в кожному осередку перед найменуванням моделі стоїть слово «Смартфон» і пробіл. Таким чином, та фраза, яку потрібно вивести в окрему клітинку скрізь починається з десятого символу. Встановлюємо число «10» в дане поле.

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

   Після того, як дані введені, тиснемо на кнопку «OK» .


2. Після цього найменування першої моделі смартфона виводиться в заздалегідь зазначену комірку таблиці.

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

4. Як бачимо, вся колонка після цього буде заповнена потрібними нам даними. Секрет полягає в тому, що аргумент " Текст» являє собою відносну посилання і в міру зміни положення цільових осередків теж змінюється.

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

   Щоб «відв'язати» результат від початкової колонки, виробляємо наступні маніпуляції. Виділяємо стовпець, який містить формулу. Далі переходимо у вкладку»Головна" і тиснемо на піктограму «копіювати» , розташовану в блоці " Буфер обміну» на стрічці.

   

   Як альтернативна дія, можна після виділення натиснути комбінацію клавіш Ctrl+C .

6. Далі, не знімаючи виділення, клацаємо по колонці правою кнопкою миші. Відкривається контекстне меню. У блоці «Параметри вставки» клацаємо по піктограмі " Значення» .

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

Приклад 3: використання комбінації операторів

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

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

Синтаксис функції пошук наступний:

=пошук (шуканий_текст; текст_для_пошуку; початкова_позиція)

Синтаксис оператора знайти виглядає таким чином:

=знайти (шуканий_текст;проглядаємий_текст; нач_позиція)

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

Давайте подивимося, як використовувати оператор пошук у поєднанні з функцією ПСТР . Маємо таблицю, в яку занесені найменування різних моделей комп'ютерної техніки з узагальнюючою назвою. Як і минулого разу, нам потрібно витягти найменування моделей без узагальнюючої назви. Труднощі полягає в тому, що якщо в попередньому прикладі узагальнююче найменування для всіх позицій було одне і те ж («смартфон»), то в цьому списку воно різне («комп'ютер», «монітор», «колонки» і т.д.) з різним числом символів. Щоб вирішити дану проблему нам і знадобиться оператор пошук , який ми вкладемо у функцію ПСТР .

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

   У полі " Текст» , як зазвичай, вказуємо першу клітинку стовпця з вихідними даними. Тут все без змін.


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

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


3. Відкривається вікно майстри функцій . В категорії »текстові" виділяємо найменування »пошук" і тиснемо на кнопку «OK» .

4. Запускається вікно аргументів оператора пошук . Так як ми шукаємо пробіл, то в полі «шуканий текст» ставимо пробіл, встановивши туди курсор і натиснувши відповідну клавішу на клавіатурі.

   У полі " Текст для пошуку» вказуємо посилання на першу клітинку колонки з вихідними даними. Це посилання буде тотожна тієї, яку ми раніше вказали в поле " Текст» у вікні аргументів оператора ПСТР .

   Аргумент поля " Початкова позиція» не обов'язковий до заповнення. У нашому випадку його заповнювати не потрібно або можна встановити число «1» . При будь-якому з цих варіантів пошук буде здійснюватися з початку тексту.

   Після того, як дані введені, не поспішаємо тиснути на кнопку «OK» , оскільки функція пошук є вкладеною. Просто натискаємо по найменуванню ПСТР у рядку формул.


5. Після виконання останньої вказаної дії ми автоматично повертаємося до вікна аргументів оператора ПСТР . Як бачимо, поле " Початкова позиція» вже заповнено формулою пошук . Але дана формула вказує на пробіл, а нам потрібен наступний символ після пробілу, з якого і починається найменування моделі. Тому до існуючих даних у полі " Початкова позиція» дописуємо вираз «+1» без лапок.

   У полі " кількість знаків» , як і в попередньому прикладі, записуємо будь-яке число, яке більше або дорівнює кількості символів в найдовшому виразі вихідного стовпця. Наприклад, ставимо число «50» . У нашому випадку цього цілком достатньо.

   Після виконання всіх зазначених маніпуляцій тиснемо на кнопку «OK» у нижній частині вікна.


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

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

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

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

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