MS Excel. Приемы работы с данными

MS Excel. Приемы работы с данными
Юрий Николаевич Лубягин

В книге собраны приемы работы с тестовой строкой и массивами данных. Даны способы анализа массивов. Приведён пример практической реализации создания интерактивной системы автоматических расчетов. Для изучения материала необходимы базовые навыки работы в MS Excel.

Юрий Лубягин

MS Excel. Приемы работы с данными

Всегда есть способ любую работу делать быстрее и качественнее, чем вы ее делаете сейчас.

Введение

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

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

В книге читатель ознакомиться с приемами и алгоритмами работы с массивами данных, используя табличный процессор MS Excel наработанные в течение 10 лет практической работы.

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

Во второй главе, рассматривается работа с массивами данных, модификация и их анализ.

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

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

Глава 1. Приемы работы с текстовой строкой

Получение перового слова с начала текстовой строки

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

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

Синтаксис функции.

НАЙТИ(искомый_текст, просматриваемый_текст, [нач_позиция])

Аргументы функции.

Искомый_текст – обязательный аргумент. Текст, который необходимо найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти искомый текст.

Нач_позиция – необязательный аргумент. Знак, с которого нужно начать поиск. Первый знак в тексте "просматриваемый_текст" имеет номер 1. Если номер опущен, он полагается равным 1.

Рассмотрим сроку «Фамилия Имя Отчество» видим, что после фамилии стоит пробел. Пробел это тоже знак, хотя мы его и не видим.

Алгоритм действий – для получения фамилии из строки с ФИО необходимо вернуть количество символов до первого пробела.

Используем функцию «ЛЕВСИМВ»

Синтаксис функции.

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

Аргументы функции.

Текст  Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.

Число_знаков  Необязательный. Количество символов, извлекаемых функцией. "Число_знаков" должно быть больше нуля или равно ему. Если значение "число_знаков" опущено, оно считается равным 1.

Полезно знать. Входной переменной аргумента любой функции может быть и другая функция.

В нашем примере число знаков, которые необходимо вернуть мы получим с помощью функции «НАЙТИ».

Условимся, текстовая строка, с которой мы работаем, всегда находится в ячейке A1, а формулы последовательно вводим в следующие по порядку ячейки в сроке. В ячейку B1 вводим формулу «=НАЙТИ(" ";A1;1)». Чтобы указать адрес ячейки в формуле, достаточно только при вводе нажать курсором на необходимую ячейку. Тут функция «НАИТИ» возвращает позицию пробела в тексте находящимся в ячейке A1, поиск идет с первого символа. Результатом функции будет число – позиция первого пробела с начала строки, которое мы используем в качестве аргумента число знаков функции ЛЕВСИМВ. В ячейку C1 вводим формулу «=ЛЕВСИМВ(A1;B1-1))», тут функция «ЛЕВСИМВ» возвращает количество знаков до первого пробела из текста, находящегося в ячейке A1. Почему мы отнимаем один от результата функции «НАЙТИ», она вернула положение пробела, и если этого не сделать, то функция ЛЕВСИМВ вернет слово с пробелом на конце.

Можно формулы всех ячеек записать в одну, для этого в формуле «=ЛЕВСИМВ(A1;B1-1))» вместо адреса ячейки B1 водим формулу из этой ячейки, только без знака «=», и получим следящую формулу «=ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)», – результатом будет первое слово до пробела из строки ФИО.

Получение любого слова из текстовой строки

Получив фамилию в предыдущем примере, логично получить и инициалы.

Чтобы получить инициалы необходимо отдельно, имя и отчество, и используя функцию «ЛЕВСИМВ», где в качестве аргумента Число_знаков используем 1 или опускаем данный аргумент, получим первый символы имени и отчества.

Как получить имя из текстовой строки «Фамилия Имя Отчество»?

В строке «Фамилия Имя Отчество» слово имя стоит после пробела и после имени стоит пробел.

Алгоритм действий: для получения имени, необходимо вернуть из строки с ФИО количество символов от первого пробела до второго пробела.

Внимание! В стоке не должно быть между словами лишних пробелов. Для этого можно использовать функцию «СЖПРОБЕЛЫ» она удаляет из текста все пробелы, за исключением одиночных между словами и имеет только один аргумент – текст. Если вы сомневаетесь в количестве пробелов в используемом вами тексте, то необходимо воспользоваться данной функцией.

Для реализации вышеописанного алгоритма используем еще одну текстовую функцию – «ПСТР».

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

Синтаксис функции.

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

Аргументы функции.

Текст  Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция  Обязательный. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Число_знаков  Обязательный. Указывает, сколько знаков должна вернуть функция «ПСТР».