/ Статья
Работа с формулами в таблицах МойОфис
17 декабря 2020
Электронные книги – важный и очень мощный инструмент для анализа и обработки данных. Чтобы работа с ними была максимально эффективной, важно уметь правильно применять формулы и функции, которые помогают выполнять расчеты.
В этой статье мы расскажем, как пользоваться формулами и функциями в «МойОфис Таблица».
Формула в «МойОфис Таблица» всегда начинается с символа «=» (равно) и может быть введена как сразу в ячейке таблицы, так и в строке формул. Формула может содержать внутри себя функцию. Функция – это предустановленная формула «МойОфис Таблица». Список функций можно найти на дополнительной панели справа. Чтобы функция корректно работала, необходимо ознакомиться с её атрибутами и четко следовать правилам, описанным в подсказке.
Рассмотрим, как с помощью формул решить задачу, часто встречающуюся при работе с внешними системами и базами данных.
Дано: после обработки данных из внешней системы сбора информации мы получили таблицу с несколькими столбцами. Ячейки в столбце B содержат текстовую и числовую информацию (рис. 1). Как перенести числовые данные в отдельную ячейку, чтобы в дальнейшем можно было выполнить расчеты с ними?
Рисунок 1. Данные из внешней системы
В ячейке B2 мы видим количество учащихся: 106. Именно это число необходимо перенести в отдельную ячейку для дальнейших расчетов. То есть нам потребуется извлечь числовое значение, находящееся в строке «Количество учащихся».
Очевидно, что данные внутри ячейки – текстовые, поэтому и операции будут выполняться с текстовыми сегментами.
Для решения задачи используем простую функцию:
ПСТР(строка; начало; длина_извлеченного_сегмента) – возвращает фрагмент текстовой строки.
У функции следующие атрибуты:
строка: текстовая строка, содержащая фрагмент, который требуется извлечь;
начало: символ в текстовой строке, с которого начинается извлекаемый фрагмент;
длина_извлеченного_сегмента: длина фрагмента, который необходимо извлечь.
Запомнить функцию очень просто: Получение СТРоки = ПСТР.
Другими словами, эта функция скопирует в другую ячейку часть текста, который состоит из заданного количества символов.
Чтобы функция начала работать, пользователь должен указать, с какого момента необходимо извлекать сегмент, т.е. дать точные координаты, в нашем случае – с какого порядкового номера символа начинать работать (атрибут «начало»).
В ячейке В2 находится текст:
Число «106» находится на 280 позиции по количеству символов до первой цифры.
Введем в ячейку формулу:
где:
В2 – адрес ячейки, из которой извлекаем текст;
280 – координата «начало», с которого извлекаем текст;
3 – количество извлекаемых знаков.
Получим результат:
Рисунок 2. Результат работы формулы ПСТР
Это простая реализация, но она заняла достаточно много времени: пришлось вручную определять координаты начала извлечения. В случае с изменяющимися значениями и наименованиями учреждений, для работы с большими данными такая формула не подойдет: придется каждый раз «угадывать» координаты.
Чтобы решить задачу с автоматизированным поиском начала извлечения, воспользуемся второй функцией – ПОИСК.
ПОИСК(искомый_текст; текст_для_поиска;[начало]) – возвращает начальную позицию искомой строки в тексте.
С помощью функции ПОИСК мы получим начальную позицию того символа (или строки), из которой функция ПСТР будет извлекать текст.
У функции следующие атрибуты:
искомый_текст: строка, которую необходимо найти в аргументе «текст_для_поиска»;
текст_для_поиска: текст, в котором необходимо осуществить поиск;
[начало]: необязательный Символ в аргументе «текст_для_поиска», с которого необходимо начать поиск. Если аргумент опущен, по умолчанию используется 1.
Попробуем для начала извлечь строку с данными целиком: «Количество учащихся: 106».
Для этого с помощью функции ПОИСК определим начальную позицию, с которой стартует фрагмент «Количество...».
Введем в ячейку:
где:
«Количество» – искомый текстовый фрагмент,
В2 – адрес ячейки, в котором ищется фрагмент.
Получен результат: 259.
Именно с этой позиции должна стартовать функция извлечения.
Рисунок 3. Результат работы функции ПОИСК
Объединим работу двух описанных функций:
где:
B2 – ячейка, в которой ищется и извлекается текст;
ПОИСК(«Количество»; B2) – атрибут функции, определяющий начальное положение извлекаемого текста;
25 – количество символов в фразе «Количество учащихся: 106».
В ячейке появилась фраза, которая не будет изменяться и всегда имеет точное позиционирование, т.е. число всегда будет находиться на 22 позиции в строке.
Мы уже почти приблизились к решению исходной задачи!
Доработаем формулу и допишем в синтаксис извлечение из фразы «Количество учащихся: 106» трех символов, начиная с 22 позиции.
Используем уже знакомую функцию ПСТР:
где:
С2 – адрес ячейки с фразой «Количество учащихся: 106»;
22 – позиция начала извлекаемых символов;
3 – количество выводимых символов.
Рисунок 4. Извлечение числа из ячейки С2
Объединим все фрагменты функций в одну большую формулу, которая извлекает 3 символа из строки с начальным позиционированием от 22 символа:
где:
ПСТР(B2; ПОИСК(«Количество»; B2); 25) – это строка, из которой извлекается текст.
Чтобы его указать, используется поиск по ключевому слову «Количество», из которого извлекается 25 символов из ячейки В2;
22 – атрибут «начало», т.е. координата (начало) извлекаемого фрагмента;
3 – количество извлекаемых символов (длина_извлеченного_сегмента).
Формула работает, расчеты получаются!
Но что будет, если в учреждении учеников больше – тысяча, две тысячи?
Для этого необходимо доработать формулу – указать длину извлекаемого сегмента, которая будет состоять из четырех символов, а не из трех. Если длина извлеченного сегмента станет 4, при работе функции на экране может отобразиться максимальное количество 9999. Если значений меньше, то будет извлечено столько символов, сколько есть в строке (см. рис. 5)
Рисунок 5. Функция ПСТР с настроенными параметрами «вывести 4 символа»
Скопируйте функцию по всем строкам, и задача подсчета общего количества учеников будет решена!
Теперь вы умеете переносить данные в отдельную ячейку автоматически – с помощью функций и формул редактора «МойОфис Таблица».
Скачать файл примера можно по ссылке.
В этой статье мы расскажем, как пользоваться формулами и функциями в «МойОфис Таблица».
Формула в «МойОфис Таблица» всегда начинается с символа «=» (равно) и может быть введена как сразу в ячейке таблицы, так и в строке формул. Формула может содержать внутри себя функцию. Функция – это предустановленная формула «МойОфис Таблица». Список функций можно найти на дополнительной панели справа. Чтобы функция корректно работала, необходимо ознакомиться с её атрибутами и четко следовать правилам, описанным в подсказке.
Рассмотрим, как с помощью формул решить задачу, часто встречающуюся при работе с внешними системами и базами данных.
Дано: после обработки данных из внешней системы сбора информации мы получили таблицу с несколькими столбцами. Ячейки в столбце B содержат текстовую и числовую информацию (рис. 1). Как перенести числовые данные в отдельную ячейку, чтобы в дальнейшем можно было выполнить расчеты с ними?
Рисунок 1. Данные из внешней системы
В ячейке B2 мы видим количество учащихся: 106. Именно это число необходимо перенести в отдельную ячейку для дальнейших расчетов. То есть нам потребуется извлечь числовое значение, находящееся в строке «Количество учащихся».
Очевидно, что данные внутри ячейки – текстовые, поэтому и операции будут выполняться с текстовыми сегментами.
Для решения задачи используем простую функцию:
ПСТР(строка; начало; длина_извлеченного_сегмента) – возвращает фрагмент текстовой строки.
У функции следующие атрибуты:
строка: текстовая строка, содержащая фрагмент, который требуется извлечь;
начало: символ в текстовой строке, с которого начинается извлекаемый фрагмент;
длина_извлеченного_сегмента: длина фрагмента, который необходимо извлечь.
Запомнить функцию очень просто: Получение СТРоки = ПСТР.
Другими словами, эта функция скопирует в другую ячейку часть текста, который состоит из заданного количества символов.
Чтобы функция начала работать, пользователь должен указать, с какого момента необходимо извлекать сегмент, т.е. дать точные координаты, в нашем случае – с какого порядкового номера символа начинать работать (атрибут «начало»).
В ячейке В2 находится текст:
Число «106» находится на 280 позиции по количеству символов до первой цифры.
Введем в ячейку формулу:
| =ПСТР(B2; 280; 3) |
где:
В2 – адрес ячейки, из которой извлекаем текст;
280 – координата «начало», с которого извлекаем текст;
3 – количество извлекаемых знаков.
Получим результат:
Рисунок 2. Результат работы формулы ПСТР
Это простая реализация, но она заняла достаточно много времени: пришлось вручную определять координаты начала извлечения. В случае с изменяющимися значениями и наименованиями учреждений, для работы с большими данными такая формула не подойдет: придется каждый раз «угадывать» координаты.
Чтобы решить задачу с автоматизированным поиском начала извлечения, воспользуемся второй функцией – ПОИСК.
ПОИСК(искомый_текст; текст_для_поиска;[начало]) – возвращает начальную позицию искомой строки в тексте.
С помощью функции ПОИСК мы получим начальную позицию того символа (или строки), из которой функция ПСТР будет извлекать текст.
У функции следующие атрибуты:
искомый_текст: строка, которую необходимо найти в аргументе «текст_для_поиска»;
текст_для_поиска: текст, в котором необходимо осуществить поиск;
[начало]: необязательный Символ в аргументе «текст_для_поиска», с которого необходимо начать поиск. Если аргумент опущен, по умолчанию используется 1.
Попробуем для начала извлечь строку с данными целиком: «Количество учащихся: 106».
Для этого с помощью функции ПОИСК определим начальную позицию, с которой стартует фрагмент «Количество...».
Введем в ячейку:
|
=ПОИСК(«Количество»; B2) |
где:
«Количество» – искомый текстовый фрагмент,
В2 – адрес ячейки, в котором ищется фрагмент.
Получен результат: 259.
Именно с этой позиции должна стартовать функция извлечения.
Рисунок 3. Результат работы функции ПОИСК
Объединим работу двух описанных функций:
| =ПСТР(B2; ПОИСК(«Количество»; B2); 25) |
где:
B2 – ячейка, в которой ищется и извлекается текст;
ПОИСК(«Количество»; B2) – атрибут функции, определяющий начальное положение извлекаемого текста;
25 – количество символов в фразе «Количество учащихся: 106».
В ячейке появилась фраза, которая не будет изменяться и всегда имеет точное позиционирование, т.е. число всегда будет находиться на 22 позиции в строке.
Мы уже почти приблизились к решению исходной задачи!
Доработаем формулу и допишем в синтаксис извлечение из фразы «Количество учащихся: 106» трех символов, начиная с 22 позиции.
Используем уже знакомую функцию ПСТР:
| =ПСТР(C2; 22; 3) |
где:
С2 – адрес ячейки с фразой «Количество учащихся: 106»;
22 – позиция начала извлекаемых символов;
3 – количество выводимых символов.
Рисунок 4. Извлечение числа из ячейки С2
Объединим все фрагменты функций в одну большую формулу, которая извлекает 3 символа из строки с начальным позиционированием от 22 символа:
|
=ПСТР(ПСТР(B2; ПОИСК(«Количество»; B2); 25); 22; 3) |
где:
ПСТР(B2; ПОИСК(«Количество»; B2); 25) – это строка, из которой извлекается текст.
Чтобы его указать, используется поиск по ключевому слову «Количество», из которого извлекается 25 символов из ячейки В2;
22 – атрибут «начало», т.е. координата (начало) извлекаемого фрагмента;
3 – количество извлекаемых символов (длина_извлеченного_сегмента).
Формула работает, расчеты получаются!
Но что будет, если в учреждении учеников больше – тысяча, две тысячи?
Для этого необходимо доработать формулу – указать длину извлекаемого сегмента, которая будет состоять из четырех символов, а не из трех. Если длина извлеченного сегмента станет 4, при работе функции на экране может отобразиться максимальное количество 9999. Если значений меньше, то будет извлечено столько символов, сколько есть в строке (см. рис. 5)
Рисунок 5. Функция ПСТР с настроенными параметрами «вывести 4 символа»
Скопируйте функцию по всем строкам, и задача подсчета общего количества учеников будет решена!
Теперь вы умеете переносить данные в отдельную ячейку автоматически – с помощью функций и формул редактора «МойОфис Таблица».
Скачать файл примера можно по ссылке.
