Применение функции ПРАВСИМВ в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Применение функции ПРАВСИМВ в Microsoft Excel

Применение функции ПРАВСИМВ в Microsoft Excel

Среди различных функций в Экселе, предназначенных для работы с текстом, своими не совсем обычными возможностями выделяется оператор ПРАВСИМВ. Его задачей является извлечение из указанной ячейки заданного количества символов, отсчитывая с конца. Давайте более подробно узнаем о возможностях этого оператора и о нюансах использования его в практических целях на конкретных примерах.

Оператор ПРАВСИМВ

Функция ПРАВСИМВ извлекает из указанного элемента на листе то количество символов справа, которое укажет сам пользователь. Выводит конечный результат в ту ячейку, где располагается она сама. Данная функция относится к текстовой категории операторов Excel. Её синтаксис следующий:

Как видим, функция имеет всего два аргумента. Первый из них «Текст» может принимать вид, как собственно текстового выражения, так и ссылки на элемент листа, в котором оно располагается. В первом случае оператор будет извлекать указанное количество символов именно из текстового выражения, заданного в виде аргумента. Во втором случае функция будет «отщипывать» символы от текста, содержащегося в указанной ячейке.

Второй аргумент — «Число знаков» — представляет собой числовое значение, указывающее, какое именно количество символов в текстовом выражении, отсчитывая справа, нужно отобразить в целевой ячейке. Этот аргумент не является обязательным. Если его опустить, то считается, что он равен единице, то есть, в ячейку выводится только один самый крайний правый символ указанного элемента.

Пример применения

Теперь давайте рассмотрим применение функции ПРАВСИМВ на конкретном примере.

Для примера возьмем список сотрудников предприятия. В первой колонке этой таблицы находятся фамилии работников вместе с номерами телефонов. Нам нужно эти номера с помощью функции ПРАВСИМВ вынести в отдельный столбец, который так и называется «Номер телефона».

    Выделяем первую пустую ячейку столбца «Номер телефона». Щелкаем по пиктограмме «Вставить функцию», которая размещена слева от строки формул.

Происходит активация окна Мастера функций. Переходим в категорию «Текстовые». Из представленного списка наименований выделяем название «ПРАВСИМВ». Производим щелчок по кнопке «OK».

Открывается окно аргументов оператора ПРАВСИМВ. Оно содержит в себе два поля, которые соответствуют аргументам указанной функции. В поле «Текст» нужно указать ссылку на первую ячейку столбца «Имя», в котором содержится фамилия работника и номер телефона. Адрес можно указать вручную, но мы сделаем по-другому. Устанавливаем курсор в поле «Текст», а затем щелкаем левой кнопкой мыши по той ячейке, координаты которой следует внести. После этого адрес отображается в окошке аргументов.

В поле «Число знаков» вводим с клавиатуры цифру «5». Именно из пяти знаков состоит номер телефона каждого работника. К тому же, все номера телефонов расположены в конце ячеек. Поэтому, чтобы их вывести отдельно, нам нужно извлечь из этих ячеек именно по пять символов справа.

После того, как вышеуказанные данные введены, щелкаем по кнопке «OK».

После этого действия номер телефона указанного работника извлечен в предварительно выделенную ячейку. Конечно, вводить указанную формулу отдельно для каждого лица в списке очень долгое занятие, но можно сделать быстрее, а именно скопировать её. Для этого ставим курсор в нижний правый угол ячейки, в которой уже содержится формула ПРАВСИМВ. При этом курсор преобразуется в маркер заполнения в виде небольшого крестика. Зажимаем левую кнопку мыши и тянем курсор вниз до самого конца таблицы.

Теперь весь столбец «Номер телефона» заполнен соответствующими значениями из колонки «Имя».

Но, если мы попытаемся убрать номера телефонов из столбца «Имя», то они начнут исчезать и из колонки «Номер телефона». Это объясняется тем, что оба эти столбца связаны формулой. Для того, чтобы удалить эту связь, выделяем все содержимое столбца «Номер телефона». Затем кликаем по пиктограмме «Копировать», которая находится на ленте во вкладке «Главная» в группе инструментов «Буфер обмена». Можно также набрать сочетание клавиш Ctrl+C.

Далее, не снимая выделения с вышеуказанной колонки, кликаем по ней правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выбираем позицию «Значения».

  • После этого все данные в колонке «Номер телефона» будут представлены как самостоятельные символы, а не как результат вычислений формулы. Теперь, при желании, можно удалять номера телефонов из колонки «Имя». Это никак не повлияет на содержимое столбца «Номер телефона».
  • Как видим, возможности, которые предоставляет функция ПРАВСИМВ, имеют конкретную практическую пользу. При помощи данного оператора можно выводить в отмеченную область нужное количество символов из указанных ячеек, отсчитывая с конца, то есть, справа. Особенно полезен этот оператор будет в том случае, если нужно извлечь одинаковое число символов с конца в большом диапазоне ячеек. Использование формулы при подобных обстоятельствах позволит значительно сэкономить время пользователя.

    Текстовые функции Excel

    ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

    Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

    Функция ЛЕВСИМВ

    Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

    Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

    • текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
    • количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

    Пример использования:

    Формула: =ЛЕВСИМВ(“Произвольный текст”;8) – возвращенное значение «Произвол».

    Функция ПРАВСИМВ

    Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

    Пример использования:

    Формула: =ПРАВСИМВ(“произвольный текст”;5) – возвращенное значение «текст».

    Функция ДЛСТР

    С ее помощью определяется длина строки. В качестве результата возвращается целое число, указывающее количество символов текста.

    Синтаксис: =ДЛСТР(текст)

    Пример использования:

    Функция НАЙТИ

    Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

    Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

    • искомый_текст – строка, которую необходимо найти;
    • текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
    • нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.

    Пример использования:

    Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

    Функция ЗАМЕНИТЬ

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

    Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

    Пример использования:

    Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

    В случае обработки небольшого количества ячеек такой способ вполне приемлем. Но если обработать необходимо несколько тысяч различных строк, то процесс составления формул станет трудоемким. Поэтому переделаем рассмотренный пример, используя знания по тем функциям, которые уже описаны в начале статьи, а именно:

    • Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
    • В место аргумент «количество_знаков» вложим функцию «ДЛСТР».
    Читайте также:  Расчет коэффициента детерминации в Microsoft Excel

    В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(“старый”;A1);ДЛСТР(“старый”);”новый”)

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

    Функция ПОДСТАВИТЬ

    Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

    Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

    • текст – строка или ссылка на ячейку, содержащую текст;
    • старый_текст – подстрока из первого аргумента, которую необходимо заменить;
    • новый_текст – строка для подмены старого текста;
    • номер_вхождения – необязательный аргумент. Принимает целое число, указывающее порядковый номер вхождения старый_текст, которое подлежит замене, все остальные вхождения затронуты не будут. Если оставить аргумент пустым, то будут заменены все вхождения.

    Пример использования:

    Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

    Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

    Функция ПСТР

    ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

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

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

    Пример использования:

    Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

    Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

    Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

    Функция СЖПРОБЕЛЫ

    Данная функция удаляется все лишние пробелы: пробелы по краям и двойные пробелы между словами. После обработки строк функцией остаются только одиночные пробелы между словами.

    Синтаксис: =СЖПРОБЕЛЫ(текст)

    Пример использования:

    =СЖПРОБЕЛЫ( ” Текст с лишними пробелами между словами и по краям “ )

    Результатом выполнения функции будет строка: “Текст с лишними пробелами между словами и по краям” .

    Функция СЦЕПИТЬ

    С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

    Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

    Функция должна содержать не менее одного аргумента

    Пример использования:

    Функция возвратит строку: «Слово1 Слово2».

    Не забывайте, что функция самостоятельно не добавляет пробелы между строками, поэтому добавлять их приходится самостоятельно.

    Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=”Слово1″&” “&“Слово2″».

    Функция ПРАВСИМВ в Excel: формула и применение

    В программе Excel существует немало функций, назначение которых – работа с текстовыми данными. Среди них особенно выделяется оператор ПРАВСИМВ, который позволяет извлечь определенное число символов из заданной ячейки. При этом извлекаемые символы, согласно названию, отсчитываются с конца текста, т.е. с правой стороны.

    В этой статье мы рассмотрим, что из себя представляет данная функция, а также как она работает на конкретном примере.

    Описание функции ПРАВСИМВ

    Задача функции “ПРАВСИМВ” состоит в извлечении заданного количества символов из указанной пользователем ячейки. При этом отсчет символов начинается с правой стороны ячейки, т.е. с ее конца. Результат выводится в заранее выбранную ячейку, в которую вставляется соответствующий оператор или прописывается формула вручную.

    Данный оператор предназначен для работы с текстовыми данными и относится к категории “текстовые”. Сама формула функции выглядит следующим образом:

    =ПРАВСИМВ(текст;количество_знаков).

    Как вы можете заметить, в этом операторе присутствует два аргумента:

    1. Первый аргумент – “Текст”. Он отвечает за исходное значение, из которой будут извлекаться символы. Причем, это значение может быть как виде конкретного текста (в этом случае указанное количество символов будет извлекаться из этого самого текста), так и в виде координат ячейки, из которого будет происходить извлечение.
    2. Второй аргумент – “Число_знаков”. Он отвечает за то, сколько знаков требуется извлечь из исходного значения и выражается в цифровом эквиваленте. Этот аргумент не является обязательным в данной функции и его можно опустить. В случае, если аргумент не заполнен, в результирующей ячейке будет выведен только один самый последний символ справа из указанного текстового аргумента (как будто мы указали число знаков, равное цифре “1”).

    Использование функции ПРАВСИМВ на примере

    Для того, чтобы лучше понять принцип работы оператора, разберем его на конкретном примере.

    Возьмем таблицу, в которой представлены продажи спортивной обуви. В первом столбце указаны наименования с размерами. Наша цель — вынести эти размеры в отдельный столбец.

    Для осуществления данной задачи как раз прекрасно подойдет оператор ПРАВСИМВ.

    1. Первым делом давайте создаем столбец, куда будут выведены извлеченные данные. Назовем его “Размер”.
    2. Ставим курсор на первую ячейку столбца (после заголовка) и кликаем мышью по кнопке «Вставить функцию» (ее пиктограмма располагается слева от поля для ввода формул).
    3. Откроется окно Мастера функций, в котором мы раскрываем список категорий и выбираем вариант “Текстовые”.
    4. Из представленного перечня функций выбираем ПРАВСИМВ и подтверждаем действие нажатием кнопки OK.
    5. В результате перед нами появится окно с двумя полями, которые соответствуют аргументам функции “ПРАВСИМВ”:
      • В поле с названием «Текст» пишем адрес первой ячейки столбца “Наименование” (в нашем случае – A2), в которой прописаны наименования обуви. Чтобы не указывать координаты нужной ячейки вручную, кликаем курсором в области поля “Текст”, и далее – по нужной нам ячейки таблицы.
      • В поле “Число_знаков” указываем количество символов в размере. В нашем примере – это цифра 9, т.к. размеры указаны в конце ячейки и занимают 9 символов (“пробел” также считается символом). После заполнения нужных значений аргументов нажимаем OK.Примечание: формулу функции можно прописать и вручную, установив курсор в требуемой ячейке. В нашем случае она выглядит так: =ПРАВСИМВ(A2;9). После ее набора нажимаем клавишу Enter.
    6. В результате проделанных действий в выбранной ячейке, куда мы вставили функцию, будет выведен размер обуви.
    7. Теперь нужно применить функцию ко всем ячейкам в столбце “Размер”. Наводим курсор мыши на нижний правый угол ячейки с формулой (он должен преобразоваться в крестик). Зажав левую кнопку мыши двигаем курсор вниз. После того, как мы выделили весь нужный диапазон ячеек, отпускаем кнопку.
    8. В результате строки столбца “Размер” будет заполнены данными из столбца “Наименование” (последние 9 символов, как мы и указывали).
    9. Однако, если стереть данные по размерами из столбца “Наименование”, то они исчезнут и из колонки “Размер” (так как они связанны). Давайте уберем эту связь, чтобы ничто не мешало дальнейшей работе с таблицей. Для этого выделяем весь ячейки колонки “Размер” и нажимаем на кнопку “Копировать” на ленте.Также для копирования можно воспользоваться комбинацией клавиш Ctrl+C или контекстным меню, щелкнув правой кнопкой мыши по любой ячейке в отмеченном диапазоне.
    10. Теперь кликаем правой кнопкой мыши по первой ячейке ранее отмеченной области и выбираем из предложенных опций в пункте “Параметры вставки” вариант “Значения”.
    11. В итоге все данные в столбце “Размер” будут вставлены как самостоятельные значения, не связанные со столбцом “Наименование”, и при необходимости данные последнего можно редактировать или удалить на свое усмотрение.
    Читайте также:  Составление заголовка в Microsoft Excel

    Заключение

    Microsoft Excel предлагает широчайшие возможности в работе с числовыми и графическими данными. Но не менее сильной стороной программы являются инструменты, предназначение для обработки текстовых данных, которые позволят значительно сократить время на выполнение определенных операций и исключат ошибки в результате человеческого фактора при работе с большим объемом данных.

    БЛОГ

    Только качественные посты

    Практический справочник функций Microsoft Excel с примерами их использования

    На сегодняшний день программа Microsoft Excel является самой популярной программой в бизнесе, которая позволяет решать различные задачи — от анализа до учета данных. Самым популярным инструментом в Excel являются встроенные функции, количество которых приближается к 1000 штук.

    Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

    Могу с уверенностью, опираясь на свой 17 летний профессиональный опыт работы в Excel, сказать, что достаточно освоить всего около 100 функций…

    Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

    – изучив данные Excel функции, у Вас будет достаточно теоретических знаний, чтобы решать практически любую задачу в Excel

    ( Для перехода к примерам нажмите на название функции. Все примеры — это ссылки на лучшие статьи уважаемых специалистов по Excel и наших партнеров)

    1. СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN) — [Базовые формулы Excel]
    2. ВПР (VLOOKUP) — [Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце]
    3. ИНДЕКС (INDEX) — [По индексу получает значение из ссылки или массива]
    4. ПОИСКПОЗ (MATCH) — [Ищет значения в ссылке или массиве]
    5. СУММПРОИЗВ (SUMPRODUCT) — [Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)]
    6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS) — [Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров]
    7. ЕСЛИ (IF) — [Выполняет проверку условия]
    8. И / ИЛИ / НЕ (AND / OR / NOT) — [Логические условия, как правило для функции ЕСЛИ]
    9. ЕСЛИОШИБКА (IFERROR) — [Если формула возвращает ошибку то что]
    10. СУММЕСЛИМН (SUMIFS) — [Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия]
    11. СРЗНАЧЕСЛИМН (AVERAGEIFS) — [Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям]
    12. СЧЁТЕСЛИМН (COUNTIFS) — [Подсчитывает количество ячеек, которые соответствуют нескольким условиям]
    13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS) — [Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям]
    14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL) — [Возвращает k-ое наибольшее/наименьшее значение в множестве данных]
    15. ДВССЫЛ (INDIRECT) — [Определяет ссылку, заданную текстовым значением]
    16. ВЫБОР (CHOOSE) — [Выбирает значение из списка значений по индексу]
    17. ПРОСМОТР (LOOKUP) — [Ищет значения в массиве]
    18. СМЕЩ (OFFSET) — [Определяет смещение ссылки относительно заданной ссылки]
    19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN) — [Возвращает номер строки/столбца, на который указывает ссылка]
    20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS) — [Возвращает количество столбцов/строк в ссылке]
    21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP) — [Округляет число до указанного количества десятичных разрядов]
    22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK) — [Возвращает случайное число]
    23. Ч (N) — [Возвращает значение, преобразованное в число]
    24. ЧАСТОТА (FREQUENCY) — [Находит распределение частот в виде вертикального массива]
    25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &) — [Объединения двух или нескольких текстовых строк в одну]
    26. ПСТР (MID) — [Выдает определенное число знаков из строки текста, начиная с указанной позиции]
    27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT) — [Возвращает заданное количество символов текстовой строки слева / права]
    28. ДЛСТР (LEN) — [Определяет количество знаков в текстовой строке]
    29. НАЙТИ / ПОИСК (FIND / SEARCH) — [Поиск текста в ячейке с учетом / без учета регистр]
    30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE) — [Заменяет в текстовой строке старый текст новым]
    31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER) — [Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную]
    32. ГИПЕРССЫЛКА (HYPERLINK) — [Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете]
    33. СЖПРОБЕЛЫ (TRIM) — [Удаляет из текста все пробелы, за исключением одиночных пробелов между словами]
    34. ПЕЧСИМВ (CLEAN) — [Удаляет все непечатаемые знаки из текста]
    35. СОВПАД (EXACT) — [Проверяет идентичность двух текстов]
    36. СИМВОЛ / ПОВТОР (CHAR / REPT) — [Возвращает знак с заданным кодом/Повторяет текст заданное число раз]
    37. СЕГОДНЯ / ТДАТА (TODAY / NOW) — [Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате]
    38. МЕСЯЦ / ГОД (MONTH / YEAR) — [Вычисляет год / месяц от заданной даты]
    39. НОМНЕДЕЛИ (WEEKNUM) — [Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата]
    40. ДАТАЗНАЧ (DATEVALUE) — [Преобразует дату из текстового формата в числовой]
    41. РАЗНДАТ (DATEDIF) — [Вычисляет количество дней, месяцев или лет между двумя датами]
    42. РАБДЕНЬ (WORKDAY) — [Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней]
    43. ЯЧЕЙКА (CELL) — [Возвращает сведения о формате, расположении или содержимом ячейки]
    44. ТРАНСП (TRANSPOSE) — [Выдает транспонированный массив]
    45. ПРЕОБР (CONVERT) — [Преобразует число из одной системы мер в другую]
    46. ПРЕДСКАЗ (FORECAST) — [Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом]
    47. ТИП.ОШИБКИ (ERROR.TYPE) — [Возвращает числовой код, соответствующий типу ошибки]
    48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) — [Возвращает данные, хранящиеся в сводной таблице]
    49. БДСУММ (DSUM) — [Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям]
    50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel.

    После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

    В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

    • Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.
    • Power Pivot — это технология моделирования данных, которая позволяет создавать аналитические модели данных, устанавливать отношения и добавлять аналитические вычисления.
    • Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие визуализировать различную информацию.

    Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

    Примеры использования функций ПРАВСИМВ и ЛЕВСИМВ в Excel

    Функция ПРАВСИМВ предназначена для вычисления количества символов в указанной ячейке. Количество считается справа на лево. Функция относится к текстовой категории.

    Как использовать функцию ПРАВСИМВ в Excel?

    Функция ПРАВСИМВ (на английском RIGHT) является кладезем для всех сотрудников, которые работают с массивами и выборками текстовых данных.

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

    1. Текст – исходная ячейка с данными.
    2. Количество знаков – длина вырезанного фрагмента текста с правой стороны.

    Рассмотрим на примере работу функции ПРАВСИМВ в Excel. Главный бухгалтер выгрузил из базы 1С Оборотно-сальдовую ведомость, представленную ниже:

    Задача: для отчета необходимо из столбца А с помощью функции ПРАВСИМВ вытащить только номера договоров.

    1. В столбец H вынесем номера договоров. Щелкнем в ячейку Н3 и нажмем кнопку мастер функций fx или комбинацию горячих клавиш SHIFT+F3. В окне мастера функций выберем категорию Текстовые, найдем функцию ПРАВСИМВ и нажмем кнопку ОК.
    2. На следующем шаге откроется окно аргументов функции ПРАВСИМВ. В поле текст укажем ячейку А3 с которой надо начинать действие, в поле количество знаков укажем 14, именно из такого количества состоит номер договора. Нажмем кнопку ОК.
    3. В результате в ячейке Н3 появится «№2016/07-29/1» без «Договор субподряда». Для того чтобы формула применилась к остальным ячейкам протянем ее за уголочек в низ.
    Читайте также:  Функция LOG в Microsoft Excel

    В результате весь столбик заполнится соответствующими номерами договоров. Таким образом, рассмотрев на практике работу функции ПРАВСИМВ можно сделать вывод, что она дает практическую пользу при работе с данными сотрудникам разных специальностей.

    Пример решения задачи для отделения текста с функцией ПРАВСИМВ

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

    Задача: руководитель, просмотрев сведения решил выделить эту информацию в отдельный столбец.

    1. В столбец В2 внесем заголовок «наличие автомобиля». Щелкнем в ячейку В3 и нажмем кнопку мастер функций или клавиши SHIFT+F3. В разделе категория выберем Текстовые, найдем функцию ПРАВСИМВ и нажмем кнопку ОК.
    2. В окне атрибутов функции ПРАВСИМВ в поле текст введем адрес ячейки, с которой необходимо начать действие, а поле количество знаков оставим пустым. Так как в функции не указан атрибут количество извлекаемых символов, то функция по умолчанию извлекает один символ справа.
    3. В результате работы функции столбец «Наличие автомобиля» будет содержать знак «+», в первой ячейке В3.
    4. Протянем за уголок ячейку В3 чтобы результат появился во всех ячейках.

    Пример задачи для функций ПРАВСИМВ и ЛЕВСИМВ

    Кадровый работник организации ведет таблицу принятых на работу сотрудников. Таблица имеет следующий вид:

    Задача: руководителю необходимо предоставить список сотрудников, которые приняты на работу, с разграничением принят и даты приема.

    1. Щелкнем в ячейку D2 и нажмем мастер функций (SHIFT+F3). В разделе категория выберем Текстовые и найдем функцию ЛЕВСИМВ (на английском LEFT):
    2. В окне атрибутов функции в поле текст укажем ссылку на ячейку C2, в поле количество знаков 6.
    3. В результате функция извлечет 6 символов из текстовой строки в ячейке C2 и возвратит необходимое нам слово «Принят».
    4. Проделаем те же операции с ячейкой Е2. Только вместо функции ЛЕВСИМВ используем ПРАВСИМВ. А во втором аргументе укажем числовое значение 10 как показано на рисунке ниже:
    5. В результате в ячейке E2 появится дата приема на работу.

    Таким образом, рассмотрев разные варианты использования атрибутов для функции ПРАВСИМВ и ЛЕВСИМВ, можно сделать вывод, о том, что можно отделять необходимые значения текста вырезая его из исходной строки с правой и левой стороны. Для подобного рода решения задач в Excel предусмотрена еще одна текстовая функция ПСТР, которая позволяет вырезать текст з середины строки. Применяя эти функции в формулах можно решать более сложные, например, разделение текстовых строк на слова и т.п.

    Текстовые функции Excel в примерах

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

    СЦЕПИТЬ

    Для объединения содержимого ячеек в Excel, наряду с оператором конкатенации, можно использовать текстовую функцию СЦЕПИТЬ. Она последовательно объединяет значения указанных ячеек в одной строке.

    СТРОЧН

    Если в Excel необходимо сделать все буквы строчными, т.е. преобразовать их в нижний регистр, на помощь придет текстовая функция СТРОЧН. Она не заменяет знаки, не являющиеся буквами.

    ПРОПИСН

    Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр. Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.

    ПРОПНАЧ

    Текстовая функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные преобразует в строчные.

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

    ДЛСТР

    В Excel Вы можете подсчитать количество знаков, содержащихся в текстовой строке, для этого воспользуйтесь функцией ДЛСТР. Пробелы учитываются.

    ЛЕВСИМВ и ПРАВСИМВ

    Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.

    Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.

    СОВПАД

    Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

    Если регистр для Вас не играет большой роли (так бывает в большинстве случаев), то можно применить формулу, просто проверяющую равенство двух ячеек.

    СЖПРОБЕЛЫ

    Удаляет из текста все лишние пробелы, кроме одиночных между словами.

    В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.

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

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

    ПОВТОР

    Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.

    НАЙТИ

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

    Данная функция чувствительна к регистру…

    … и может начинать просмотр текста с указанной позиции. На рисунке ниже формула начинает просмотр с четвертого символа, т.е. c буквы “r“. Но даже в этом случае положение символа считается относительно начала просматриваемого текста.

    ПОИСК

    Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.

    ПОДСТАВИТЬ

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

    Приведенная ниже формула заменяет все вхождения слова “Excel” на “Word”:

    Заменяет только первое вхождение слова “Excel”:

    Удаляет все пробелы из текстовой строки:

    ЗАМЕНИТЬ

    Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

    Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение “2013”. Применительно к нашему примеру, формула заменит “2010” на “2013”.

    Заменяет первые пять символов текстовой строки, т.е. слово “Excel”, на “Word”.

    Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!

    Ссылка на основную публикацию
    Adblock
    detector