Методичка. Учебное пособие В. М. Лопатин издание второе, стереотипное 1 17
 Скачать 4.33 Mb.
|
|
СЦЕПИТЬ (текст 1;текст 2;…) – функция аналогичного действия, объеди- няет до 255 текстовых строк в одну строку в одной ячейке. 7 / 17 108 Обратная операция – разделение текста из одной ячейки по разным ячейкам выполняется с помощью Мастера текстов. В диалоговом окне Мастера текстов программа предложит вариант своего деления фразы, который может быть изме- нен пользователем в части формата или расстановки делителей. Мастер предста- вит также предварительный просмотр выбранных данных. Фразу, помещенную в ячейку А1 (табл. 34), с помощьюкоманды Данные/Текст по столбцам/Мастер текстов(разбор) можно разделить на три ячейки Таблица 34 Деление текста по ячейкам А B C D 1 казнить нельзя помиловать казнить нельзя помиловать С помощью функций обработки текста можно также определить день недели для любой даты календаря. ТЕКСТ (значение из ячейки; формат «ДДДД») – функция, которая преобра- зует дату, указанную в ячейке, в соответствующий день недели. Например, если в ячейку А1 занести дату рождения (в формате Дата), а в ячейку В1 функцию ТЕКСТ с аргументами, показанными в табл. 35, то результат вычисления будет представлен в виде соответствующего дня недели. Таблица 35 Определение дня недели для конкретной даты А B 1 00.00.00 =ТЕКСТ(А1; «ДДДД») Функции категории Дата и время В категории Дата и время содержится примерно два десятка функций. СЕГОДНЯ() – функция без аргумента, которая выдает текущую дату (на день ввода). Используя эту функцию можно произвести расчет количества про- житых дней (табл. 36), если в ячейку В1 занести дату рождения (в формате Дата), в ячейку В2 – функцию СЕГОДНЯ(), а в ячейку В3 – формулу для расчета раз- ности (в общем формате). Таблица 36 Промежуток времени между датами А В 1 Дата рождения 00.00.00 2 Сегодня =СЕГОДНЯ() 3 Прожито дней =В2-В1 8 / 17 109 Функции этой категории позволяют также рассчитать промежуток времени между событиями с точностью до секунды. Пример расчета показан в табл. 37. Таблица 37 Определение продолжительности события Средства анализа данных В составе приложения Excel имеются специальные средства для анализа дан- ных. К таким средствам относятся инструменты: подбор параметра и поискре- шения. Подбор параметра Задачи по отысканию параметра, при котором некоторая функция y=f(x) при- нимает определенное значение, возникают в математике, технике, экономике, бухгалтерии, банковском деле и многих других областях. В Excel решение по- добных задач реализовано как поиск параметра или аргумента, удовлетворяю- щего конкретному значению функции. Если известно, какой результат необхо- димо получить в расчете по известной формуле y=f(x), но не известно значение аргумента x, при котором достигается этот результат, то используется средство Подбор параметра. Excel ищет решение методом последовательных приближе- ний (итераций). Чтобы воспользоваться средством Подбор параметра, необходимо выполнить следующие операции. 1. Заполнить ячейки формулами и данными, которые будут использоваться для расчета. Выделить и оставить пустой ячейку, предназначенную для пара- метра (аргумента). 2. Установить курсор в ячейке с формулой. 3. Выбрать команду Данные/Анализ «что-если»/Подбор параметра и запол- нить поля диалогового окна Подбор параметра: −в поле Установить в ячейке должна находиться ссылка на выделенную ячейку; −в поле Значение нужно ввести требуемое числовое значение функции; −в поле Изменяя значение ячейки необходимо ввести ссылку на ячейку, за- резервированную для аргумента. 4. После нажатия кнопки ОК программа автоматически найдет решение. А В 1 1 Дата, время 30.03.18 6:00:00 2 2 Дата, время 01.04.18 12:00:00 3 Промежуток времени =В2-В1 (формат Время) 9 / 17 110 Пример. С помощью подбора параметра найдем корень уравнения: (2х + 10)/2 + (4х + 20) = 60. Первоначально занесем расчетную формулу в электронную таблицу. При этом будем считать, что неизвестный параметр x находится в ячейке А1 (табл. 38). Таблица 38 Ввод расчетной формулы А B 1 =(2*A1+10)/2+(4*A1+20) Выберем команду Данные/Анализ «что-если»/Подбор параметра. В окно Подбор параметра введем данные (табл. 39). Таблица 39 Окно инструмента Подбор параметра Установить в ячейке B2 Значение 60 Изменяя значение ячейки А1 После нажатия кнопки ОК программа выведет в ячейке А1 значение 7. Это означает, что корень уравнения x = 7. Пример. Составим штатное расписание и определим размер месячной зара- ботной платы работников станции технического обслуживания, если фонд ме- сячной заработной платы равен 800 тыс. руб., при этом для расчетов заработной платы используем следующие соотношения: −слесарь получает в 1,6 раза больше рабочего; −станочник – в 1,3 раза больше слесаря; −мастер – на 1000 руб. больше, чем станочник; −менеджер – в 1,5 раза больше слесаря; −зав. складом – на 2500 руб. меньше мастера; −главный бухгалтер – в 1,5 раз больше менеджера; −директор – в 1,4 раза больше главного бухгалтера. Используем приведенные соотношения и заполняем таблицу исходными формулами, ячейку В2 оставляем под параметр, который нужно определить (табл. 40). 10 / 17 111 Таблица 40 Ввод расчетных формул A B C D 1 Должность Заработная плата Кол-во персонала Сумма выплат 2 рабочий 2 =В2*С2 3 слесарь =В2*1,6 8 =В3*С3 4 станочник =В3*1,3 3 =В4*С4 5 мастер =В4+1000 3 =В5*С5 6 менеджер =В3*1,5 4 =В6*С6 7 зав. складом =В5-2500 1 =В7*С7 8 главный бухгалтер =В6*1,5 1 =В8*С8 9 директор =В8*1,4 1 =В9*С9 10 Всего: =СУММ(D2:D9) В окно Подбор параметра вводим данные (табл. 41). Таблица 41 Окно инструмента Подбор параметра Установить в ячейке D10 Значение 800000 Изменяя значение ячейки B2 После нажатия клавиши ОК программа заменит все формулы в табл. 40 чис- ленными данными, в том числе данными о заработной плате всех работников в текущем месяце. Следует отметить, что совокупность использованных расчетных формул об- разует небольшую программу. Составленная таким образом программа может использоваться для ежемесячной корректировки заработной платы в зависимо- сти от полученного дохода. Подбор параметра используется в случае, когда необходимо отыскать един- ственный возможный аргумент функции. При увеличении числа параметров сле- дует пользоваться инструментом Поиск решения. Поиск решения В экономике и технике широкое применение находят оптимизационные мо- дели. Эти модели используются для решения задач подбора сбалансированного 11 / 17 112 рациона питания, оптимизации ассортимента продукции, транспортной логи- стики и пр. Надстройка программы Поиск решения предназначена для быстрого поиска оптимальных решений с использованием сложных моделей. Поиск решения поз- воляет использовать одновременно большое количество изменяемых ячеек (до 200) и задавать ограничения для изменяемых ячеек. Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения. 1. Существует единственная целевая ячейка, значение в которой должно быть сделано максимальным, минимальным или же равным какой-то конкретной величине. 2. Значение в целевой ячейке связано формулами с рядом изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые обеспечили бы оптимальное значение для целе- вой ячейки. 3. Дополнительно может быть задано некоторое количество ограничений – условий или соотношений, которым должны удовлетворять некоторые из изме- няемых ячеек. Кнопка надстройки Поиск решения расположена в разделе меню Данные. Если кнопка отсутствует, то для её установки следует выполнить последователь- ность действий: Кнопка Office/Параметры Excel/Надстройки/Поиск решения/Перейти/По- иск решения/ОК. В качестве примера рассмотрим решение задачи, в которой необходимо отыс- кать три неизвестных параметра. Пример. Решим известную старинную задачу из арифметики Л. Ф. Магниц- кого. Крестьянин на базаре за 100 руб. купил 100 голов скота. Бык стоит 10 руб., корова 5 руб., теленок 50 коп. Сколько быков, коров и телят купил крестьянин? Воспользуемся инструментом Поиск решения. Заносим исходные данные и расчетные формулы, которые потребуются для решения задачи, в таблицу (табл. 42). Таблица 42 Ввод исходных данных и расчетных формул A B С D 1 Кол-во голов Цена, руб. Сумма, руб. 2 Бык 10 =В2*С2 3 Корова 5 =В3*С3 4 Теленок 0,5 =В4*С4 5 Всего =СУММ(В2:В4) =СУММ(D2:D4) 12 / 17 113 Ячейки B2, B3, B4 отведены под определяемые параметры или изменяемые ячейки – количество голов скота, все остальные ячейки таблицы заполнены дан- ными и формулами. Далее открываем окно Поиск решения, вводим имена изме- няемых ячеек и ограничения, заложенные в условиях (табл. 43). Таблица 43 Окно надстройки Поиск решения Установить в целевую ячейку D5 значение 100 Изменяя ячейки B2:В4 Ограничения B5=100 B2>=1 B3>=1 B4>=1 B2= целое B3= целое B4= целое После нажатия клавиши Выполнить программа выполнит расчеты и найдет одновременно три параметра: В2 = 1, В3 = 9 и В4 = 90. Базы данных и системы управления базами данных С появлением компьютера на рабочем столе каждый специалист в той или иной мере стал заниматься сбором, накоплением и обработкой систематизиро- ванных цифровых данных, которые со временем выделились в отдельный объект исследования и получили название «База данных». Первое оборудование и про- граммное обеспечение для обработки структурированных цифровых записей по- явилось в 50-х гг. прошлого века. Термин «база данных» стал общепринятым в 1960-х, а в 1970-х гг. была сформирована модель представления данных, осно- ванная на математических и логических подходах. Для первых баз данных был создан структурированный язык запросов (SQL), который позволял манипулировать в базе отдельными записями и группами за- писей. Позднее для управления большими базами данных были созданы системы управления базами данных – СУБД. Все эти системы поддерживают язык запро- сов SQL наравне со своим уникальным языком. С начала 1980-х базы данных стали широко использоваться для удовлетворе- ния потребностей многих предприятий и организаций. Первоначальное пред- ставление об упорядоченном наборе записей сменилось на более точное понятие, которое продолжает дополняться и корректироваться. В зависимости от источ- ника данных это понятие выражается в разных формулировках. Общепризнанная формулировка баз данных (БД) пока отсутствует, для раскрытия понятия удобно пользоваться определениями из разных источников. 13 / 17 114 1. Базой данных является совокупность самостоятельных материалов (ста- тей, расчетов, нормативных актов и подобных материалов), систематизирован- ных таким образом, чтобы эти материалы могли быть найдены и обработаны с помощью ЭВМ [15]. 2. База данных – это организованная структура, предназначенная для хра- нения информации [1]. Это определение означает, что в современных базах мо- жет храниться информация, хотя их по-прежнему называют базами данных. На основе известных формулировок можно дать следующее короткое опре- деление. База данных – совокупность структурированных логически связанных ма- териалов, которые собраны в одном или нескольких файлах. Для уточнения понятия БД используют также отличительные признаки, ко- торые выражаются в следующем. 1. БД хранятся в компьютерах и обрабатываются с помощью компьютерных программ. Этот признак исключает из числа баз данных некомпьютерные храни- лища, например бумажные архивы или библиотеки. 2. Данные в базе логически структурированы или систематизированы для удобства пользования, выполнения операций поиска, дополнения, выборки дан- ных и др. 3. В БД встраивается логическая структура данных в соответствии с моде- лью, которая закладывается при разработке базы. Наряду с определениями и отличительными признаками существует обще- принятая практика, в соответствии с которой не называют базами данных фай- ловые архивы, интернет-порталы, электронные таблицы, хотя они обладают не- которыми признаками БД. Определения и отличительные особенности БД свидетельствуют о том, что они созданы для хранения огромных массивов данных и автоматической обра- ботки этих массивов с помощью вычислительной техники. Для надежного хра- нения информации, которая может иметь большую ценность, в базе данных в отличие от других программ используют двойной подход к изменению данных. 1. Операции по созданию или изменению структуры базы данных производят только с копией и никогда не производят с базой данных, находящейся в эксплу- атации. 2. Все изменения с данными сохраняются немедленно и автоматически. Базу данных нельзя закрыть без сохранения, так как все сохранения бывают завер- шены к моменту закрытия. Классификация и структура баз данных Множество существующих баз данных поддается различным видам класси- фикаций [16]. Одной из распространенных является классификация по модели данных,в которой выделяются следующие категории. 1. Иерархические базы данных могут быть представлены как дерево, состо- ящее из объектов различных уровней. Примером иерархической структуры яв- ляется файловая структура. 14 / 17 115 2. Сетевые базы данных состоят из элементов (узлов), каждый из которых находится на определенном уровне и может быть связан любым другим элемен- том. 3. Реляционные базы данных формируются на основе таблиц, связанных между собой определенными отношениями. 4. В классификации по содержимому название категории определяется со- держанием БД: исторические, экологические, экономические и т. п. В классификации по степени распределенности отмечают место распо- ложения и выделяют централизованные (сосредоточенные) и распределенные базы данных. Распределенные базы данных, как правило, обрабатываются раз- ными удаленными друг от друга пользователями или организациями. Относительно недавно распределенные базы данных получили мощный им- пульс в своем развитии. Этот импульс связан с возникновением технологии блок- чейн (англ. blockchain – цепочка блоков). Суть технологии или платформы блок- чейн выражается в формировании данных в виде связанных между собой блоков, которые хранятся на некотором множестве компьютеров. Другими словами, блокчейн – это распределенная база данных, в создании и поддержании которой принимает участие множество пользователей. Создание записей или блоков в этой базе подчиняется определенным прави- лам. Новые записи постоянно создаются пользователями, каждая вновь создан- ная запись проверяется участникамисети. После подтверждения запись присо- единяется к уже существующему блоку, после этого блок сохраняется и автома- тически обновляется на всех сетевых компьютерах. Обновленный блок хранится в зашифрованном виде, доступ к нему возможен с помощью электронной циф- ровой подписи. Технология блокчейн имеет свои специфические преимущества. 1. Безопасность базы данных обеспечивается ее распределенным функцио- нированием и многократным копированием. Взломать или изменить такую базу невозможно, поскольку задача по отысканию тысяч компьютеров и дешифрова- нию данных каждого относится к практически нерешаемым. 2. Децентрализация заложена одним из принципов существования этой сети. Технология позволяет любым пользователям напрямую обмениваться дан- ными. Подлинность операций проверяет только сообщество пользователей. Цен- трализованный надзор за процессом функционирования не предусмотрен. 3. Прозрачность всех операций обеспечивается публичным доступом к со- храненным записям. Первую проверку технология блокчейн получила в 2009 г., когда на ее основе была создана криптовалюта биткоин. Опыт эксплуатации новой платформы, по- лученный в ходе развития этой и других криптовалют, показал жизнеспособ- ность и перспективы существования технологии блокчейн. Области применения блокчейна в дальнейшем связаны с учетом и хранением больших объемов дан- ных, которые представляют ценность для пользователей. К возможным областям применения относятся формирование систематизированных записей в форме ре- естра (электронные библиотеки, карта звездного неба), облачные системы 15 / 17 116 хранения данных, финансовые операции, контроль сделок с недвижимостью и многое другое. БД имеет табличную структуру, в которой в отличие от обычной таблицы столбец называют полем, а строку – записью. Поля образуют структуру, а записи относятся к данным, которые хранятся в базе (табл. 44). Структура – это сетка с наименованием полей, которая может быть постро- ена и без записей. Например, ежедневник – это определенная база данных, кото- рая может существовать в заполненном и незаполненном состоянии. Структура базы данных создается в процессе ее проектирования и не может быть изменена пользователем при занесении данных. Таблица 44 Структура базы данных Поле 1 Поле 2 Поле 3 Запись1 Запись2 Запись3 Каждому полю при создании базы данных присваиваются определенные свойства. Свойства полей зависят от типа данных. Различаются свойства тексто- вых и графических данных. В приложении к текстовым данным могут быть вы- браны, в частности, следующие свойства. Имяполя – по умолчанию именем поля является заголовок столбца. Типполя – определяет тип данных, которые могут содержаться в поле. Размерполя – определяет предельную длину данных (в символах), которые могут размещаться в поле. Форматполя – определяет способ форматирования данных в ячейках, при- надлежащих полю. Маскаввода – определяет форму, с помощью которой вводятся данные в поле (средство автоматизации ввода). Значениепоумолчанию – значение, которое вводится в ячейки автомати- чески (средство автоматизации ввода). Сообщениеобошибке – текстовое сообщение, которое выдается автома- тически при вводе ошибочных данных. Обязательноеполе – свойство, определяющее обязательность заполнения данного поля. Индексированноеполе – полю придается дополнительное свойство для ускорения поиска или сортировки записей. Перечень и названия свойств полей могут быть значительно расширены в за- висимости от специфики базы данных. Наряду со свойством полей в БД необхо- димо учитывать тип данных. Таблицы БД могут иметь значительное число типов данных. Текстовый– тип данных для хранения текста ограниченного размера (до 255 символов). 16 / 17 |