Юрий Николаевич Лубягин
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 и так далее.
Число_знаков Обязательный. Указывает, сколько знаков должна вернуть функция «ПСТР».
Переменную аргумента «начальная позиция» получаем, функцией «НАЙТИ», в ячейке B1 вводим формулу «=НАЙТИ(" ";A1;1)+1» находим начальную позицию слова «Имя». Для получения переменой аргумента «число_знаков» необходимо от позиции второго пробела отнять начальную позицию слова «Имя». Для получения позиции второго пробела вспомним о необязательном аргументе функции «НАЙТИ» «Нач_позиция» позиция, с которой нужно начать поиск, в нашем примере поиск мы должны начать с позиции начала слова «Имя», иначе функция нам вернет позицию первого пробела.
В ячейку C1 вводим формулу «=НАЙТИ(" ";A1;B1)» получаем позицию второго пробела.
Для получения слова «Имя» в ячейку D1 вводим формулу «=ПСТР(A1;B1;C1-B1)».
Запишем все формулы в виде одной.
Выполним последовательно следующие действия:
1. В ячейке С1 формулу «=НАЙТИ(" ";A1;B1)» меняем на «=НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)»;
2. В ячейке D1 формулу «=ПСТР(A1;B1;C1-B1)» меняем на «=ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-(НАЙТИ(" ";A1;1)+1))». Для корректности расчетов формулу из ячейки B1 необходимо взять в скобки.
Но можно и не усложнять формулы так сильно, а скрывать столбцы с промежуточными расчетами.
Получив слово «Имя» и используя функцию «ЛЕВСИМВ» вводим в ячейку E1 формулу – «=ЛЕВСИМВ(D1)» и получим первую букву имени.
Для добавления точки в конце инициала, используем функцию «СЦЕПИТЬ» Данная функция объединяет до 255 текстовых строк в одну. Объединяемые элементы могут быть текстом, числами, ссылками на ячейки или сочетанием этих элементов. Аргументами функции являются минимум две тактовые строки, которые необходимо сцепить. В ячейку F1 вводим формулу «=СЦЕПИТЬ(E2;".")» получаем первый инициал с точкой. Точка, как и любая текстовая строка, в формуле берется в двойные кавычки – ".".
Для получения второго инициала действия аналогичные, получаем слово «Отчество» берем от него первую букву и добавляем точку в конце.
Алгоритм действий получения отчества: вернуть из строки с ФИО количество символов от второго пробела до конца строки. Для реализации алгоритма используем функцию «ПСТР». Для получения переменой аргумента «число_знаков» нам необходимо от числа символов строки отнять начальную позицию слова «Отчество»+1.
Для получения позиции начала слова «Отчество» нужна позиция второго пробела, воспользуемся информацией полученной ранее. Позиция второго пробела находится в ячейке C1. В ячейку G1 вводим формулу «=C1+1» и получаем позицию начала слова «Отчество».
Для получения количества символов текстовой строки используется функция «ДЛСТР». В ячейку H1, вводим формулу «=ДЛСТР(A1)».
В ячейку I1 вводим формулу «=ПСТР(A1;G1;H1-G1+1)» и получим слово «Отчество». Далее используя функцию «ЛЕВСИМВ» и «СЦЕПИТЬ» вводим в ячейке J1 формулу «=СЦЕПИТЬ(ЛЕВСИМВ(I1);".")» получаем второй инициал. И для получения Фамилии с инициалами в ячейку K1 введем формулу «=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1);" ";F2;" ";J2)».
Разберем еще несколько примеров использования текстовых функций.
