Бизнес-информатика by программирование кисит

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

СОДЕРЖАНИЕ ВВЕДЕНИЕ ............................................................................................................................... 3 ТЕМА 1. MICROSOFT WORD 2016................................................................................... 4 1.

ТЕХНОЛОГИЯ СОЗДАНИЯ РАССЫЛОК ...................................................................................4

ОСНОВНЫЕ ПРАВИЛА ОФОРМЛЕНИЯ ТЕКСТОВОЙ ДОКУМЕНТАЦИИ ....................6

СТРУКТУРИРОВАНИЕ ТЕКСТА ДОКУМЕНТА. СОЗДАНИЕ СОДЕРЖАНИЯ ................8

РАБОТА С ИЗОБРАЖЕНИЯМИ В MS WORD 2016 .................................................................11

ДОБАВЛЕНИЕ НАЗВАНИЙ К РИСУНКАМ ..............................................................................21

ДОБАВЛЕНИЕ НАЗВАНИЙ К ТАБЛИЦАМ ..............................................................................23

ОСНОВНЫЕ НЕПЕЧАТАЕМЫЕ СИМВОЛЫ ..........................................................................26

СОЗДАНИЕ ЗАПОЛНЯЕМОЙ ФОРМЫ .....................................................................................31

СОЗДАНИЕ И ЗАПУСК МАКРОСА .............................................................................................36

ТЕСТ 1. MICROSOFT WORD 2016 ............................................................................................................42

ТЕМА 2. ОСНОВЫ VBA ..................................................................................................... 44 1.

ЗАПИСЬ МАКРОСА В EXCEL ..........................................................................................................44

ФУНКЦИИ-ПРОЦЕДУРЫ VBA ........................................................................................................49

ТЕСТ 2. ОСНОВЫ VBA ...............................................................................................................................80

ТЕМА 3. SMART-ТАБЛИЦЫ В MS EXCEL 2016 ....................................................... 82 1.

СОЗДАНИЕ ТАБЛИЦЫ EXCEL .......................................................................................................82

ПРИМЕР УМНОЙ ТАБЛИЦЫ EXCEL 2007-2016 .........................................................................89

УМНЫЕ ТАБЛИЦЫ. ДОПОЛНИТЕЛЬНЫЕ СВЕДЕНИЯ. ........................................................92

УСЛОВНОЕ ФОРМАТИРОВАНИЕ В MS EXCEL ........................................................................97

СОЗДАНИЕ РАСКРЫВАЮЩИХСЯ СПИСКОВ В EXCEL ......................................................112

СОЗДАНИЕ СПАРКЛАЙНОВ .........................................................................................................124

ИНТЕРАКТИВНАЯ ДИАГРАММА ................................................................................................128

ТЕСТ 3. SMART-ТАБЛИЦЫ В MS EXCEL 2016 ..................................................................................136

ТЕМА 4. БАЗЫ ДАННЫХ В MS EXCEL 2016 .......................................................... 138 1. РАБОТА С ФИЛЬТРАМИ В MS OFFICE EXCEL ...........................................................................138 2. УСЛОВНОЕ ФОРМАТИРОВАНИЕ В ФИЛЬТРАХ ........................................................................146 ТЕСТ 4. ФИЛЬТРАЦИЯ ДАННЫХ В MS EXCEL ...............................................................................147

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ ....................................................... 150

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

ВВЕДЕНИЕ Дистанционный курс «Бизнес-информатика» создан для студентов русскоязычного потока «Киевского Национального Экономического Университета имени Вадима Гетьмана» на базе Microsoft Office 365. Бизнес-информатика — наука о проектировании, разработке и применении информационных и коммуникационных систем в бизнесе. Областью прикладных исследований бизнес-информатики стали проблемы построения эффективной системы управления компанией, основанной на выбранной стратегии управления бизнесом (MRP, ERP, CRM), бизнес-процессах, обеспечивающих достижение стратегической цели, и поддерживающих их информационных технологий. Наука «Бизнес-информатика» сформировалось от предшествующих направлений «Прикладная информатика в экономике» и «Информационные системы в экономике». Проиллюстрировать решение бизнес-процессов позволяют разные инструментальные среды. В предлагаемом курсе в качестве таких сред предлагаются: текстовый редактор Microsoft Word и электронные таблицы Microsoft Excel. Электронные таблицы являются не только практическим инструментом бизнес-аналитика, но и средой, позволяющей дать студентам навыки визуализации данных, проиллюстрировать реализованные в сложных аналитических системах возможности. В профессиональной деятельности каждый бизнес-аналитик сталкивается в работе с необходимостью на регулярной основе выполнять однообразные операции в среде Microsoft Office. Для автоматизации выполнения подобных задач встроен в офисном пакете специальный язык программирования Visual Basic for Application. Наличие знаний и навыков в области программирования на языке VBA позволяет бизнес-информатику решать практически любые задачи автоматизации по сбору, анализу и визуализации данных. Бизнес-информатика онлайн дает возможность стать профессиональным бизнес-аналитиком, который отвечает за технологическую и информационную в сфере предпринимательства и способен: − оказывать помощь в ведении электронного бизнеса; − внедрять технологии, позволяющие усовершенствовать работу предприятия; − управлять контентом и корпоративными ИС; − осуществлять реорганизацию бизнеса; − заниматься управлением IT-проектов; Материал курса призван сформировать у студентов понимание возможностей информационных технологий по обеспечению бизнес-процессов компании. Полученные знания позволят в дальнейшем изучать подходы к моделированию и анализу бизнес-процессов, алгоритмы и среды анализа данных, стандарты управления ИТ-ресурсами компании, а также другие важные методы и технологии, которые необходимы для разработки и поддержки современной архитектуры предприятия на уровне мировых стандартов. 3

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

ТЕМА 1. MICROSOFT WORD 2016 1. ТЕХНОЛОГИЯ СОЗДАНИЯ РАССЫЛОК Использование инструмента Слияние для подготовки комплексных документов в MS Word 2016. Текстовый процессор MS Word 2016 предоставляет возможность автоматизированной подготовки документов для рассылки. Слияние можно использовать для создания любого типа документов, в которых поля сопоставляются данным, а именно для подготовки документов стандартной формы нескольким адресатам (приглашения, уведомления, ведомости результатов и т. п). Для этого необходимо первоначально подготовить шаблон в виде произвольного документа Word и список реквизитов в отдельном файле, а затем выполнить их слияние. Процедура слияния состоит из нескольких этапов: 1. Создание или выбор существующего источника данных. Источником данных называется файл, содержащий данные, различающиеся в каждой копии составного документа. Источником данных может быть практически любая структурированная в таблицу информация. 2. Создание основного документа. Основным документом слияния называется документ, который содержит текст и рисунки, одинаковые в каждой версии конечного документа. 3. Вставка полей слияния. Поля, в которые переносится переменная информация из источника данных в основной документ перед выводом готового документа на печать, называются полями слияния. 4. Вывод стандартного документа. При этом каждая строка (или запись) источника данных порождает отдельный документ на бланке, почтовую наклейку, конверт или элемент каталога. При этом составные документы могут быть объединены в новом документе для последующего просмотра и печати. Результат можно выводить сразу на печать, либо получить в виде нового документа с множеством страниц. Процедура слияния позволяет автоматически создать множество копий шаблона документа с различными реквизитами. Пример. На основе шаблона с приглашением и базы данных студентов создать сложный документ Microsoft Word, на основе сформированного списка рассылки приглашений студентов на конференцию.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

1) Создайте файлы Шаблон_ приглашение.docх и БД_студентов.docх 2) Откройте файл Шаблон_ приглашение.docх 3) Создайте связь между файлом с приглашением и файлом с базой данных: а) вкл. РАССЫЛКИ → гр. НАЧАТЬ СЛИЯНИЕ → кн. НАЧАТЬ СЛИЯНИЕ → ПОШАГОВЫЙ МАСТЕР СЛИЯНИЯ

б) в правой части окна MS Word, в область задач СЛИЯНИЕ → выберите тип документа: ПИСЬМА → кн. ДАЛЕЕ. в) ОТКРЫТИЕ ДОКУМЕНТА → укажите основной документ: ТЕКУЩИЙ ДОКУМЕНТ → кн. ДАЛЕЕ. г) ВЫБОР ПОЛУЧАТЕЛЕЙ → укажите источник данных: ИСПОЛЬЗОВАНИЕ СПИСКА → кн. ОБЗОР → укажите файл БД_студентов.docх → не изменяйте источник!!! → кн. ОК д) закройте область задач СЛИЯНИЕ. 4) В приглашении добавьте поле слияния Группа с базой данных студентов: а) выделите в приглашении текст {Группа} б) вкл. РАССЫЛКИ → гр. СОСТАВЛЕНИЕ ДОКУМЕНТА И ВСТАВКА ПОЛЕЙ → кн. ДОБАВИТЬ ПОЛЕ СЛИЯНИЯ в) выберите соответствующее поле → кн. ВСТАВИТЬ → в документе появится ФИО первого сотрудника или название поля БД г) аналогично с полями слияния Фамилия, Имя и отчество. 5) Перейдите к просмотру значений полей БД, для того чтобы переключиться между режимами отображения названий полей БД и их значений: вкл. РАССЫЛКИ → гр. ПРОСМОТР РЕЗУЛЬТАТОВ → кн. ПОСМОТРЕТЬ РЕЗУЛЬТАТЫ. 5

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

6) Используйте кн. перехода между записями, чтобы поочерёдно просмотреть приглашения для всех студентов . 7) Чтобы получить в новом документы приглашения всех студентов одновременно (список рассылки): вкл. РАССЫЛКИ → гр. ЗАВЕРШЕНИЕ → кн. НАЙТИ И ОБЪЕДИНИТЬ → ИЗМЕНИТЬ ОТДЕЛЬНЫЕ ДОКУМЕНТЫ → выбрать ОБЪЕДИНИТЬ ВСЕ ЗАПИСИ → ОК. 2. ОСНОВНЫЕ ПРАВИЛА ОФОРМЛЕНИЯ ТЕКСТОВОЙ ДОКУМЕНТАЦИИ Основные правила лингвистической обработки текста В MS Word установлены словари по каждому языку, если какого – либо слова нет в словаре данного языка, то MS Word выделит его это как орфографическую ошибку и подчеркнет красной волнистой линией. В случае несоответствия предложения грамматическим и синтаксическим правилам, MS Word подчеркнёт предложение зеленой волнистой линией. Чтобы установить автоматическую проверку правописания (орфографии) и грамматики текста документа: ✓ ФАЙЛ → ПАРАМЕТРЫ → ПРАВОПИСАНИЕ Способы устранения появляющихся ошибок: ✓ ПКМ на подчёркнутом слове → выбрать возможный вариант написания слова, или же изменить язык ввода, либо добавить слово в словарь; ✓ вкл. РЕЦЕНЗИРОВАНИЕ → гр. ПРАВОПИСАНИЕ → кн. ПРАВОПИСАНИЕ Чтобы задать язык введенного фрагмента текста (напр., в случае ошибочно набранног украинского текста на русской раскладке клавиатуры, тогда MS Word подчеркнет каждое слово красной волнистой линией): 1) выделить текст 2) 2 click на индикаторе языка в строке состояния

3) в диалоговом окне ЯЗЫК из списка ПОМЕТИТЬ ВЫДЕЛЕННЫЙ ТЕКСТ КАК… выбрать необходимый язык → кн. ОК. Чтобы установить необходимый язык по умолчанию во всем документе: 1) 2 click на индикаторе языка на строке состояния

2) в диалоговом окне ЯЗЫК в списке ПОМЕТИТЬ ВЫДЕЛЕННЫЙ ТЕКСТ КАК… выбрать необходимый язык → кн. ПО УМОЛЧАНИЮ → кн. ОК. Чтобы установить автоматическую смену раскладки клавиатуры в зависимости от языка, на котором обозначен текст, где расположили курсор: ФАЙЛ 6

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

→ ПАРАМЕТРЫ → ДОПОЛНИТЕЛЬНО → зона ПАРАМЕТРЫ ПРАВКИ → АВТОМАТИЧЕСКИ ПЕРЕКЛЮЧАТЬ РАСКЛАДКУ СООТВЕТСТВИИ С ЯЗЫКОМ ОКРУЖАЮЩЕГО ТЕКСТА

КЛАВИАТУРЫ

Чтобы установить автоматическую расстановку переносов в словах в документе в соответствии с орфографическими нормами: вкл. РАЗМЕТКА СТРАНИЦЫ → гр. ПАРАМЕТРЫ СТРАНИЦЫ → кн. РАССТАНОВКА ПЕРЕНОСОВ Чтобы установить принудительный перенос в слове: CTRL + –. В результате слово не разрывается как структурная единица текста, и MS Word не определяет это слово как ошибку, в отличие от вставленного дефиса в месте переноса слова. Основные правила форматирования текста Стандартный формат шрифта: Times New Roman, размер 14 пт, начертание ОБЫЧНЫЙ. Установка параметров форматирования символов «по умолчанию»: вкл. ГЛАВНАЯ → кн. гр. ШРИФТ → в диалоговом окне ШРИФТ установить указанные параметры → кн. ПО УМОЛЧАНИЮ Стандартный формат абзаца: отступы справа, слева, перед и после — 0 пт, отступ первой строки — 1-1,5 см, полуторный междустрочный интервал, выравнивание по ширине абзаца. Установка параметров форматирования абзацев «по умолчанию»: вкл. ГЛАВНАЯ → кн. гр. АБЗАЦ → в диалоговом окне АБЗАЦ установить указанные параметры → кн. ПО УМОЛЧАНИЮ Стандартные параметры страницы: ориентация книжная; поля: верхнее — 2 см, нижнее — 2 см, правое — 1-1,5 см, левое — 2,5 см, расстояние от края поля до колонтитула — 1,25 см. Установка параметров форматирования разделов «по умолчанию»: вкл. РАЗМЕТКА СТРАНИЦЫ → кн. гр. ПАРАМЕТРЫ СТРАНИЦЫ → в диалоговом окне ПАРАМЕТРЫ СТРАНИЦЫ установить указанные параметры → кн. ПО УМОЛЧАНИЮ Стандартные параметры страницы: ориентация книжная, поля: верхнее — 2 см, нижнее — 2 см, правое — 1-1,5 см, левое — 2,5 см, расстояние от края поля до колонтитула — 1,25 см (2 click на линейке).

Стандартный формат шрифта: Times New Roman, размер 14 пт, начертание - ОБЫЧНЫЙ

Стандартный формат абзаца: отступы справа, слева, перед и после — 0 пт, отступ первой строки — 1-1,5 см, полуторный междустрочный интервал, выравнивание по ширине абзаца (2 click на маркере абзацного отступа). 7

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

3. СТРУКТУРИРОВАНИЕ ТЕКСТА ДОКУМЕНТА. СОЗДАНИЕ СОДЕРЖАНИЯ Под структурой понимают иерархию заголовков и соответствующих (подчиненных) им частей текста документа ЗАГОЛОВКИ ПИШУТ В ОТДЕЛЬНЫХ АБЗАЦАХ!!!

Пример 4. Структурировать текст реферата и создать его автоматическое содержание: 1. Открываем вкладку ССЫЛКИ

2. Ставим курсор мышки напротив названия главы, например,

3. Затем нажимаем ДОБАВИТЬ ТЕКСТ

4. Выбираем нужный уровень содержания (оглавления), в нашем случае УРОВЕНЬ 1 (напротив него появляется галочка). 8

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

5. Далее по всему тексту реферата структурируем разделы и подразделы реферата, проставляя нужные уровни – обычно разделы и подразделы – УРОВЕНЬ 1, подразделы – УРОВЕНЬ 2, подпункты – УРОВЕНЬ 3. Как правило, в учебных работах используются три уровня, они настроены в программе автоматически, но можно добавить и другие уровни.

6. Затем, на первой странице реферата, пишем по центру слово СОДЕРЖАНИЕ, добавляем пустую строку.

7. На вкладке ССЫЛКИ → кн. ОГЛАВЛЕНИЕ, выбираем пункт ОГЛАВЛЕНИЕ…

8. В диалоговом окне ОГЛАВЛЕНИЕ → вкл. ОГЛАВЛЕНИЕ → выбрать ЗАПОЛНИТЕЛЬ → кн. ОК.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

9. Автоматизированное содержание в документе – готово.

10. При изменение документа (заголовков разделов, нумерации страниц) необходимо обновить содержание. В созданном содержании, правой кнопкой мыши вызываем контекстное меню, выбираем команду ОБНОВИТЬ ПОЛЕ.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

11. Для обновления номеров страниц активируем переключатель ОБНОВИТЬ ТОЛЬКО НОМЕРА СТРАНИЦ → кн. ОК.

Для обновления всего содержание активируем переключатель ОБНОВИТЬ ЦЕЛИКОМ → кн. ОК. 12. Для отображения на экран СХЕМЫ ДОКУМЕНТА и перемещения по заголовкам содержания: вкл. ВИД → гр. ПОКАЗАТЬ → кн. ОБЛАСТЬ НАВИГАЦИИ

4. РАБОТА С ИЗОБРАЖЕНИЯМИ В MS WORD 2016 Вставка изображения Изображения, рисунки, фотографии, можно добавлять в документ Word из различных источников: лазерных носителей, флэш-дисков, жесткого диска или различных веб-ресурсов. Для того чтобы вставить в документ изображение, необходимо щелкнуть в том месте куда будет добавлено изображение жесткого диска, после чего: 1. Перейдите на вкладку Вставка → Рисунок 2. В открывшемся окне Вставка рисунка найдите файл с изображением, щелкните по нему; 3. Нажмите кнопку Вставить Вставка изображения может быть выполнена тремя различными способами. 1. Вставить - в этом случае изображение будет помещено в документ без связи с исходным файлом. Изображение помещается в документ без изменений. В то же время, изменения, вносимые в исходный файл, никак не влияют (не изменяют) изображение, вставленное в документ. 2. Связать с файлом - в данном случае в документ помещается лишь эскиз изображения, а сам рисунок не размещается в теле документа. Если вносить изменения в изображение, то они не отражаются на эскизе рисунка (будут видны при последующем открытии документа). Если перенести документ на другой носитель, не поместив на него использованное изображение, то оно удалится. Вывод: связанные изображения должны либо находиться в теле документа, либо размещаться в папке документа и быть связаны (процедуру вставки изображения необходимо провести повторно).

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

3. Вставить и связать - изображение вставляется полностью, но остается связь с исходным файлом. То есть изменения, вносимые в оригинал, так же будут отражены во вставленном изображении. Его имеет смысл использовать, в случае если вам необходимо переносить документ на другие носители или размещать в Интернете одним файлом, но при создании документа вы хотите видеть изменения в изображениях. При переносе на носитель документа с эскизом изображения, связь с оригиналом теряется (если он не размещен на том же носителе и путь к нему не обновлен). При работе с окном Вставка рисунка можно вставлять изображения способом Вставить (файл изображения добавляется как внедренный рисунок без связей с оригиналом) - для этого дважды щелкните по файлу изображения. Данный способ вставки используется в Word по умолчанию. Вставка изображения с веб-страницы Первый способ: 1. Открыть документ Word, а затем веб-страницу с изображением; 2. Щелкнуть правой кнопкой мыши на рисунке (на веб-странице) и в контекстном меню выбрать пункт Копировать; 3. Щелкнуть правой кнопкой мыши в нужном месте страницы документа Word и выбрать пункт Вставить. Второй способ: 1. Открыть документ Word и веб-страницу с изображением. 2. Щелкнуть изображение и перетащить его в документ Word. 12

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Данный способ подходит только в случаях, когда уменьшенная копия изображения содержит в себе ссылку на полноразмерное изображений. Ссылка может быть привязана к чему угодно, но чаще всего это копия изображения или тематический текст. Если перетащить неразвернутое изображение (в составе веб-страницы), то переместится только ссылка на него. Она привяжется к находящемуся в буфере обмена тексту или изображению, который автоматически будет вставлен с данной гиперссылкой. В отдельном случае, можно увидеть надпись «Ошибка! Недопустимый объект гиперссылки». Она сообщает о том, что изображение не может быть перемещено. Гиперссылка на него не может быть привязана к информации, помещенной в буфер обмена. Для вставки изображений с веб-страницы можно их предварительно сохранить на жестком диске компьютера, а затем вставить в документ описанными выше способами: 1. Открыть изображение на веб-странице, а затем щелкнуть на нем правой кнопкой мыши; 2. В контекстном меню выбрать пункт Сохранить рисунок как... Замена одного рисунка другим Если изображение в документе необходимо заменить на другое, при неизменных настройках размера и положения рисунка в документе, выполнить следующее: Первый способ. 1. Выделить в документе Word нужное изображение 2. Открыть вкладку Формат → гр. Изменить → кн. Изменить рисунок 3. В открывшемся окне Вставка изображения → выбрать изображение кн. Вставить.

Второй способ. 1. Щелкнуть по изображению правой кнопкой мыши в окне документа Word 2. В контекстном меню выбрать пункт Изменить рисунок 3. В окне Вставка изображения выбрать нужное изображение. 13

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Обтекание текстом Изображение вставляется в документ Word как часть текста и перемещается как блок текста, поскольку является встроенным объектом. Для того чтобы переместить изображение в графический слой и получить возможность свободно перемещать его по документу, выполнить следующее: 1. В окне открытого документа Word щелкнуть правой кнопкой мыши по нужному изображению; 2. В контекстном меню выбрать пункт Обтекание текстом, а затем выбрать расположение рисунка либо с Обтеканием, либо Перед текстом.

Замещающие рамки рисунков По мере вставки в документ изображений увеличивается размер документа, и нагрузка на компьютер возрастает. На маломощных компьютерах документ Word с большим количеством изображений (особенно цветных и высокого качества) может замедлять просмотр документа. В данном случае можно ускорить работу, 14

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

отображая вместо картинок только их рамки. Тем более это удобно, если необходимо просмотреть (редактировать) только текст. 1. Щелкнуть вкладку Файл → Параметры 2. Выбрать закладку Дополнительно 3. В группе Показывать содержимое документа → установить флажок Показывать замещающие рамки рисунков → кн. ОК.

Настройка яркости и контрастности изображения Настройка яркости изображения подразумевает осветление или затемнение изображения. При использовании этих средств настройки, программа Word автоматически осуществляет изменения в диапазоне цветов, то есть настройка яркости по теням, светлым и средним тонам не доступна. Настройка используется при помощи специальных шаблонов, которые имеют разные степени экспозиции. Экспозиция, в свою очередь, определяет степень осветления/затемнения. Контрастность, является отношением самых светлых областей изображения к самым темным. В цветовом контрасте – это разность цветовых оттенков. В Word яркость и контрастность регулируется одними и теми же шаблонами. В свою очередь, шаблоны могут иметь нулевые значения яркости или контрастности, что позволяет отдельно регулировать уровень их значений. В случае тонкой настройки (окно Формат рисунка), ситуация аналогичная. Для настройки яркости и контрастности изображения, выполнить следующее: Первый способ. 1. Выделить изображение в документе Word, перейти к вкладке Формат 15

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. В группе Изменить щелкнуть по кнопке Коррекция, в открывшемся окне выбрать нужное значение яркости из коллекции образцов. Если нужна более тонкая настройка, щелкните по кнопке Параметры коррекции рисунка. Откроется окно Формат на вкладке Настройка рисунка. 3. В области Яркость указать нужное значение (в процентах) и щелкнуть кн. Закрыть. Второй способ. 1. Щелкнуть на изображении в окне Word, в контекстном меню выбрать пункт Формат рисунка. 2. Перейти на вкладку Настройка рисунка, указатьнеобходимые значения яркости. Аналогично проводится изменение резкости. Уменьшение резкости приводит к размытию изображения, а повышение, напротив, к повышению четкости изображения. Однако при этом теряются плавные переходы в изображении, и увеличивается его зернистость. Изменение насыщенности цвета Насыщенность цвета – это интенсивность определённого цветового тона в палитре. «Чистый» цвет означает минимальное присутствие пикселей других цветов, их оттенков, равно как и оттенков основного цвета. Насыщенность показывает, как выглядит определенный цвет при различной степени освещенности. Насыщенность, или интенсивность цвета, меняется в зависимости от силы освещения. Чем ярче картинка, тем ярче и насыщенный цвета в ней – и это правило касается даже черного цвета и его оттенков. Изменяя насыщенность цвета можно кардинально менять вид всего изображения, поскольку с изменением насыщенности цветов, полностью изменяется состав цветовой палитры изображения. Для цветовой настройки изображения, выполнить следующее: ✓ Выделить рисунок в документе Word. ✓ Щелкнуть вкладку Формат → в гр. Изменить → кн. Цвет. ✓ В открывшемся окне в группе Насыщенность цвета выбрать подходящий образец. Для более тонкой настройки, выполнить следующие действия: Первый способ. 1. Щелкнуть правой кнопкой мыши на нужном рисунке, в контекстном меню выбрать пункт Формат рисунка. 2. Щелкнуть по вкладке Настройка рисунка в открывшемся окне Формат рисунка перейдите к группе Насыщенность цветом. Второй способ. 1. Щелкнуть кнопку Цвет, в открывшемся окне выбрать Параметры цвета рисунка. 2. Настроить области Заготовки и Насыщенность в группе Насыщенность цвета для установки нужных параметров. 16

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Оттенок цвета Изменение оттенка цвета позволяет понизить или повысить «температуру» изображения, уменьшить или увеличить насыщенность теплыми или холодными оттенками цвета. Пример теплых цветов – оттенки красного, желтого, оранжевого, а холодных – оттенки синего, бирюзового и серого. Чтобы изменить оттенок цвета рисунка, выполнить следующее: 1. Открыть вкладку Формат → в группе Изменить → кн. Цвет. 2. В открывшемся окне перейти в группу Оттенок цвета и выбрать один из предлагаемых образцов. Для более тонкой настройки перейти в окно Формат рисунка, в группе Оттенок цвета задать нужные значения. Перекраска изображения Для полной перекраски изображения, то есть придания ему определенного цвет (цветовой схемы), выполнитб следующие действия: Первый способ. 1. На вкладке Формат → в группе Изменить → кн. Цвет. 2. В открывшемся окне перейти в группу Перекрасить, выбрать один из образцов. Второй способ. 1. Щелкнуть правой кнопкой мыши на рисунке из контекстного меню выбрать пункт Формат рисунка. 2. В окне Формат рисунка щелкнуть по вкладке Цвет рисунка. 3. В группе Перекрасить щелкнуть кнопку Перекрасить в области Заготовки, в раскрывшемся окне выбрать подходящий образец. Художественные эффекты рисунка При помощи художественных эффектов можно значительно улучшить внешний вид изображения, не прибегая к помощи специальных графических редакторов. Всего доступно 23 образца эффектов. Работа со слоями не доступна!!! Для придания рисунку художественного эффекта, выполнить следующее: Первый способ. 1. В окне открытого документа щелкнуть на изображении. 2. Перейти на вкладку Формат в группу Изменить и щелкнуть по кнопке Художественные эффекты. 3. В открывшемся окне выбрать подходящий образец. Второй способ. 1. Щелкнуть правой кнопкой мыши на рисунке из контекстного меню выбрать пункт Формат рисунка. 17

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. В открывшемся окне открыть вкладку Художественные эффекты, затем щелкнуть по кнопке Художественные эффекты, выбрать подходящий образец. Для более тонкой настройки используются параметры художественных эффектов Прозрачность и Размер. ✓ Прозрачность - позволяет регулировать уровень прозрачности примененного эффекта. Чем больше прозрачность, тем меньше изменений внесенных в рисунок при применении эффекта будет отражено и наоборот. ✓ Размер - определяет «плотность» применяемого эффекта. Так для эффекта «Акварель» - это размер кисти, для эффекта «Светлый экран» - это размер сетки и т.д. Чем меньше значения, тем аккуратнее вносимые изменения и наоборот. Настройка параметров доступна только после применения эффекта к рисунку. Удаление фона Для удаления фона у изображения, щелкнуть по кнопке Удалить фон. Появятся дополнительные настройки: ✓ Пометить области для сохранения – сохраняет выбранный цвет фона ✓ Пометить области для удаления – удаляет выбранный цвет фона ✓ Удалить пометки – удаляет пометку цвета фона ✓ Отменить все сохранения – отменяет все сохранения для удаления фона ✓ Сохранить изменения – сохраняет изменения для удаления фона Стили и трехмерные эффекты рисунка Начиная с Microsoft Word 2007, стали доступны средства представления рисунков в особом, до этого невиданном для текстовых редакторов, виде трехмерных изображений. Если ранее необходимо было создавать изображения трехмерных объектов с помощью различных специализированных редакторов, то сейчас можно основные трехмерные эффекты применить к изображению, непосредственно в Microsoft Word. Изменение формы рисунка Первый способ. 1. Щелкнуть по рисунку, а затем перейдите на вкладку Формат 2. В группе Стили щелкнуть по кнопке развертывания диалогового окна Формат фигуры 3. В открывшемся окне перейти на вкладку Формат объемной фигуры и придать нужную форму рисунку 18

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Второй способ. 1. Щелкнуть правой кнопкой мыши на рисунке, из контекстного меню выберать пункт Формат рисунка 2. В открывшемся окне перейти на вкладку Формат объемной фигуры и внесите нужные изменения С объемными фигурами можно проводить различные манипуляции – менять высоту и ширину, цвет заливки контура, поворачивать, добавлять эффекты тени и сглаживания и т.д. Окно «Формат объемной фигуры» На примере определенного рисунка можно применить эффект объема (и прилагаемые к нему эффекты). Рассмотрим работу с эффектом Рельеф, который позволяет создать эффект «прозрачной рамки», а при использовании возможностей поворота фигуры позволяет создавать неповторимые и необычные объемные фигуры. Для настройки эффектов объема рисунка вызываем диалоговое окно Формат рисунка и используем вкладку Формат объемной фигуры. Структура вкладки следующая:

✓

Рельеф - позволяет настроить форму краев (границ) изображения, задать их ширину и высоту как сверху, так и снизу. Для верхнего и нижнего краев изображения заданы 12 образцов рельефа. По умолчанию значения задаются в «пунктах», но можно вводить и в других единицах измерения – миллиметрах, сантиметрах, дюймах; Глубина - задает толщину трехмерного объекта. Наиболее наглядно это видно при задании объема фигуры в виде параллелепипеда с видом сбоку. 19

Бизнес-информатика: дистанционный курс

✓ ✓

Т. Н. Кисиль

Глубина в данном случае – это расстояние между вертикальными ребрами фигуры, т.е. чем больше глубина, тем «толще» параллелепипед; Контур - задает толщину линии подчеркивающей края фигуры; Поверхность - позволяет выбрать образец материала, из которого будет состоять объемная фигура (область Материал), и тип применяемого к ней освещения (и его цветовой схемы), а так же угол падения (области Освещение и Угол).

Вставка названия рисунка Для правильного отображения в документе названия рисунка необходимо: 1. Щелкнуть правой клавишей по рисунку, из контекстного меню выбрать Вставить название 2. В диалоговом окне дописать название рисунка и нажать ОК.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

5. ДОБАВЛЕНИЕ НАЗВАНИЙ К РИСУНКАМ Названия используются, для того чтобы подписать объект (рисунок, главу, текст, диаграмму, пр.), создавать текстовые ссылки на эти названия, изменяемые автоматически и в дальнейшем ссылаться на него. Название представляет собой текст, состоящий из 3 частей: тип, номер, описание. Например: «Рис. 1 Вставить название». Для того, чтобы перемещать название объекта вместе с объектом, следует вставить объект и название общую область. 1. Для создания названия к объекту необходимо поместите курсор в область с объектом, выбрать команду: ССЫЛКИ → ВСТАВИТЬ НАЗВАНИЕ.

2. В диалоговом окне выберите подпись, которая будет использоваться для всех объектов этого типа, например Рисунок → кн. ОК (на рис. пункт 2, 4). После этого отобразится надпись в графе название (на рис. пункт 1). В текстовом поле НАЗВАНИЕ можно ввести текс к создаваемому рисунку: например, «Рисунок 1 Вставить название».

3. При создании следующие объектов и добавления названий, нумерация идёт в той последовательности, в которой они были созданы. Для того чтобы объекты были пронумерованы в той последовательности, в которой они расположены в документе: ССЫЛКИ → ВСТАВИТЬ НАЗВАНИЕ → в диалоговом окне выбрать НУМЕРАЦИЯ… (на рис. выше пункт 3) → в открывшемся окне НУМЕРАЦИЯ НАЗВАНИЙ выберете вид нумерации → кн. ОК.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

После этого названия автоматически переименуются в той последовательности, в которой они находятся в документе. 4. Для рисунков необходимо установить расположения название ПОД ВЫДЕЛЕННЫМ ОБЪЕКТОМ

5. Для обновления нумерации в объектах, выделить нужные объекты и нажать функциональную клавишу F9. Для обновления нумерации во всём документе, следует выделить весь текст. 6. После добавления подписей ко всем рисункам документа, необходимо добавить их перечень. Для этого используется перекрёстная ссылка ССЫЛКИ → ПЕРЕКРЕСТНАЯ ССЫЛКА.

7. В диалоговом окне выбрать тип названия, на которое создается ссылка ТИП ССЫЛКИ → ВСТАВИТЬ ССЫЛКУ НА → указать объект ДЛЯ КАКОГО НАЗВАНИЯ → кн. ВСТАВИТЬ.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

8. Для обновления нумерации пронумерованных рисунков, выделить их и нажать функциональную клавишу F9. Для обновления нумерации во всём тексте, следует выделить весь текст документа. Важно!!! Если в названиях ваших объектов не будет описания, т.е. они будут выглядеть как «Рис. №», то ссылки не будут обновляться при нажатии F9. Чтобы решить эту проблему добавьте в конец названия каждого объекта пробел: «Рис. №_» 6. ДОБАВЛЕНИЕ НАЗВАНИЙ К ТАБЛИЦАМ Для нумерации таблиц в документе: 1. Click в таблице вкл. ССЫЛКИ → гр. НАЗВАНИЯ → кн. ВСТАВИТЬ НАЗВАНИЕ 2. Для установки неизменной части подписи: в диалоговом окне НАЗВАНИЕ → кн. СОЗДАТЬ → диалоговом окне НОВОЕ НАЗВАНИЕ → ввести Табл.3.

3. Для таблиц установить расположение НАД ВЫДЕЛЕННЫМ ОБЪЕКТОМ → кн. ОК 23

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

Для вставки ссылок на таблицу в документе: click в тексте → вкл. ССЫЛКИ → гр. НАЗВАНИЯ → кн. ПЕРЕКРЕСТНАЯ ССЫЛКА

Результат:

Для вставки списка таблиц документа: 4. click в начале (конце) документа: вкл. ССЫЛКИ → гр. НАЗВАНИЕ → кн. СПИСОК ИЛЛЮСТРАЦИЙ 5. в сп. НАЗВАНИЕ → выбрать ТАБЛ. 3, указать, нужно ли выводить название таблицы, пересмотреть другие параметры → кн. ОК

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Результат:

Для обновления нумерации в документе ссылок и списка таблиц: выделить весь текст (CTRL+A) → кл. F9

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

7. ОСНОВНЫЕ НЕПЕЧАТАЕМЫЕ СИМВОЛЫ В MS Word 2016 существуют различные непечатаемые символы. Большая часть из них приведена в таблице. Непечатаемый символ

Название символа

Описание

Способ вставки символа

Пробел

Создает пробел между символами.

Клавиша Пробел на клавиатуре

Табуляция

Создает отступ

Клавиша Tab

Конец абзаца

Завершает абзац

Клавиша Enter

Переносит на следующую строку не Сочетание клавиш Shift+Enter создавая абзац. Создает пробел между символами, который Неразрывный пробел нельзя использовать, чтобы начать новую Сочетание клавиш Ctrl+Shift+Пробел строку. Разрыв строки

Разрыв страницы

Перенос курсора на следующую страницу

Сочетание клавиш Ctrl+ Enter

Разрыв раздела

Перенос курсора на следующую страницу и создание раздела в документе

Вкладка «Разметка страницы» --> Группа «Параметры страницы» --> пункт «Разрывы»

Разрыв колонки

Перенос курсора в следующую колонку

Сочетание клавиш Ctrl+Shift+Enter

Конец текста в ячейке Обозначает конец ячейки таблицы

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

✓ Символ пробела. На изображении, представленном ниже, каждая точка обозначает символ пробела между словами, то есть одно нажатие на клавишу пробел это одна точка.

✓ Символ табуляции →. Нажатие клавиши табуляции (Tab) отображается символом стрелки, направленной вправо:

✓ Символ конец абзаца. После нажатия клавиши Enter перемещается курсор на новую строку и начинает новый абзац. Конец абзаца отображается символом ¶.

✓ Символ разрыва строки. Для вставки разрыва строки используются сочетание клавиш Shift+Enter. При вставке разрыва строки происходит

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

✓ Неразрывный пробел. Неразрывный пробел используется когда желаемый текст не должен быть разорван переносом на новую строку. Наглядный пример представлен ниже.

✓ Символ разрыва страницы. Символ разрыва страницы обозначает перенос текста на следующую страницу. Для вставки разрыва страницы используйте сочетание клавиш Ctrl+Enter.

Это очень удобный способ, если необходимо начать текст с новой страницы. Хотя, к сожалению, многие пользователи не используют этот инструмент, а просто многократно нажимают клавишу Enter. 28

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

✓ Символ разрыв колонки. При использовании колонок ворд автоматически делит текст на приблизительно равные части. Но бывают случаи, когда необходимо по собственному усмотрению разбить текст в колонках. Тогда используйте сочетание клавиш Ctrl+Shift+Enter.

✓ Символ конца текста в ячейке таблицы. Данный символ означает конец текста в ячейке таблицы. С помощью данного символа вы также можете ориентироваться в форматировании текста в ячейке. 29

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

✓ Черный квадрат. Данный символ означает, что к тексту применен хотя бы один из параметров абзаца.

✓ Символ привязки объектов работает для таких объектов как изображения, автофигуры и диаграммы, показывает, где находится объект. Данный символ отображается только тогда, когда режим обтекания любой, кроме режима В ТЕКСТЕ.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Включить режим отображения непечатаемых символов: ГЛАВНАЯ → гр. АБЗАЦ → кн. ОТОБРАЗИТЬ ВСЕ ЗНАКИ 8. СОЗДАНИЕ ЗАПОЛНЯЕМОЙ ФОРМЫ Чтобы создать в Word форму, которую смогут заполнять другие люди, начните с шаблона и добавьте к нему элементы управления содержимым. К таким элементам относятся: ✓ флажки, ✓ текстовые поля, ✓ элементы выбора даты ✓ раскрывающиеся списки. Создание заполняемых форм начинается с шести основных шагов. Шаг 1. Отображение вкладки "Разработчик". 1. Щелкните вкладку Файл. 2. Выберите пункт Параметры. 3. Выберите пункт Настроить ленту. 4. В разделе Настроить ленту установите в списке Основные вкладки флажок Разработчик и нажмите кнопку ОК.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Шаг 2. Открытие шаблона или документа, на основе которого будет создана форма. Чтобы сэкономить время, за основу для формы можно взять шаблон. Если хотите начать с нуля, возьмите пустой шаблон. Создание формы на основе шаблона 1. Щелкните вкладку Файл. 2. Щелкните Создать. 3. В поле Поиск шаблонов в сети введите тип создаваемого содержимого и нажмите ВВОД. 4. Щелкните шаблон формы, который хотите использовать, а затем нажмите кнопку Создать. Использование пустого шаблона 1. Щелкните вкладку Файл. 2. Нажмите кнопку Создать. 3. Выберите Новый документ.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Шаг 3. Добавление содержимого в форму На вкладке Разработчик нажмите кнопку Режим конструктора и вставьте нужные вам элементы управления.

Примечание: Форму с элементами управления содержимым можно распечатать, однако поля вокруг них напечатаны не будут. Вставка элемента управления "стандартный блок" Используйте стандартные блоки, когда хотите предоставить пользователям возможность выбрать определенный блок текста. Например, эти элементы управления полезны, когда создается шаблон договора, в котором в зависимости от конкретных требований нужно добавить разные варианты стандартного текста. Вы можете создать для каждого варианта элемент управления содержимым "форматированный текст" и поместить их все в элемент управления "стандартный блок", который будет служить контейнером. Элементы управления "стандартный блок" также можно использовать в формах. 1. Щелкните место, куда нужно добавить элемент управления. 2. На вкладке Разработчик в группе Элементы управления выберите следующий элемент управления содержимым:

Чтобы задать свойства элемента управления, см. Шаг 4. Установка или изменение параметров элементов управления содержимым. Вставка элемента управления текстом, куда пользователи могут вводить текст В элементе управления содержимым "форматированный текст" пользователи могут выделять текст полужирным шрифтом или курсивом, а также вводить несколько абзацев текста. Чтобы ограничить возможности пользователей, вставьте элемент управления содержимым "обычный текст". 1. Щелкните в том месте, где хотите вставить элемент управления. 33

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. На вкладке Разработчик щелкните Элемент управления содержимым "форматированный текст" или Элемент управления содержимым "обычный текст" . Чтобы задать свойства элемента управления, см. Шаг 4. Установка или изменение параметров элементов управления содержимым. Вставка элемента управления "рисунок". Элемент управления "рисунок" часто используется в шаблонах, однако его можно добавить и в форму. 1. Щелкните в том месте, где хотите вставить элемент управления. 2. На вкладке Разработчик щелкните Элемент управления содержимым "рисунок". Чтобы задать свойства элемента управления, см. Шаг 4. Установка или изменение параметров элементов управления содержимым. Вставка поля со списком или раскрывающегося списка В поле со списком пользователи могут выбрать один из предложенных вами пунктов или ввести собственный вариант. В раскрывающемся списке пользователи могут только выбрать один из имеющихся пунктов. 1. На вкладке Разработчик щелкните Элемент управления содержимым "поле со списком" или Элемент управления содержимым "раскрывающийся список" . 2. Выделите элемент управления содержимым, а затем на вкладке Разработчик щелкните Свойства. 3. Чтобы создать список вариантов, щелкните Добавить в разделе Свойства раскрывающегося списка. 4. Введите значение в поле Отображаемое имя, например Да, Нет или Возможно. Повторяйте этот шаг до тех пор, пока все нужные вам значения не окажутся в раскрывающемся списке. 5. При необходимости задайте остальные свойства. Примечание: Если установить флажок Содержимое нельзя редактировать, пользователи не смогут изменять выбранные пункты. Вставка элемента управления "выбор даты" 1. Щелкните в том месте, где нужно вставить элемент управления "выбор даты". 2. На вкладке Разработчик щелкните Элемент управления содержимым "выбор даты". Чтобы задать свойства элемента управления, см. Шаг 4. Установка или изменение параметров элементов управления содержимым. Вставка флажка 1. Щелкните в том месте, где нужно вставить элемент управления "флажок". 34

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. На вкладке Разработчик щелкните Элемент управления содержимым "флажок". Чтобы задать свойства элемента управления, см. Шаг 4. Установка или изменение параметров элементов управления содержимым. Шаг 4. Установка или изменение параметров элементов управления содержимым У каждого элемента управления содержимым есть параметры, которые можно установить или изменить. Например, для элемента управления "Выбор даты" вы можете выбрать различные форматы отображения даты. 1. Щелкните элемент управления содержимым, который вы хотите изменить. 2. На вкладке Разработчик щелкните Свойства и измените свойства нужным вам образом. Шаг 5. Добавление в форму пояснительного текста Пояснительный текст может сделать форму, которую вы создаете и распространяете, более удобной в использовании. Пояснительный текст, который показывается по умолчанию в элементе управления содержимым, можно изменить. Чтобы настроить пояснительный текст по умолчанию для пользователей формы, выполните одно из следующих действий. 1. На вкладке Разработчик нажмите кнопку Режим конструктора. 2. Щелкните элемент управления содержимым, у которого вы хотите изменить замещающий пояснительный текст. 3. Отредактируйте и отформатируйте замещающий текст. 4. На вкладке Разработчик нажмите кнопку Режим конструктора, чтобы отключить возможность конструирования и сохранить пояснительный текст. Примечание: Не устанавливайте флажок Содержимое нельзя редактировать, если хотите, чтобы пользователи формы заменяли пояснительный текст своим собственным. Шаг 6. Защита формы Если вы хотите ограничить возможности редактирования или форматирования формы пользователями, воспользуйтесь командой Ограничить редактирование. 1. Откройте форму, которую хотите заблокировать или защитить. 2. Щелкните Главная > Выделить > Выделить все или нажмите сочетание клавиш CTRL+A. 3. Щелкните Разработчик > Ограничить редактирование. Совет: При желании вы можете проверить форму, перед тем как распространять ее. Откройте и заполните форму, а затем сохраните ее копию в нужном месте.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

9. СОЗДАНИЕ И ЗАПУСК МАКРОСА В Word можно автоматизировать часто выполняемые задачи с помощью макросов. Макрос — это набор команд и инструкций, группируемых вместе в виде единой команды для автоматического выполнения задачи. Чтобы сэкономить время при выполнении часто повторяющихся задач, объедините несколько действий в макрос. Сначала необходимо записать макрос. Затем вы сможете запускать его нажатием кнопки на панели быстрого доступа или с помощью сочетания клавиш. Способ запуска зависит от того, как вы настроите макрос. Запись макроса, который запускается с помощью кнопки 1. В меню Вид последовательно выберите пункты Макросы и Записать макрос.

2. Введите имя макроса.

3. Чтобы использовать этот макрос во всех новых документах, проверьте, что в поле Сохранить изменения в указано значение Всех документов (Normal.dotm).

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

4. Чтобы запускать макрос нажатием кнопки, выберите пункт Кнопка.

5. Щелкните новый макрос (у него будет имя примерно следующего вида: Normal.NewMacros.<имя вашего макроса>), а затем нажмите кнопку Добавить.

6. Нажмите кнопку Изменить.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

7. Выберите изображение для кнопки, введите нужное имя и дважды нажмите OK.

8. Теперь необходимо записать все шаги макроса. Выбирайте команды или нажимайте клавиши для каждого шага задачи. Word будет записывать все, что вы щелкаете мышью, и все клавиши, которые вы нажимаете. Примечание: Во время записи макроса для выделения текста используйте клавиатуру. Макрос не записывает выделения, сделанные с помощью мыши. 9. Чтобы остановить запись, в меню Вид последовательно выберите пункты Макросы и Остановить запись.

На панели быстрого доступа появится кнопка для вашего макроса.

Чтобы запустить макрос, нажмите эту кнопку. Создание макроса, который запускается с помощью сочетания клавиш 1. В меню Вид последовательно выберите пункты Макросы и Записать макрос.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. Введите имя макроса.

4. Чтобы запускать макрос с помощью сочетания клавиш, выберите пункт Клавиатура.

5. Введите сочетание клавиш в поле Новое сочетание клавиш. 6. Проверьте, не назначено ли такое же сочетание клавиш какой-либо другой команде. Если такое сочетание клавиш назначено какой-либо другой команде, то попробуйте использовать другое сочетание клавиш. 7. Чтобы использовать этот макрос во всех новых документах, проверьте, что в поле Сохранить в указано значение Normal.dotm. 8. Нажмите кнопку Назначить. 9. Теперь необходимо записать все шаги макроса. Выбирайте команды или нажимайте клавиши для каждого шага задачи. Word будет записывать все, что вы щелкаете мышью, и все клавиши, которые вы нажимаете. 39

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Примечание: Во время записи макроса для выделения текста используйте клавиатуру. Макрос не записывает выделения, сделанные с помощью мыши. 10.Чтобы остановить запись, в меню Вид последовательно выберите пункты Макросы и Остановить запись.

Чтобы запустить макрос, нажмите сочетание клавиш. Запуск макроса Чтобы запустить макрос, нажмите кнопку на панели быстрого доступа, нажмите сочетание клавиш или запустите макрос из списка Макросы. 1. В меню Вид последовательно выберите пункты Макросы и Просмотр макросов.

2. В пункте Имя макроса выберите в списке макрос, который вы хотите запустить. 3. Нажмите кнопку Запустить. Как сделать макрос доступным во всех документах Чтобы сделать макрос в одном документе доступным во всех новых документах, добавьте его в шаблон Normal.dotm. 1. Откройте документ, в котором содержится макрос. 2. В меню Вид последовательно выберите пункты Макросы и Просмотр макросов.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

3. Нажмите кнопку Организатор.

4. Щелкните макрос, который вы хотите добавить в шаблон Normal.dotm, а затем нажмите кнопку Копировать. Добавление кнопки макроса на ленту 1. В меню Файл последовательно выберите пункты Параметры и Настроить ленту. 2. В пункте Выбрать команды из выберите пункт Макросы. 3. Выберите нужный макрос. 4. В пункте Настройка ленты выберите вкладку и настраиваемую группу, в которую вы хотите добавить макрос. Если у вас нет настраиваемой группы, то нажмите кнопку Новая группа. Затем нажмите кнопку Переименовать и введите имя настраиваемой группы. 1. Нажмите кнопку Добавить. 2. Чтобы изменить изображение для макроса и ввести нужное имя, нажмите кнопку Переименовать. 3. Дважды нажмите кнопку OK. Создание макроса с нуля в Visual Basic 1. На вкладке Разработчик в группе Код нажмите кнопку Макросы. 2. В поле Имя макроса введите имя нового макроса. Примечание: Если новому макросу присвоить то же самое имя, что и макросу, встроенному в Word, новые макрокоманды будут выполняться 41

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

вместо встроенных. Чтобы просмотреть список встроенных макросов, выберите пункт Команды Word в списке Макросы из. 3. В списке Макросы из щелкните шаблон или документ, в котором нужно сохранить макрос. Чтобы макрос был доступен во всех документах, выберите Normal.dotm. 4. Нажмите кнопку Создать, чтобы вызвать редактор Visual Basic. После запуска редактора Visual Basic могут потребоваться дополнительные сведения о работе с языком Visual Basic для приложений. Для получения сведений выберите в меню Help (Справка) пункт Microsoft Visual Basic Help (Справка по Microsoft Visual Basic) или нажмите клавишу F1.

ТЕСТ 1. MICROSOFT WORD 2016 1

Как создать связь между файлом с рассылкой и базой данных? вкл. РАССЫЛКИ - гр. НАЧАТЬ СЛИЯНИЕ - кн. НАЧАТЬ СЛИЯНИЕ ПОШАГОВЫЙ МАСТЕР СЛИЯНИЯ вкл. РАССЫЛКИ - гр. СОСТАВЛЕНИЕ ДОКУМЕНТА И ВСТАВКА ПОЛЕЙ - кн. ДОБАВИТЬ ПОЛЕ СЛИЯНИЯ вкл. РАССЫЛКИ - гр. ПРОСМОТР РЕЗУЛЬТАТОВ - кн. ПОСМОТРЕТЬ РЕЗУЛЬТАТЫ 2

Таблица со списком клиентов для рассылки должна удовлетворять следующие условия: В таблице не должно быть объединенных ячеек В таблице не должно быть пустых строк или столбцов В таблице должны быть пустые ячейки 3

Как добавить в документ поле слияния с базой данных? вкл. РАССЫЛКИ - гр. НАЧАТЬ СЛИЯНИЕ - кн. НАЧАТЬ СЛИЯНИЕ ПОШАГОВЫЙ МАСТЕР СЛИЯНИЯ вкл. РАССЫЛКИ - гр. СОСТАВЛЕНИЕ ДОКУМЕНТА И ВСТАВКА ПОЛЕЙ - кн. ДОБАВИТЬ ПОЛЕ СЛИЯНИЯ вкл. РАССЫЛКИ - гр. ПРОСМОТР РЕЗУЛЬТАТОВ - кн. ПОСМОТРЕТЬ РЕЗУЛЬТАТЫ 4

Для создания оглавление необходимо выполнить следующие действия: оформить заголовки текста стилями всех уровней сформировать оглавление: ССЫЛКИ- ОГЛАВЛЕНИЕ - кн. ОК отобразить содержание: ВИД - гр. ПОКАЗАТЬ - кн. ОБЛАСТЬ НАВИГАЦИИ 42

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль 5

Укажите основные этапы формирования списка иллюстраций? Установить курсор в нужном месте документа Выбрать команду: вкл. ССЫЛКИ - гр. НАЗВАНИЕ - кн. СПИСОК ИЛЛЮСТРАЦИЙ Сформировать список: вкл. ССЫЛКИ - гр. НАЗВАНИЯ - кн. ПЕРЕКРЕСТНАЯ ССЫЛКА 6

Укажите этапы добавления названия к изображению в MS Word? Выделить изображение Выбрать команду: Ссылки - Вставить название. Ввести текст в поле "Название" Пронумеровать изображение

Как добавить кнопку макроса на ленту? В меню Файл последовательно выберите пункты Параметры - Настроить ленту В пункте Выбрать команды из... выберите пункт Макросы Выбрать нужный макрос и добавить его на выбранную вкладку в нужную группу На вкладке Разработчик в группе Код нажмите кнопку Макросы 8

Какие основные этапы выполнения макроса в MS Word? Вид - пункт Макросы В пункте Имя макроса выбрать в списке макрос Нажать кнопку Организатор Нажать кнопку Запустить

Какие элементы управления можно добавить в шаблон заполняемой формы? Форматированный текст Рисунок Поле со списком Раскрывающийся список Выбор даты Флажок Полоса прокрутки Кнопка 43

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль 10

Полный цикл формирования рассылки состоит из следующих этапов: Выбор типа документа Выбор документа Выбор получателей Создание письма Просмотр писем Завершение слияния Печать документа ТЕМА 2. ОСНОВЫ VBA 1. ЗАПИСЬ МАКРОСА В EXCEL Предположим, нужно часто применять полужирный шрифт Arial 12-го размера в качестве стиля форматирования символов ячеек рабочих листов, к которым надо привлечь особое внимание. С целью сокращения времени, необходимого для форматирования текста, можно записать макрос, который выбирает полужирный шрифт Arial 12-го размера и применяет это форматирование к любой ячейке или диапазону ячеек текущего выделенного фрагмента. Задание стартовых условий Т.к. необходимо, чтобы макрос работал с любой выделенной ячейкой или диапазоном ячеек, стартовыми условиями для этого макроса являются открытая рабочая книга с выделенным диапазоном ячеек в активном рабочем листе. Назначение имени и сохранение макроса Для запуска макрорекордера в Excel, назначения имени макросу, выбора места для сохранения нового макроса и выбора дополнительных опций нужно выполнить следующее: • Выбрать команду "Сервис-Макрос-Начать запись"; • В текстовом окне "Имя макроса" в качестве имени макроса ввести ArialBold12. Такое имя позволит запомнить, что выполняет макрос; • В поле "Описание" добавить следующий текст: «Форматирует выделенные ячейки Arial, Bold, 12»; • Список "Сохранить в…" следует использовать для выбора места, в котором будет сохранен записанный макрос. Доступными вариантами являются: "Личная книга макросов", "Новая книга", "Эта книга". Поскольку необходимо, чтобы этот макрос был доступен во всех рабочих книгах, надо выбрать "Личная книга макросов"; 44

Бизнес-информатика: дистанционный курс

• •

Т. Н. Кисиль

Если будущий макрос планируется использовать довольно часто, можно назначить для его запуска горячую клавишу; Для начала записи макроса необходимо нажать кнопку "ОК". Запись действий

О процессе записи макроса свидетельствует наличие панели "Остановить запись", при этом, в левом нижнем углу окна книги Excel появляется надпись "Запись".

По умолчанию панель "Остановить запись" в Excel содержит две командные кнопки. Левая кнопка - кнопка "Стоп"; служит для завершения процесса записи макроса. Правая кнопка - кнопка "Относительная ссылка". По умолчанию Excel записывает абсолютные ссылки на ячейки в макросы. Если, например, начать запись в выделенной ячейке А5, а затем выделить ячейку справа от А5, т.е. В5, то записанный макрос также будет выделять ячейку В5. Для записи указанного макроса необходимо выполнить следующие действия (после нажатия кнопки "ОК" в окне "Запись макроса"): • Из выпадающего списка "Шрифт" панели "Форматирование" выбрать шрифт Arial; • Из выпадающего списка "Размер шрифта" той же панели выбрать 12-й кегль; • Нажать кнопку "Ж" выбора начертания шрифта. 45

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

После этого макрорекордер необходимо остановить, нажав кнопку "Стоп" на панели "Остановить запись". Либо выбрать команду "Сервис-МакросОстановить запись"). Новый макрос в Excel создан. Выполнение макроса После того, как макрос записан, его можно выполнить. При выполнении макроса Word или Excel следуют всем инструкциям, записанным в макросе. Первый метод: И в Word, и в Excel макрос запускается выбором команд меню РАЗРАБОТЧИК - МАКРОС и в открывшемся окне выбрать макрос и нажать кнопку ВЫПОЛНИТЬ.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Диалоговые окна для запуска макросов в Word и Excel практически идентичны - надо выбрать название макроса и щелкнуть кнопку "Выполнить". Следует помнить, что перед тем, как запустить макрос на выполнение, должны быть выполнены стартовые условия. В нашем случае - выделен фрагмент текста в Word или блок ячеек в Excel. В Word диалоговое окно "Макрос" перечисляет макросы, сохраненные в текущем документе или в любых общих шаблонах. Если затрудняетесь найти нужный макрос в полном списке доступных макросов - следует использовать список "Макросы" для выбора определенного документа или шаблона. Диалоговое окно "Макрос" в Excel перечисляет макросы, сохраненные в любых рабочих книгах, открытых в данный момент. Имя рабочей книги, содержащей макрос, помещено перед именем макроса в списке "Имя макроса", если макрос не находится в текущей рабочей книге. Если необходимый макрос не находится в списке, откройте рабочую книгу, в которой был сохранен этот макрос, чтобы сделать макрос доступным перед тем, как будет открыто диалоговое окно "Макрос". Если в процессе записи макроса ему были присвоены "горячие" клавиши или кнопки на панели инструментов, то данный макрос может быть запущен нажатием соответствующей комбинации клавиш или соответствующей кнопкой на панели инструментов. Второй метод: Создать кнопку на рабочем листе для выполнения макроса, выбрав меню РАЗРАБОТЧИК - ВСТАВИТЬ, выбрать элемент КНОПКА, растянуть рамку кнопки на рабочем листе, отпустить левую кнопку мыши, выбрать нужный макрос и написать на кнопке название макроса.

Для редактирования названия, в замене макроса и удаление кнопки нажать на кнопке правую кнопку мыши и выбрать нужную команду:

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Код макроса При записи макроса в Word или Excel рекордер сохраняет последовательность текстовых инструкций, которые описывают на языке программирования VBA различные действия, выполняемые пользователем, когда рекордер включен. Это текстовое описание команд называется исходным кодом для этого макроса. Позже, когда макрос запускается на выполнение, VBA считывает записанные в исходном коде инструкции и выполняет каждую последовательно, дублируя таким образом действия, которые выполнялись пользователем при записи макроса. Ниже приведен исходный код макросов "ВыделенныйТекст" (Word) и "ArialBold12" (Excel).

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. ФУНКЦИИ-ПРОЦЕДУРЫ VBA Функция-процедура - это особый вид процедуры VBA, возвращающей результат. Пользовательские функции-процедуры, как и встроенные функции VBA, могут иметь необязательные и именованные аргументы. Для записи функции-процедуры нельзя использовать макрорекордер, хотя можно редактировать записанный рекордером макрос и превращать его в функциюпроцедуру. Основное различие между функцией - процедурой и другими процедурами, помимо того, что функции возвращают значение, а процедуры - нет, состоит в том, что в функции-процедуре используются ключевые слова Function и End Function. Синтаксис:

⎯ ⎯

- ключевое слово, объявляющее начало функции. - имя функции. Имена функций следуют тем же правилам, что и имена других идентификаторов VBA. ⎯ - список аргументов данной функции, необязательный элемент. ⎯ - тип возвращаемого значения функции. Если тип не определен, результат, который возвращает функция-процедура, имеет тип Variant. ⎯ - присваивание функции, которое указывает VBA, какое значение должна возвращать функция, необязательный элемент. Всегда следует включать оператор присваивания в функции-процедуры. ⎯ - ключевые слова, заканчивающие функцию. Даже если функция не имеет аргументов (например, Now, Date) в объявлении функции необходимо использовать круглые скобки. 49

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Обычно функция предназначается для выполнения вычисления и для возвращения результата. При объявлении функции-процедуры указывается имя каждого аргумента, передаваемого функции. Имена аргументов в списке отделяются друг от друга запятой и должны следовать правилам, применяемым к любому идентификатору VBA. Например, необходимо создать функцию пользователя для вычисления значений:

Решение: 1. В редакторе Visual Basic добавить модуль: Insert – Module.

2. Создать функцию пользователя (с именем Y) следующего вида:

3. На Лист1, в ячейке D2 применить созданную функцию пользователя: =y($A$2;$B$2;C2). Или же используем мастер функций: Формулы – Вставить функцию – категория Определенные пользователем – в диалоговом окне указываем аргументы функций

4. Скопировать созданную функцию в столбце D. 50

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Арифметические операторы VBA Оператор присваивания (=) Оператор присваивания используется для присваивания результата выражения переменной. Операция присваивания имеет две синтаксические формы:

- любая переменная VBA - любое выражение VBA Первый вариант операции присваивания использовался в ранних языках программирования Basic. Второй вариант используется в современной версии VBA. При выполнении оператора присваивания VBA сначала вычисляет выражение справа от оператора присваивания, а затем сохраняет результат выражения в переменной, имя которой находится слева от знака равенства. X = 5 + 7; Y = X + 5 ; Z = X - Y; A = B; I = I + 1 Начинающим пользователям иногда непонятен смысл последней операции присваивания, когда и в левой, и в правой частях операции стоит одна и та же переменная. В этом случае сначала в промежуточную ячейку памяти помещается результат вычисления выражения правой части оператора присваивания, а затем этот результат присваивается переменной в левой части. Например, если в операторе присваивания А = А +5, переменная А до операции присваивания содержала значение 7, то после операции она будет содержать значение 12 (7+5). Следует запомнить: • Можно присваивать любую численную переменную (или выражение) любой другой переменной численного типа (или переменной типа Variant); • Если присваивается численное выражение типизированной переменной с меньшей точностью (например, Double - Long), VBA округляет значение выражения для совпадения с точностью переменной, принимающей новое значение; 51

Бизнес-информатика: дистанционный курс •

Т. Н. Кисиль

Если переменной типа String присваивается переменная типа Variant, содержащая число, VBA автоматически преобразует это число в строку.

Оператор сложения (+) Оператор сложения выполняет простое сложение. Оба операнда должны быть численными выражениями или строками, которые VBA может преобразовать в число. Оператор сложения можно также использовать для выполнения арифметических операций с данными типа Date. Тип данных результата выражения сложения обычно тот же, что и наиболее точный тип в этом выражении. Но, есть исключения: • Результатом сложения типа Single и Long будет Double; • Результатом сложения типа Date с любым другим типом данных всегда будет Date; • Если результат превышает диапазон типа Integer, то VBA преобразует его в Long; • Если результат превышает типы Long, Single, Date, то VBA преобразует его в Double; • Если любой операнд в выражении сложения является Null, то результатом выражения сложения также будет Null. Порядок увеличения точности для численных типов данных: Byte, Integer, Long, Single, Double, Currency. Оператор вычитания (-) Оператор вычитания выполняет две задачи: используется для вычитания одного числа из другого; обозначает унарный минус (это знак минус, который помещается перед числом для указания того, что это отрицательное число). Поместить унарный минус перед переменной или выражением означает то же, что умножить это число на -1. Оба операнда в выражении вычитания должны быть численными переменными (выражениями) или строковыми выражениями, которое VBA может преобразовать в число. Можно использовать оператор вычитания для работы с датами. VBA использует те же правила для определения типа данных результата выражения вычитания, что и для выражений, использующих оператор сложения. Но, есть дополнение: если оба операнда в выражении являются типом Date, то результат выражения будет иметь тип Double. Оператор умножения (*) Оператор умножения перемножает два числа - результатом выражения умножения является произведение двух операндов. Оба операнда в выражении умножения должны быть численными выражениями или строками, которые VBA может преобразовать в число. VBA следует тем же правилам для определения типа данных результата выражения умножения, что и для выражений, использующих оператор сложения. 52

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

В выражениях умножения все переменные Variant, которые содержат значения типа Date, преобразуются в численные значения. Оператор деления (/) Оператор деления с плавающей точкой выполняет обычное арифметическое деление своих операндов. В выражениях деления первый операнд делится на второй операнд - результатом деления является частное. Оба операнда в выражении деления с плавающей точкой должны быть численными выражениями или строками, которые VBA может преобразовать в число. Если хотя бы один операнд в выражении деления имеет тип Null, то результат деления также будет Null. Типом данных операции деления с плавающей точкой является Double, за исключением: • Оба операнда в выражении деления имеют тип Integer или Single - результат Single; • Если результат выражения не переполняет диапазон значений для типа Single. Целочисленное деление (\) Целочисленное деление отличается от деления с плавающей точкой тем, что его результатом всегда есть целое число без дробной части. Оба операнда в выражении целочисленного деления должны быть численными выражениями или строками, которые VBA может преобразовать в число. Перед выполнением операции целочисленного деления VBA округляет каждый операнд до числа типа Integer или Long (такой же тип имеет и результат целочисленного деления). VBA отбрасывает (но не округляет!) любой дробный остаток результата выражения целочисленного деления. Например, выражения 22\5 и 24\5 будут иметь один и тот же результат = 4. Если хотя бы один операнд в выражении целочисленного деления имеет тип Null, то результат деления также будет Null. Деление по модулю (Mod) Деление по модулю как бы дополняет целочисленное деление. В делении по модулю выражение возвращает только остаток операции деления как целое. 22 Mod 5 = 2 24 Mod 5 = 4 25 Mod 5 = 0 Остальные свойства деления по модулю идентичны целочисленному делению. Возведение в степень (^) Оператор возведения в степень возводит число в степень. 5 ^ 3 =125 Оба оператора в выражении возведения в степень должны быть численными выражениями или строками, которые VBA может преобразовать в числа. 53

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Операнд слева от оператора возведения в степень может быть отрицательным числом только, если операнд справа является целым. Результат выражения имеет тип Double. Если хотя бы один операнд в выражении имеет тип Null, то результат возведения в степень также будет Null. Арифметические операторы VBA Оператор

Синтаксис

Описание

A+B

Сложение: складывает А и В.

A-B

Вычитание: вычитает из А В.

A*B

Умножение: перемножает А на В.

A/B

Деление: делит А на В.

A\B

Целочисленное деление: делит А на В, отбрасывая дробную часть. Результат целое число.

Mod

A Mod B

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

A^B

Возведение в степень: возводит А в степень В.

Логические операторы VBA В качестве операнда для логического оператора можно использовать любое действительное выражение, имеющее результат типа Boolean, а также число, которое может быть преобразовано в значение типа Boolean. Результатом логической операции является значение типа Boolean (или Null, если хотя бы один из операндов имеет значение Null). Оператор

Синтаксис

Описание

A AND B

Конъюнкция: Если А и В имеют значение True, то - True. Иначе False

A OR B

Дизъюнкция: Если любой из операндов имеет значение True, то - True. Иначе - False

NOT

NOT A

Отрицание: Если А имеет значение False, то - True. Иначе - False

XOR

A XOR B

Исключение: Если А имеет значение True или В имеет

AND

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

значение True, то - True. Иначе False EQV

IMP

A EQV B

Эквивалентность: Если А имеет такое же значение что и В, то True. Иначе - False

A IMP B

Импликация: Если А имеет значение True и В имеет значение False, то - False. Иначе - True

Логический оператор AND Синтаксис: Оператор AND выполняет логическую конъюнкцию. Результатом данной операции является значение True, только когда оба операнда имеют значение True, иначе - False. Таблица истинности Операнд_1 Операнд_2 Результат True

True

False

True

False

False False False Оператор AND можно использовать для нескольких операндов: (5<7) AND (4>3) AND (5=6) результатом будет False Независимо от количества операндов результатом логической операции AND будет True только в том случае, когда все операнды выражения будут иметь значение True. В любом другом случае результатом будет False. Операнды заключаются в круглые скобки, VBA сначала вычисляет значение каждого операнда внутри скобок, а затем уже все выражение полностью. Логический оператор OR Синтаксис: Оператор OR выполняет логическую дизъюнкцию. Результатом данной операции является значение True, если хотя бы один из операндов имеет значение True, иначе - False. Таблица истинности Операнд_1 Операнд_2 Результат True

True 55

True

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

True

False

True

False

True

False False False Оператор OR можно использовать для нескольких операндов: (5<7) OR (4>3) OR (5=6) результатом будет True Независимо от количества операндов результатом логической операции OR будет всегда True в том случае, если хотя бы один из операндов выражения будет иметь значение True. Иначе результатом будет False. Операторы AND и OR можно комбинировать: ((5<7) AND (4>3)) OR (5=6) результатом будет True Логический оператор NOT Синтаксис: Оператор NOT выполняет логическое отрицание. Оператор NOT использует только один операнд. Таблица истинности Операнд Результат True

False

False True Операторы AND OR NOT можно комбинировать: ((5<7) AND (4>3)) OR NOT (5=6) результатом будет True Логический оператор XOR Синтаксис: Оператор XOR выполняет логическое исключение. Результатом данной операции является значение True, если операнды имеют разные значения, иначе False. Таблица истинности Операнд_1 Операнд_2 Результат True

True

False

True

False

True

False

True

False False False ((5<7) AND (4>3)) OR NOT (5=6) XOR (5=5) результатом будет False Логический оператор EQV

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Синтаксис: Оператор EQV - это оператор логической эквивалентности. Результатом данной операции является значение True, если операнды имеют одинаковые значения, иначе - False. Таблица истинности Операнд_1 Операнд_2 Результат True

True

False

True

False

False False True ((5<7) AND (4>3)) OR NOT (5=6) EQV (5=5) результатом будет True Логический оператор IMP Синтаксис: Оператор IMP выполняет логическую операцию импликации. Таблица истинности Операнд_1 Операнд_2 Результат True

True

False

True

False False True ((5<7) AND (4>3)) OR NOT (5=6) IMP (5=5) результатом будет True Логический оператор IMP наименее интуитивно понятный из всех логических операторов. Но необходимость в его применении возникает довольно редко. Операторы сравнения Оператор

Синтаксис

Описание

A=B

Равенство: Если А равно В, то - True. Иначе - False

A<B

Меньше: Если А меньше В, то - True. Иначе - False

A <= B

Меньше или равно: Если А меньше или равно В, то True. Иначе - False

A>B

Больше: Если А больше В, то - True. Иначе - False

A >= B

Больше или равно: Если А больше или равно В, то True. Иначе - False

A <> B

Не равно: Если А не равно В, то - True. Иначе - False 57

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Операции сравнения используются в операторах организации циклов для принятия какого-либо решения о дальнейшем ходе выполнения операций. Результатом любой операции сравнения является значение типа Boolean: True, False. Если оба операнда в выражении сравнения имеют один и тот же тип данных, VBA выполняет простое сравнение для этого типа. Если оба операнда в выражении сравнения имеют определенные типы и эти типы не являются совместимыми, VBA выдает сообщение об ошибке несовпадения типов. Если один или оба операнда в выражении сравнения являются переменными типа Variant, VBA пытается преобразовать тип Variant в какой-либо совместимый тип. Функции VBA. Ниже представлены таблицы с кратким описанием наиболее часто используемых функций VBA. Математические функции N - означает любое численное выражение. Все аргументы функций являются обязательными, если не указано иначе. Функция (аргумент)

Действие

Abs(N)

Возвращает абсолютное значение аргумента N

Atn(N)

Возвращает арктангенс N (радиан)

Cos(N)

Возвращает косинус N (радиан)

Exp(N)

Возвращает константу е (натуральный логарифм = 2.718282...), возведенную в степень N

Функция (аргумент)

Действие

Fix(N)

Возвращает целую часть N. Не округляет число, а отбрасывает любую дробную часть. Если N отрицательное число, то возвращается ближайшее отрицательное целое большее, чем или равное N

Int(N)

Возвращает целую часть N. Не округляет число, а отбрасывает любую дробную часть. Если N отрицательное число, то возвращается ближайшее отрицательное целое меньшее, чем или равное N

Log(N)

Возвращает натуральный логарифм N

Rnd(N)

Возвращает случайное число: аргумент является необязательным. Используется только после инициализации генератора случайных чисел (оператор Randomize) 58

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Sgn(N)

Возвращает знак числа: -1, если N - отрицательное; 1, если N - положительное; 0, если N равно 0

Sin(N)

Возвращает синус N (радиан)

Sqr(N)

Возвращает корень квадратный из N. Если N является отрицательным числом - возвращается ошибка времени исполнения

Tan(N)

Возвращает тангенс N (радиан)

Функции преобразования данных N - любое численное значение; S - любое строковое значение; E - выражение любого типа. Аргументы каждой функции являются обязательными, если не указано иначе. Функция (аргумент)

Действие

Asc(S)

Возвращает число кода символа, соответствующее первой букве строки S

Chr(N)

Возвращает строку из одного символа, соответствующего коду символа N, который должен быть числом между 0 и 255 (обратна предыдущей)

Format(E,S)

Возвращает строку, содержащую значение, представленное выражением Е, в формате в соответствии с инструкциями, содержащимся в S

Hex(N)

Возвращает строку, содержащую шестнадцатиричное представление N

Функция (аргумент)

Действие

Oct(N)

Возвращает строку, содержащую восьмиричное представление N

RGB(N,N,N)

Возвращает целое типа Long, представляющее значение основных цветов изображения. N для каждого аргумента должно быть целым и лежать в пределах от 0 до 255

Str(N)

Возвращает строку, эквивалентную численному выражению N

Val(S)

Возвращает численное значение, соответствующее числу, представленному строкой S, которая должна содержать только цифры и одну десятичную точку, иначе возвращается 0

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

CBool(N)

Возвращает Boolean-эквивалент численного выражения N

CByte(E)

Возвращает численное значение типа Byte (от 0 до 255). Е - любое допустимое численное или строковое выражение, которое может быть преобразовано в число

CCur(E)

Возвращает численное значение типа Currency. Е любое допустимое численное или строковое выражение, которое может быть преобразовано в число

CDate(E)

Возвращает значение типа Date. E может быть любым допустимым выражением (строкой или числом), представляющим дату в диапазоне 1/1/100 12/31/9999

CDbl(E)

Возвращает численное значение типа Double. Е любое допустимое численное или строковое выражение, которое может быть преобразовано в число

CInt(E)

Возвращает численное значение типа Integer. Е любое допустимое численное или строковое выражение, которое может быть преобразовано в число

CLng(E)

Возвращает численное значение типа Long. Е любое допустимое численное или строковое выражение, которое может быть преобразовано в число

Функция (аргумент)

Действие

CSng(E)

Возвращает численное значение типа Single. Е любое допустимое численное или строковое выражение, которое может быть преобразовано в число

CStr(E)

Возвращает численное значение типа String. Е любое допустимое численное или строковое выражение, которое может быть преобразовано в число

CVar(E)

Возвращает численное значение типа Variant. Е любое допустимое численное или строковое выражение, которое может быть преобразовано в число

Функции даты и времени 60

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

N - любое допустимое численное значение; D - любое допустимое выражение типа Date (либо выражение, которое может быть преобразовано к типу Date). Аргументы каждой функции являются обязательными, если не указано иначе. Функция (аргумент)

Действие

Date

Возвращает системную дату

Time

Возвращает системное время

Now

Возвращает системные дату и время

Year(D)

Возвращает целое, являющееся частью выражения типа Date и содержащее год. Год возвращается как число между 100 и 9999

Month(D)

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

Day(D)

Weekday(D)

Возвращает целое, являющееся частью выражения типа Date и содержащее день. День возвращается как число между 1 и 31 Возвращает целое, являющееся частью выражения типа Date и содержащее день недели. День недели возвращается как число между 1 и 7 (1 воскресенье)

Функция (аргумент)

Действие

Hour(D)

Возвращает целое, содержащее часы как часть времени, содержащегося в выражении типа Date. Часы возвращаются как число между 0 и 23. Если аргумент не содержит значения времени, то возвращается 0

Minute(D)

Возвращает целое, содержащее минуты как часть времени, содержащегося в выражении типа Date. Минуты возвращаются как число между 0 и 59. Если аргумент не содержит значения времени, то возвращается 0

Second(D)

Возвращает целое, содержащее секунды как часть времени, содержащегося в выражении типа Date. Секунды возвращаются как число между 0 и 59. Если аргумент не содержит значения времени, то возвращается 0 61

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

DateSerial(N,N,N)

Возвращает значение последовательной даты для заданной даты. Слева направо аргументы представляют год (целое число от 100 до 9999), месяц (от 1 до 12) и день (от 1 до 31)

TimeSerial(N,N,N)

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

DateValue(E)

Возвращает значение типа Date, эквивалентное дате, заданной аргументом Е, который может быть строкой, числом или константой, представляющей время

TimeValue(E)

Возвращает значение типа Date, содержащее время, заданное аргументом Е, который может быть строкой, числом или константой, представляющей время

Timer

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

Строковые функции N - любое допустимое численное значение; S - любое допустимое строковое значение. Аргументы каждой функции являются обязательными, если не указано иначе. Функция (аргумент) Действие InStr(N1,S1,S2,N2)

Возвращает положение S2 в S1. N1 - начальное положение для поиска. N2 определяет, следует ли выполнять поиск с учетом регистра. N1, N2 необязательные аргументы

Lcase(S)

Возвращает строку (тип String), содержащую копию S со всеми символами верхнего регистра, преобразованными в символы нижнего регистра

Left(S,N)

Возвращает строку: копирует N символов из S, начиная с левого крайнего символа S

Len(S)

Возвращает число символов в S, включая начальные и конечные пробелы

Ltrim(S)

Возвращает копию строки S после удаления символов пробела из левой части строки (начальные пробелы) 62

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Mid(S,N1,N2)

Возвращает строку: копирует N2 символов из S, начиная с позиции символа в S, заданной аргументом N1. N2 - необязательный аргумент, если он опущен, то возвращаются все символы в строке S от позиции N1 до конца строки

Right(S,N)

Возвращает значение типа String: копирует N символов из S, начиная с правого крайнего символа S

Rtrim(S)

Возвращает копию строки S после удаления символов пробела из правой части строки (конечные пробелы)

Space(N)

Возвращает строку пробелов длиной N символов

StrComp(S1,S2,N)

Сравнивает S1 с S2 и возвращает число, обозначающее результат сравнения: -1, если S1<S2; 0 - S1=S2; 1 - S1>S2. N является необязательным аргументом и указывает следует ли выполнять сравнение с учетом регистра

StrConv(S,N)

Возвращает строку, преобразованную в новую форму в зависимости от числового кода, заданного аргументом N. VBA предоставляет внутренние константы для использования с функцией StrConv. Наиболее полезными являются: vbProperCase (преобразует строку так, что каждая буква, начинающая слово, становится заглавной), vbLowerCase (преобразует строку в буквы нижнего регистра) и vbUpperCase (преобразует строку в буквы верхнего регистра)

String(N,S)

Возвращает строку длиной N символов, состоящую из символа, заданного первым символом в S

Trim(S)

Возвращает копию строки S после удаления начальных и конечных символов пробела из этой строки

Ucase(S)

Возвращает S со всеми символами нижнего регистра, преобразованными в символы верхнего регистра

Операторы VBA Операторы ветвления VBA: простой выбор 63

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

До сих пор рассматривались процедуры и функции, которые VBA выполняет в линейном порядке - VBA начинает выполнение кода с первого оператора после строки объявления процедуры (функции) и продолжает выполнять каждый оператор построчно до тех пор, пока не будет достигнут оператор End Sub (End Function). Такой линейный алгоритм можно представить нижеследующей блоксхемой:

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

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Операторы, выполняющие роль ветвления программы на основании какоголибо условия, называются операторами условного перехода. Простейшими операторами условного перехода являются операторы:

Первый оператор позволяет выбирать единственную ветвь процедуры (левая блок-схема), тогда как второй дает возможность выбирать из двух альтернативных ветвей кода процедуры (правая блок-схема) на основе оценки того, является ли условие равным True или False. Синтаксис If..Then Вариант 1: Вариант 2:

- логическое выражение; - один, несколько или ни одного оператора VBA.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Первый вариант требует написания оператора в одну строку, при этом в секции Statements можно указывать несколько операторов, разделяя их двоеточием. Второй вариант позволяет указывать несколько операторов в разных строках. Логика работы следующая: VBA сначала оценивает логическое выражение, представленное в секции Condition; если это логическое выражение равно True то выполняется оператор (операторы) секции Statements; затем VBA продолжает выполнение кода следующего за строкой If..Then (Вариант 1) или за ключевыми словами End If (Вариант 2). Если же логическое выражение равно False, то операторы секции Statements пропускаются и выполняется код, следующий за этой секцией. Ниже приведен листинг элементарного использования первого варианта написания If..Then:

А теперь - второй вариант:

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Операторы секции Statements написаны с использованием отступа (используется табуляция). Следует сразу приучать себя к подобному оформлению программного кода - это повышает его удобочитаемость и значительно облегчает поиск ошибок. Синтаксис If..Then..Else Вариант 1:

Вариант 2:

- логическое выражение; - один, несколько или ни одного оператора VBA. По аналогии с If..Then - первый вариант требует написания оператора в одну строку, при этом в секциях Statements и ElseStatements можно указывать несколько операторов, разделяя их двоеточием. Второй вариант позволяет указывать несколько операторов в разных строках. Логика работы следующая: VBA сначала оценивает логическое выражение, представленное в секции Condition; если это логическое выражение равно True то выполняется оператор (операторы) секции Statements; затем VBA продолжает выполнение кода следующего за строкой If..Then (Вариант 1) или за ключевыми 67

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

словами End If (Вариант 2). Если же логическое выражение равно False, то выполняются операторы секции ElseStatements. Следует сказать, что блок операторов If..Then..Else (Вариант 2) легче читать и понимать. Поэтому, советую использовать именно этот вариант, даже если в секциях Statements и ElseStatements будет находиться по одному оператору.

Операторы ветвления VBA: сложный выбор. Безусловный переход В реальных программах зачастую бывает необходимо выполнять более сложный выбор в процедурах, выбирая между тремя и более ветвями. В этом случае можно помещать операторы If..Then..Else друг в друга. Это называется вложением операторов.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Такая процедура использует несколько вложенных друг в друга операторов условного перехода. Следует сказать, что эта процедура будет работать только в Excel, т.к. использует метод Application.InputBox. Этот метод не дает пользователю во время работы функции ввести что-либо, кроме числа. Если пользователь вводит не число, то получает об этом сообщение.

Если пользователь ничего не вводит, то получает сведение об ошибке.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Если пользователь воспользуется кнопкой "Отмена", то получает сообщение "Не введены данные". VBA предоставляет сокращенную версию оператора If..Then..Else, являющуюся сжатым эквивалентом вложенных операторов If..Then..Else, использованных в листинге. Такой краткой формой является оператор If..Then..ElseIf

Считается, что второй вариант более компактный, тогда как первый - более удобный и понятный. Для выполнения выбора из нескольких возможных ветвей кода можно вкладывать операторы If..Then..Else на много уровней вглубь, но уследить за ходом выполнения ветвей становится все труднее и труднее. VBA имеет условный оператор перехода для использования в случаях, когда необходимо выбирать из большого количества различных ветвей кода - Select Case. Он работает практически так же, как и Else..If, но более понятен. Ключевые слова Select Case используются со многими операторами Case, где каждый оператор Case проверяет появление другого условия и выполняется только одна из ветвей Case. Ветвь Case может содержать один, несколько или ни одного оператора VBA. 70

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

Оператор GoTo применяется только в исключительных случаях. Циклы VBA. Команды организации циклов 71

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Какие либо действия процедуры повторяющиеся заданное количество раз или пока выполняется или не выполняется некоторое условие называют циклом. Процесс выполнения все операторов, заключенных в структуру цикла, один раз называется итерацией цикла. Структуры цикла, всегда выполняющиеся заданное количество раз, называются циклами с фиксированным числом итераций. Другие типы структур цикла повторяются переменное количество раз в зависимости от некоторого набора условий. Такие циклы называются неопределенными циклами. Блок операторов, находящийся между началом и концом цикла называется "тело цикла". Самой простой структурой цикла является фиксированный цикл. Цикл For..Next Синтаксис

- любая численная переменная VBA - любое численное выражение, определяет начальное значение для переменной counter - численное выражение, определяет конечное значение для переменной counter - один, несколько или ни одного оператора VBA (тело цикла). По умолчанию VBA увеличивает переменную counter на 1 каждый раз при выполнении операторов в цикле. Можно задать другое значение (SterSize - любое численное выражение), на которое будет изменяться counter. Ключевое слово Next сообщает VBA о том, что достигнут конец цикла. Необязательная переменная counter после ключевого слова Next должна быть той же самой переменной counter, которая была задана после ключевого слова For в начале структуры цикла. ВНИМАНИЕ! При уменьшении счетчика цикла For..Next цикл выполняется, пока переменная счетчика больше или равна конечному значению, а когда счетчик цикла увеличивается, цикл выполняется, пока переменная счетчика меньше или равна конечному значению. Ниже представлен листинг простейшего цикла For..Next, который считает сумму цифр от 1 до 10:

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Два варианта цикла For..Next с использованием шага цикла отличного от единицы:

Цикл For Each..Next Цикл For Each..Next не использует счетчик цилка. Циклы For Each..Next выполняются столько раз, сколько имеется элементов в определенной группе, такой как коллекция объектов или массив (которые будут рассматриваться позже). Проще говоря, цикл For Each..Next выполняется один раз для каждого элемента в группе. 73

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Синтаксис

- переменная, используемая для итерации по всем элементам в определенной группе - это объект коллекции или массив - один, несколько или ни одного оператора VBA (тело цикла). Цикл For Each..Next всегда выполняется столько раз, сколько имеется элементов в определенной группе. В нижеприведенном листинге показана функция SheetExists, использующая цикл For Each..Next для определения того, существует ли определенный лист в рабочей книге Excel:

Вложенные циклы Циклы можно помещать внутрь друг друга. Помещение одной структуры цикла в другую называется вложением циклов. Можно делать вложение структур циклов разного типа. При вложении циклов надо соблюдать определенные правила: • При вложении циклов For..Next каждый цикл должен иметь свою уникальную переменную счетчика; 74

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

При вложении циклов For Each..Next каждый цикл должен иметь свою уникальную element-переменную; • Если используется оператор Exit For или Exit Do во вложенном цикле, этим оператором заканчивается только выполняемый в данный момент цикл. VBA продолжает выполнение следующего цикла более высокого уровня. В нижеприведенном листинге показан простейший пример использования вложенных циклов For..Next: •

Прерывание выполнения макроса или процедуры Зачастую допускаются ошибки в написании кода, которые приводят к зацикливанию программы (бесконечный цикл). Для прерывания выполнения VBA необходимо нажать клавишу Esc или комбинацию клавиш Ctrl+Break. VBA заканчивает выполнение текущего оператора, переходит в состояние ожидания и отображает окно сообщения о runtime-ошибке. Массивы VBA. Понятие и размерность массива. Статические и динамические массивы. Массив - это коллекция переменных, которые имеют общие имя и базовый тип. Все элементы данных, сохраняемых в массиве, должны иметь один и тот же тип. Информация, сохраненная в массиве, может быть доступна в любом порядке. Массив позволяет сохранять и манипулировать многими элементами данных посредством единственной переменной. Обработку массивов значительно упрощает использование циклов. Одномерные массивы Одномерный массив - это самый простой вариант массива, использующий обыкновенный список данных. Например: 75

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Это строковый массив, состоящий из 10 элементов. Дадим ему название My_Array. Нумерация элементов в массиве начинается с 0. Такая система нумерации довольно распространена в программировании и называется нумерацией с нулевой базой. Для доступа к данным, хранящимся в определенном элементе массива, следует указывать имя массива с последующим числом, называемым индексом элемента. Индекс всегда заключается в круглые скобки. Например: My_Array(3) этому элементу нашего массива соответствует "Миша" (не забывайте, что по умолчанию нумерация элементов массива начинается с 0). Поскольку нумерация с нулевой базой не очень удобна (т.к. мы привыкли считать с 1, а не с 0), то в VBA имеется директива компилятора, позволяющая исправить это "неудобство": Option Base. Директива компилятора имеет два варианта написания: - индексы массивов начинаются с 0 (установка по умолчанию) - индексы массивов начинаются с 1. Данная директива компилятора помещается в область объявлений модуля перед объявлениями любых переменных, констант или процедур. Нельзя помещать Option Base внутри процедуры. Можно иметь только один оператор Option Base в модуле, который влияет на все массивы, объявляемые в модуле. Многомерные массивы Одномерные массивы хорошо подходят для представления простых списков данных. Однако часто бывает необходимо представить таблицы данных в программах с организацией данных в формате строк и столбцов, подобно ячейкам в рабочих листах Excel. Для этого необходимо использовать многомерные массивы. Так адрес каждой ячейки листа состоит из двух чисел, одно из которых (номер строки) является первым индексом, а второе (номер столбца) - вторым индексом массива. Такой массив называется двумерным массивом. Добавив еще номер листа, получим трехмерный массив. VBA позволяет создавать массивы, имеющие до 60 измерений. Статические и динамические массивы Массивы, не меняющие число своих элементов, называются статическими массивами. Примером такого массива может служить вышеприведенный массив My_Array, содержащий 10 элементов. Однако бывают ситуации, когда изначально неизвестно количество элементов в массиве, или же, в процессе работы это количество может изменяться. Такие массивы называются динамическими массивами. Динамический массив может увеличиваться или сжиматься, чтобы вмещать точно необходимое число элементов без напрасного расходования памяти. 76

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Объявление массивов Объявление массива с использованием оператора Dim имеет следующий синтаксис: - любое имя массива, использующее допустимый идентификатор имени; - измерение массива. Если размерность массива больше единицы, то Subscripts разделяются запятыми. Оператор Subscripts имеет следующий синтаксис: - определяет нижний диапазон допустимых индексов для массива (необязательный аргумент); - определяет верхний предел для индексов массива (обязательный аргумент). Примеры правильного объявления массивов: ⎯ Dim Array_Str (1 To 10) As String - одномерный статический строковый массив, включающий 10 элементов; ⎯ Dim Array_Var() - динамический массив; ⎯ Dim Array_Mult (0 To 5, 0 To 7) As Integer - двумерный статический массив целых чисел, включающий 6*8=48 элементов. При объявлении массивов следует помнить, что включение оператора Subscripts в объявлении массива создает статический массив с фиксированным числом элементов, пропуск оператора Subscripts в объявлении массива создает динамический массив, а установка директивы компилятора Option Base влияет на общее число элементов в массиве. Использование массивов Для доступа к элементу массива необходимо указывать имя массива, за которым следует значение индекса, заключенное в круглые скобки. Нижеприведенный листинг показывает элементарное объявление и использование массива (в качестве элементов массива используется факториал номера элемента массива):

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

При помощи вложенных циклов довольно легко можно организовать инициализацию двумерного массива:

Изменение размерности динамического массива Могут сложиться обстоятельства, при которых точно неизвестно, сколько элементов потребуется в массиве. В VBA имеется возможность при помощи оператора ReDim переопределять размерность массива, а во время объявления не указывать его размерность. Синтаксис ReDim:

- имя существующего массива; - размерность существующего массива; - любой тип VBA. Необходимо использовать отдельный оператор As Type для каждого массива, который определяется; - необязательный аргумент. Его использование приводит к тому, что данные уже имеющиеся в массиве, сохраняются после изменения его размерности. Примеры правильного использования оператора ReDim: ⎯ Dim Array_Month() As String - одномерный строковый динамический массив ⎯ ReDim Array_Month(29) - устанавливет размерность динамического массива равную 29 элементам ⎯ ReDim Array_Month(1 To 30) - изменяет размер массива до 30 элемента ⎯ ReDim Preserve Array_Month(1 To 31) - изменяет размер массива до 31 элемента, сохраняя содержимое ⎯ Dim Array_DBL() As Single - объявляет динамический массив 78

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

⎯ ReDim Array_DBL(2, 9) - делает массив двумерным ⎯ ReDim Array_DBL(3, 7) - изменяет размер двумерного массива ⎯ ReDim Preserve Array_DBL(1 To 3, 1 To 5) - изменяет последний размер массива, сохраняя содержимое ВНИМАНИЕ! Можно изменять только последнее измерение многомерного массива, когда используется ключевое слово Preserve. Управление файлами с помощью VBA Управление файлами включает действия, такие как копирование файлов, удаление неиспользуемых файлов для освобождения области дисковой памяти, перемещение файлов с одного диска на другие и создание или удаление каталогов диска. Управление файлами включает такие виды обработки, как просмотр списка файлов в папке для определения размера файла или даты и времени, когда этот файл был модифицирован в последний раз. В таблице указаны функции, операторы и методы управления файлами. В первом столбце таблицы находится ключевое слово VBA, во втором - указывается, предназначено ли ключевое слово для функции, оператора или объектного метода. В третьем столбце содержится краткое описание назначения каждой функции, оператора или метода. Имя Категория Назначение ChDir

Оператор

Изменяет текущий каталог

ChDrive

Оператор

Изменяет текущий драйвер диска

CurDir

Функция

Возвращает текущий каталог

Dir

Функция

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

FileCopy

Оператор

Копирует файл

FileDateTime

Функция

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

FileLen

Функция

Возвращает длину файла в байтах

Функция

Возвращает число, представляющее объединенные атрибуты файла или каталога диска, такие как System, Hidden и т.д.

Метод

Отображает Excel-диалоговое окно Open и возвращает имя файла, выбранное пользователем. В Word не имеется.

GetAttr

GetOpenFileName

Бизнес-информатика: дистанционный курс

Имя

Категория

Т. Н. Кисиль

Назначение

GetSaveAsFileName

Метод

Отображает Excel-диалоговое окно Save As и возвращает имя файла, выбранное пользователем. В Word не имеется.

Kill

Оператор

Удаляет файлы с драйвера диска.

MkDir

Оператор

Создает каталог диска.

Name

Оператор

Переименовывает или перемещает файл.

RmDir

Оператор

Удаляет каталог диска.

SetAttr

Оператор

Устанавливает атрибуты файла.

Операторы, функции и объектные методы, имеющиеся в VBA, делятся на шесть различных функциональных частей: • Получение или изменение атрибутов файла; • Выборка или нахождение имен файлов; • Получение или изменение текущего драйвера диска и папки или создание и удаление папок диска; • Копирование или удаление файлов; • Переименование или перемещение файлов; • Получение информации о файлах, такой как длина файла, дата и время, когда этот файл был модифицирован последний раз. ТЕСТ 2. ОСНОВЫ VBA 1 Как добавить созданный макрос на панель быстрого доступа? вкл. Файл - пункт Параметры - категория Панель быстрого доступа - в списке Выбрать команды из... - выбрать макрос - кн. Добавить вкл. Файл - Параметры - категория Настроить ленту - выбрать макрос - кн. Добавить вкл. Файл - Параметры - Панель быстрого доступа - выбрать макрос - кн. Добавить 2 Укажите основные этапы создания функции пользователя в MS EXCEL: открыть редактор Visual Basic: Разработчик - Visual Basic в редакторе Visual Basic выбрать команду Insert - Module записать операторы Function имя_функции () - End Function На вкладке Разработчик в группе Код нажать кнопку Просмотр кода

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

3 Функция пользователя - это... подпрограмма, которая не является функцией. функция, написанная при помощи языка Visual Basic for Application (VBA), вызываемая в ячейках Excel и возвращающая определенный результат набор инструкций, которые сообщают программе, какие действия следует выполнить, чтобы достичь определенной цели 4 Укажите основные этапы записи макроса: На вкладке Разработчик в группе Код нажать кнопку Запись макроса В поле Имя макроса указать название макроса Назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш В списке Сохранить выбать книгу, в которой сохраняется макрос В поле Описание указать краткое описание действий макроса 5 Какое расширение файла используется в MS Excel при работе с макросами и функциями пользователя? *.xlsx *.xlsm *.xml *.xlsb 6 Укажите основные этапы редактирования записанного в Excel макроса: На вкладке Разработчик в группе Код нажать кнопку Макросы. В поле Имя макроса выбрать имя редактируемого макроса кн. Изменить - для открытия редактора Visual Basic Выбрать параметр Включить все макросы 7 Какие действия выполняет следующая функция пользователя? Function Test (a, b) Test = a*b end function Форматирует значения чисел на листе Excel умножение двух чисел не выполняет никаких действий 8 Какие действия выполнит следующий макрос: изменит начертание шрифта текста изменит название шрифта текста изменит размер текста 81

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

9 Какие основные этапы вызова созданной функции пользователя? вкд. Формулы - кн. Вставить функцию - категория Определенные пользователем выбрать имя функции в диалоговом окне Аргументы функции - указать аргументы пользовательской функции записать в ячейке Excel название созданной функции 10 Какие действия выполнит следующий макрос: изменит начертание шрифта текста изменит название шрифта текста изменит размер, начертание, название шрифта в тексте ничего не изменит изменит размер шрифта ТЕМА 3. SMART-ТАБЛИЦЫ В MS EXCEL 2016 В MS Excel есть много инструментов, о которых большинство пользователей не подозревают или же недооценивают. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы. Таблица Excel –это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Такие таблицы называют умными таблицами.

1. СОЗДАНИЕ ТАБЛИЦЫ EXCEL В наличии имеется диапазон данных о продажах.

Чтобы преобразовать этот диапазон в Таблицу, следует выделить любую ячейку и выполнить команду п/м Вставка → гр. Таблицы → кн. Таблица либо применить комбинацию клавиш Ctrl+T. 82

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

Структура и ссылки на Таблицу Excel Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в книге Excel планируется несколько Таблиц, то имеет смысл придать им смысловые названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Например, изменим название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

А также при создании формул в строке формул или же непосредственно в ячейке.

Эксель видит не только целую Таблицу, но и ее отдельные части: • столбцы, • заголовки, • итоги и др. Ссылки при этом выглядят следующим образом: =Отчет[#Все] – на всю Таблицу =Отчет[#Данные] – только на данные (без строки заголовка) =Отчет[#Заголовки] – только на первую строку заголовков =Отчет[#Итоги] – на итоги =Отчет[@] – на всю текущую строку (где вводится формула) =Отчет[Продажи] – на весь столбец «Продажи» =Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи» Для написания ссылок совсем не обязательно запоминать все эти конструкции. При вводе формулы все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Выбираем нужное значение клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную. Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи» =СУММ(D2:D8), то она автоматически записывается как =Отчет[Продажи]. Т.е. ссылка указывает не на конкретный диапазон, а на весь столбец.

Свойства Таблиц Excel 1. Каждая Таблица имеет заголовки, которые обычно выбираются из первой строки исходного диапазона.

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа. При этом нет необходимости специально закреплять области таблицы.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце, т.е для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки –добавятся автоматически. 5. Новые столбцы также автоматически включатся в Таблицу.

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

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

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

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

В группе Инструменты можно создать сводную таблицу, удалить дубликаты, а также преобразовать в обычный диапазон. Однако самое интересное – это создание срезов.

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

Для фильтрации Таблицы следует выбрать интересующую категорию.

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

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Ограничения Таблиц Excel Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки. 1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие). 2. Текущую книгу нельзя выложить для совместного использования. 3. Невозможно вставить промежуточные итоги. 4. Не работают формулы массивов. 5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует. Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

2. ПРИМЕР УМНОЙ ТАБЛИЦЫ EXCEL 2007-2016 Постановка задачи Имеется таблица Excel, в которой постоянно приходится сортировать, фильтровать, рассчитывать значения и т. д., содержимое ее периодически изменяется (добавляется, удаляется, редактируется) такого вида:

Размер - от нескольких десятков до нескольких сотен тысяч строк - не важен. Задача - всячески упростить и облегчить работу, превратив эти ячейки в "умную" таблицу. Решение Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

В раскрывшемся списке стилей выбираем любой вариант заливки на вкус и цвет и в окне подтверждения выделенного диапазона нажимаем ОК, в результате получаем умную таблицу примерно такого вида:

После такого преобразования диапазона в "умную" Таблицу имеем следующие возможности: 1. Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP). 2. Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки - она добавится ниже, если добавить новые столбцы – увеличится по ширине. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

В заголовках Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)). 3. При добавлении новых строк - автоматически копируются все формулы.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

5. При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец – нет необходимости копировать формулу маркером автозаполнения. 6. При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. не нужно закреплять заголовки диапазона:

7. Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

8. К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех значений в столбце НДС можно воспользоваться формулой: =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1): • =Таблица1[#Все] - ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов • =Таблица1[#Данные] - ссылка только на данные (без строки заголовка) • =Таблица1[#Заголовки] - ссылка только на первую строку таблицы с заголовками столбцов • =Таблица1[#Итоги] - ссылка на строку итогов (если она включена) • =Таблица1[#Эта строка] - ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] - будет ссылаться на значение НДС из текущей строки таблицы. (В англоязычной версии операторы имеют следующие названия: #All, #Data, #Headers, #Totals и #This row). 91

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

3. УМНЫЕ ТАБЛИЦЫ. ДОПОЛНИТЕЛЬНЫЕ СВЕДЕНИЯ. Microsoft предложил использование умных таблиц еще в Excel 2003 и назывались "списками" ("lists"). В Excel 2007 их переименовали в ТАБЛИЦЫ (TABLES), а то что раньше называлось таблицами, предложено называть ДИАПАЗОНОМ (range). Термин умные таблицы достаточно хорошо описывает суть работы. Действительно, иметь дело в Excel с умными таблицами куда проще и приятнее, чем с обычными. Зачем они нужны? Тот кто много работает в Excel со временем понимает, что, создавая свои таблицы, надо придерживаться определенных правил: 1. Всем столбцам давать уникальные названия в колонках. 2. Не допускать пустых столбцов и строк в таблице. 3. Не допускать разнородных данных в пределах одной колонки. Если уж решили, что, например, в колонке E должен хранится объем продаж в штуках, то не надо туда же вносить объём продаж, скажем, в деньгах у части строк таблицы. 4. Не объединять ячейки без крайней необходимости. 5. Форматировать таблицу, чтобы она выглядела одинаково во всех своих частях, т. е. элементарно рисовать сетку, выделять цветом заголовки столбцов. 6. Закреплять области, чтобы заголовок был всегда виден на экране. 7. Ставить фильтр по умолчанию. 8. Вставлять строку подитогов. 9. Грамотно использовать абсолютные и относительные ссылки в формулах, чтобы их можно было копировать без необходимости внесения изменений. 10.При работе с таблицей не выделять цветом строки/столбцы за пределами таблицы. Соблюдение этих простых правил поможет работать более продуктивно и осмысленно, осваивая действительно интересные и сложные задачи. В Excel 2013 разработчики предусмотрен функционал умных таблиц, обладающие многими полезными свойствами. Создание умной таблицы Создать умную таблицу можно выполнив следующие действия в таблице: 1. Способ 1 - на ленте ГЛАВНАЯ выбираем Форматировать как таблицу, выбираем дизайн (доступны 60 стандартных способа форматирования) 2. Способ 2 – Нажимаем комбинацию Ctrl-T 3. Способ 3 - На ленте ВСТАВКА выбрать Таблица, подтверждаем координаты таблицы и наличие/отсутствие заголовков, нажимаем OK.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Форматирование Если до того, как поумнеть, ваша таблица имела форматирование (рамки, цвета букв и фона и т.п.), то возможно стоит это форматирование сбросить, чтобы оно не "конфликтовало" с форматированием умной таблицы. Для этого: 1. Выделить таблицу целиком - 2 раза нажать Ctrl-A (латинская "A"!) 2. На ленте ГЛАВНАЯ щёлкнуть Стили ячеек, далее стиль Обычный

При этом все проблемы с форматированием сразу решаются. Однако придётся восстанавливать форматы столбцов ячеек: формат даты, времени, нюансы числового формата (типа количества знаков после точки) и т.д.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Предпросмотр стиля таблицы В меню Форматировать как таблицу можно просмотреть, как будет выглядеть ваша таблица при применении имеющегося стандартного стиля.

Преимущества умных таблиц: 1. Чередующийся цвет строк или столбцов! Да знаете ли вы, что раньше для этого надо было 10 минут колдовать с условным форматированием с бубном и крысиными костями! 2. Включение строки итогов в одно нажатие! 3. Фильтр по умолчанию 4. Первый и последний столбец могут быть выделены жирным шрифтом. 5. При прокрутке таблицы столбцы видны БЕЗ закрепления областей!

6. Упрощенное выделение таблицы, столбцов, строк 94

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

7. Умная таблица имеет имя и его можно изменять.

Хоть умная таблица и появляется в Диспетчере имен, но она не полностью равносильна именованному диапазону. Например, не получится напрямую столбец умной таблицы использовать в качестве источника строк для выпадающего списка функции Проверка данных (Data validation). Приходится создавать именованный диапазон, который ссылается уже на умную таблицу. В этом случае данный именованный диапазон, при добавлении новых строк, расширяется автоматически. Вставка срезов. В Excel 2016 появилась такая полезная функция как срезы. Это наглядные фильтры, которые можно добавлять к сводным таблицам, а также и к умным таблицам тоже.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

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

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2016 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Ррекомендуется во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множества ошибок при создании пользовательских правил при условном форматировании

4. УСЛОВНОЕ ФОРМАТИРОВАНИЕ В MS EXCEL Условное форматирование – один из самых полезных инструментов EXCEL. Умение им пользоваться может сэкономить пользователю много времени и сил. Начнем изучение Условного форматирования с проверки числовых значений на больше /меньше /равно /между в сравнении с числовыми константами. Эти правила используются довольно часто, поэтому в EXCEL они вынесены в отдельное меню Правила выделения ячеек.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Таблица 1. Использование формул умных таблиц. "Умный" способ адресации

Ссылки

Формула возвращает

Стандартный диапазон

=СУММ(Результаты)

По умолчанию умная таблица, которая названа "Результаты" ссылается на область своих данных

B3:E7

=СУММ(Результаты[#Данные])

Тот же результат вернёт данная формула, где область 87 данных указана в явном виде.

B3:E7

=СУММ(Результаты[Продажи])

Суммируем область данных столбца "Продажи". Если надо создать именованный диапазон, который будет ссылаться на столбец умной таблицы, то надо использовать синтаксис Результаты[Продажи].

D3:D7

=Результаты[@Прибыль]

Данную формулу мы вводили в строке 3. @ - означает текущую строку, а Прибыль - 6 столбец, из которого возвращаются данные.

Ссылка на диапазон столбцов: от колонки "Продажи", до колонки =СУММ(Результаты[Продажи]:Рез "Прибыль" включительно. 87 ультаты[Прибыль]) Обратите внимание на оператор ":", который создаёт диапазон.

D3:E7

=СУММ(Результаты[@])

Формулу вводили в троке 3. Она вернула всю строку таблицы.

B3:E3

=СЧЁТЗ(Результаты[#Заголовки])

Подсчёт количества элементов в #Заголовки.

B2:E2

=Результаты[[#Итоги];[Продажи]]

Формула возвращает итоговую строку для столбца Продажи. Это не одно и тоже, что 54 Результаты[Продажи], так как итоговая функция может быть разной, например, средней величиной.

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Эти правила также же доступны через меню Главная/ Стили/ Условное форматирование/ Создать правило, Форматировать только ячейки, которые содержат. Рассмотрим несколько задач: Сравнение с постоянным значением (константой) Задача1. Сравним значения из диапазона A1:D1 с числом 4. • введем в диапазон A1:D1 значения 1, 3, 5, 7 • выделим этот диапазон; • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше); • в левом поле появившегося окна введем 4 – сразу же увидим результат применения Условного форматирования. • Нажмем ОК.

Результат можно увидеть в файле примера на листе Задача1. Сравнение со значением в ячейке (абсолютная ссылка) Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4. Задача2. Сравним значения из диапазона A1:D1 с числом из ячейки А2. • введем в ячейку А2 число 4; • выделим диапазон A1:D1; • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше); • в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2).

Нажмите ОК. В результате, все значения из выделенного диапазона A1:D1 будут сравниваться с одной ячейкой $А$2. Те значения из A1:D1, которые меньше A2 будут выделены заливкой фона ячейки. Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/ 99

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Управление правилами; затем дважды кликните на правиле или нажмите кнопку Изменить правило. В результате получим следующее диалоговое окно:

Попарное сравнение строк/ столбцов (относительные ссылки) Будем производить попарное сравнение значений в строках 1 и 2. Задача3. Сравнить значения ячеек диапазона A1:D1 со значениями из ячеек диапазона A2:D2. Для этого будем использовать относительную ссылку. • введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями); • выделим диапазон A1:D1; • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше) • в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2). Убедитесь, что знак $ отсутствует перед названием столбца А. Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

Внимание! В случае использования относительных ссылок в правилах Условного форматирования необходимо следить, какая ячейка является активной в момент вызова инструмента Условное форматирование.

100

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Примечание-отступление: О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками При создании относительных ссылок в правилах Условного форматирования, они «привязываются» к ячейке, которая является активной в момент вызова инструмента Условное форматирование. СОВЕТ: Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя, там будет отображен адрес активной ячейки A1 или D1. Почему возможно 2 варианта и в чем разница для правил условного форматирования? Посмотрим внимательно на второй шаг решения предыдущей задачи 3 выделение диапазона A1:D1. Указанный диапазон можно выделить двумя способами: выделить ячейку А1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1; либо, выделить ячейку D1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1. Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1, а во втором D1! Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой. Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2, мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2. Т.к. правило распространяется на диапазон A1:D1, то B1 будет сравниваться с В2 и т.д. Задача будет корректно решена. Если при создании правила Условного форматирования активной была ячейка D1, то именно ее значение будет сравниваться со значением ячейки А2. А значение из A1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A2, EXCEL выберет самую последнюю ячейку XFD для С1, затем предпоследнюю для B1 и, наконец XFB2 для А1). Убедиться в этом можно, посмотрев созданное правило: • выделите ячейку A1; • нажмите Главная/ Стили/ Условное форматирование/ Управление правилами; • теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки <XFB2 (или <XFB$2).

101

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

EXCEL отображает правило форматирования (Значение ячейки <XFB2) применительно к активной ячейке, т.е. к A1. Правильно примененное правило, в нашем случае, выглядит так:

Выделение ячеек с текстом Предназначено выделение условным форматированием ячеек содержащих текст: • совпадение значения ячейки с текстовым критерием (точное совпадение, содержится, начинается или заканчивается) • ячейка выделяется если искомое слово присутствует в текстовой строке (фразе) • поиск в таблице сразу нескольких слов (из списка) Применение нескольких правил Часто требуется выделить значения или даже отдельные строки в зависимости от того диапазона, которому принадлежит значение. Используем Условное форматирование для выделения строк таблицы, в которых числа принадлежат к определенному диапазону. Например, если число в определенном столбце таблицы меньше 0, то вся строка будет выделена красным. Предположим, что пользователь заполняет таблицу о приросте продаж продукции. Требуется, чтобы EXCEL автоматически выделял строки следующим образом: • зеленым, если прирост продаж составил более 50%; 102

Бизнес-информатика: дистанционный курс • •

Т. Н. Кисиль

красным, если прирост продаж отрицательный, т.е. менее 0% (падение продаж); желтым - во всех остальных случаях (от 0% до 50%). Конечный результат должен выглядеть так.

Решение. Сначала создадим правило, по которому строки выделяются зеленым цветом если прирост продаж составил более 50%: • выделите диапазон, в который пользователь будет вводить данные (всю таблицу); • вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Управление правилами). Откроется окно Диспетчер правил условного форматирования, нажмите Создать правило. Или просто выберите Главная/ Стили/ Условное форматирование/Создать правило; • выберите Использовать формулу для определения форматируемых ячеек; • в поле ниже введите =$B4>=0,5; Обратите внимание на то, что в ссылке на ячейку использована смешанная адресация - именно она позволяет выделять не только ячейку содержащую значение, но и всю строку целиком. Например, для ячейки A5 это правило будет выглядеть =$B5>=0,5, т.е. также как и для ячейки В5. Обратите внимание, что в момент создания правила условного форматирования активной ячейкой у нас является ячейка А4 (если Вы выделяли таблицу начиная с левого верхнего угла, а не с нижнего правого). • нажав кнопку Формат выберите, зеленую заливку;

103

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

нажмите ОК и вернитесь в Диспетчер правил условного форматирования. Аналогично создайте правило для выделения краcной заливкой строк со значениями менее 0. Формула в этом случае будет =$B4<0 •

И сделайте заливку всех ячеек таблицы желтым (кроме заголовков столбцов). СОВЕТ: Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо: • на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить; • выберите в списке пункт Условное форматирование; • будут выделены все ячейки, к которым применены правила Условного форматирования. Приоритет правил Для проверки примененных к диапазону правил используйте Диспетчер правил условного форматирования (Главная/ Стили/ Условное форматирование/ Управление правилами). 104

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Когда к одной ячейке применяются два или более правил Условного форматирования, приоритет обработки определяется порядком их перечисления в Диспетчере правил условного форматирования. Правило, расположенное в списке выше, имеет более высокий приоритет, чем правило, расположенное в списке ниже. Новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, однако порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз. Например, в ячейке находится число 9 и к ней применено два правила Значение ячейки >6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон. На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Если к диапазону ячеек применимо правило форматирования, то оно обладает приоритетом над форматированием вручную. Форматирование вручную можно выполнить при помощи команды Формат из группы Ячейки на вкладке Главная. При удалении правила условного форматирования форматирование вручную остается. Условное форматирование и формат ячеек Условное форматирование не изменяет примененный к данной ячейке Формат (вкладка Главная группа Шрифт, или нажать CTRL+SHIFT+F). Например, если в Формате ячейки установлена красная заливка ячейки, и сработало правило Условного форматирования, согласно которого заливкая этой ячейки должна быть желтой, то заливка Условного форматирования "победит" - ячейка будет выделены желтым. Хотя заливка Условного 105

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

форматирования наносится поверх заливки Формата ячейки, она не изменяет (не отменяет ее), а ее просто не видно. Через Формат ячеек можно задать пользовательский формат ячейки, который достаточно гибок и иногда даже удобнее, чем Условное форматирование. Отладка правил условного форматирования Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено. Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1<A2 и скопируем ее вправо на 4 ячейки.

В тех столбцах, где результат формулы равен ИСТИНА, условное форматирование будет применено, а где ЛОЖЬ - нет. Использование в правилах ссылок на другие листы До MS Excel 2010 для правил Условного форматирования нельзя было напрямую использовать ссылки на другие листы или книги. Обойти это ограничение можно было с помощью использования имён. Если в Условном форматирования нужно сделать, например, ссылку на ячейку А2 другого листа, то сначала определяется имя для этой ячейки, а затем необходимо сослаться на это имя в правиле Условного форматирования. Как это реализовано См. файл примера на листе Ссылка с другого листа. Поиск ячеек с условным форматированием • на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить, • выберите в списке пункт Условное форматирование. Будут выделены все ячейки для которых заданы правила Условного форматирования. Другие предопределенные правила В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования. 106

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее. • Текст содержит… Приведем пример. Пусть в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит…Если в качестве критерия запишем ре (выделить слова, в которых содержится слог ре), то слово Дрель будет выделено.

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами...

Как видно из рисунка выше, Условное форматирование можно настроить выделять не только ячейки, содержащие определенный текст, но и не содержащие, начинающиеся с и заканчивающиеся на определенный 107

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

текст. Кроме того, в случае условий содержит и не содержит возможно применение подстановочных знаков ? и *. Пусть снова в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит… Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ, т.к. знак ? означает любой символ. Если в качестве критерия запишем ?????? (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР(), ЛЕВСИМВ(), ДЛСТР(), но этот подход, согласитесь, быстрее. • Повторяющиеся значения… Это правило позволяет быстро настроить Условное форматирование для отображения уникальных и повторяющихся значений. Под уникальным значением Условное форматирование подразумевает неповторяющееся значение, т.е. значение которое встречается единственный раз в диапазоне, к которому применено правило. • Дата… На рисунке ниже приведены критерии отбора этого правила. Для того, чтобы добиться такого же результата с помощью формул потребуется гораздо больше времени.

•

Значение ячейки. Это правило доступно через меню Главная/ Стили/ Условное форматирование/ Создать правило. В появившемся окне выбрать пункт форматировать ячейки, которые содержат. Выбор опций позволит выполнить большинство задач, связанных с выделением числовых значений.

108

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Рекомендуется обратить внимание на следующие правила из меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений. • Последние 10 элементов. Задача4. Пусть имеется 21 значение, для удобства отсортированных по возрастанию. Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера, лист Задача4. Слова "Последние 3 значения" означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они. Соответственно, правила, примененные к нашему списку: "Последнее 1 значение", "Последние 2 значения", ... "Последние 6 значений" будут приводить к одинаковому результату - выделению 6 значений равных 10. К сожалению, в правило нельзя ввести ссылку на ячейку, содержащую количество значений, можно ввести только значение от 1 до 1000. Применение правила "Последние 7 значений" приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11. 109

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Аналогично можно создать правило для выделения нужно количества наибольших значений, применив правило Первые 10 элементов. • Последние 10%

Рассмотрим другое родственное правило Последние 10%. Обратите внимание, что на картинке выше не установлена галочка "% от выделенного диапазона". Эта галочка устанавливается либо в ручную или при применении правила Последние 10%. В этом правиле задается процент наименьших значений от общего количества значений в списке. Например, задав 20% последних, будет выделено 20% наименьших значений. 110

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10. 10 - минимальное значение в списке, поэтому в любом случае будут выделены все его повторы. Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений. Правила с использованием формул Рассмотрим пример: предположим, что необходимо выделять ячейки, содержащие ошибочные значения: • Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1). • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило) • Выберите Использовать формулу для определения форматируемых ячеек

В поле «Форматировать значения, для которых следующая формула является истинной» введите =ЕОШ(A1) – если хотим, чтобы выделялись ячейки, содержащие ошибочные значения, т.е. будут выделены #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! (кроме #Н/Д) • Выберите требуемый формат, например, красный цвет заливки. Того же результата можно добиться по другому: • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/Создать правило) • Выделите пункт Форматировать только ячейки, которые содержат; • В разделе Форматировать только ячейки, для которых выполняется следующее условие: в самом левом выпадающем списке выбрать Ошибки. СОВЕТ: Отметить все ячейки, содержащие ошибочные значения можно также с помощью инструмента Выделение группы ячеек. •

111

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

5. СОЗДАНИЕ РАСКРЫВАЮЩИХСЯ СПИСКОВ В EXCEL Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac. Чтобы сделать лист удобнее, можно добавить в ячейки списки, в которых пользователь может выбрать определённое значение.

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

Совет: Теперь следует отсортировать данные в том порядке, в котором они должны отображаться в раскрывающемся списке. 2. Выделить введённые данные и выбрать из контекстного меню команду Присвоить имя. 3. В поле Имя указать имя записей, например Отделы, нажать кнопку ОК. Убедитесь, что имя не содержит пробелы. Это имя не будет отображаться в списке, но его нужно ввести, чтобы связать с раскрывающимся списком.

4. Щелкнуть по ячейке на листе, в которую требуется поместить раскрывающийся список, выполнить команду Данные→Проверка данных.

112

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Советы: Не удаётся нажать кнопку Проверка данных. Приведем возможные причины. o Раскрывающиеся списки невозможно добавлять в таблицы, которые связаны с сайтом SharePoint. Удалите связь таблицы с сайтом или удалите форматирование таблицы, а затем повторите попытку. o Возможно, лист защищён или находится в режиме совместного использования. Снимите защиту или закройте совместный доступ к листу, а затем повторите попытку. 5. На вкладке Параметры в поле Разрешить нажмите кнопку Список. 6. В поле Источник введите знак равенства (=), а сразу за ним — имя, присвоенное списку. Например: =Отделы.

7. Установите флажок Список допустимых значений 8. Если можно оставить ячейку пустой, установите выключатель Игнорировать пустые ячейки. 9. Откройте вкладку Сообщение для ввода. 10. Если необходимо, чтобы при выборе ячейки появлялось всплывающее сообщение, установите выключатель Отображать подсказку, если ячейка является текущей, введите заголовок и сообщение в соответствующие поля (до 225 знаков).

113

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

Выбор параметра в поле Стиль. o Чтобы отобразить сообщение, не препятствующее пользователям вводить данные, которые не содержатся в раскрывающемся списке, щелкните параметр Уведомление или "Предупреждение". Параметр "Уведомление" отобразит сообщение с помощью значка , а "Предупреждение" — с помощью значка . o Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, щелкните параметр Остановить. Примечание: Если не были добавлены заголовок и текст, по умолчанию вводится заголовок "Microsoft Excel" и сообщение: "Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен". Работа с раскрывающимся списком После создания раскрывающегося списка убедитесь, что он работает определенным образом. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей. Если список записей для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac. После того, как создан раскрывающийся список, может понадобиться добавлять в него дополнительные элементы или удалять имеющиеся. Изменение раскрывающегося списка, основанного на таблице Excel Если источником списка является таблица Excel, достаточно добавить элементы в список или удалить их из него, а Excel автоматически обновит все связанные раскрывающиеся списки: 114

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент. • Чтобы удалить элемент, выделите его, нажмите кнопку Удалить. Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх. •

Изменение раскрывающегося списка, основанного на именованном диапазоне: 1. Откройте лист, содержащий именованный диапазон для раскрывающегося списка. 2. Выполните одно из указанных действий. o Чтобы добавить элемент, перейдите в конец списка и введите новый элемент. o Чтобы удалить элемент, нажмите кнопку Удалить. Совет: Если удаляемый элемент находится в середине списка, щелкните его правой кнопкой мыши, выберите пункт Удалить, а затем нажмите кнопку ОК, чтобы сдвинуть ячейки вверх. 3. На вкладке Формулы нажмите кнопку Диспетчер имен. 4. В поле Диспетчер имен выберите именованный диапазон, который требуется обновить.

5. Щелкните поле Диапазон, а затем на листе выберите все ячейки, содержащие записи для раскрывающегося списка. 6. Нажмите кнопку Закрыть, и в появившемся диалоговом окне нажмите кнопку Да, чтобы сохранить изменения. 115

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Совет: Чтобы определить именованный диапазон, выделите его и найдите его имя в поле Имя. Поиск именованных диапазонов.

Применение проверки данных к ячейкам Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010. Проверка данных позволяет ограничить тип данных или значений, которые можно ввести в ячейку. Чаще всего она используется для создания раскрывающихся списков. Добавление проверки данных в ячейку или диапазон ячеек Примечание: Первые три действия, указанные в этом разделе, можно использовать для любого типа проверки данных. Шаги 4–8 относятся к созданию раскрывающегося списка. 1. Выделите одну или несколько ячеек, к которым нужно применить проверку. 2. На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.

3. На вкладке Параметры в разделе Разрешить нажмите кнопку Список. 4. В поле Источник введите значения, разделенные точкой с запятой. Например: a. Для ограничения ответа на вопрос (например, "Есть ли у вас дети?") двумя вариантами введите Да;Нет. 116

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

b. Для ограничения рейтинга качества производителя тремя позициями введите Низкое;Среднее;Высокое. Примечание: Эти инструкции обычно применимы только в том случае, если элементы списка, скорее всего, не будут изменяться. Если список может измениться или вам нужно добавлять или удалять элементы, следуйте рекомендации. Рекомендация: можно также создать список значений с помощью ссылки на диапазон ячеек в любой части книги. Удобнее всего создать список, а затем отформатировать его как таблицу Excel (на вкладке Главная щелкните Стили→Форматировать как таблицу и выберите нужный стиль таблицы). Затем выберите диапазон данных таблицы, то есть часть таблицы, содержащую список без заголовка (в данном случае — "Отдел"), и присвойте ему имя в поле "Имя".

5. В поле Источник проверки данных вместо значений введите знак равенства (=) и имя, которое только что задано.

117

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

6. Преимущество использования таблицы заключается в том, что при добавлении или удалении элементов список проверки данных будет обновляться автоматически. 7. Примечание: Рекомендуется поместить списки на отдельный лист (при необходимости скрытый), чтобы никто не мог их редактировать. 8. Убедитесь, что установлен флажок Список допустимых значений. В противном случае рядом с ячейкой не будет отображена стрелка раскрывающегося списка.

9. Чтобы указать, как обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые ячейки. Примечание: Если допустимые значения заданы диапазоном ячеек с определенным именем, в котором имеется пустая ячейка, установка флажка Игнорировать пустые ячейки позволит вводить в проверяемую ячейку любые значения. Это также верно для любых ячеек, на которые ссылаются формулы проверки: если любая ячейка, на которую указывает ссылка, пуста, то при установленном флажке Игнорировать пустые ячейки в проверяемую ячейку можно вводить любые значения. 10.Проверьте правильность работы проверки данных. Попробуйте ввести в ячейку сначала допустимые, а потом недопустимые данные и убедитесь, что параметры проверки применяются, а сообщения появляются в нужный момент. Примечания: • После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей. • Если список записей для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. • Отмена проверки данных. Выделите ячейки, проверку которых вы хотите отменить, щелкните Данные→Проверка данных, в диалоговом окне проверки данных нажмите кнопки Очистить все и ОК.

118

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

Сделайте следующее:

Разрешить вводить только целые числа из определенного диапазона

1. Выполнить действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек. 2. В списке Разрешить выберите значение Целое число. 3. В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон. 4. Введите минимальное, максимальное или точное значение. Можно ввести формулу, которая возвращает числовое значение. Например, проверку значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.

Разрешить вводить только десятичные числа из определенного диапазона

1. Выполнить действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек. 2. В поле Разрешить выберите значение Десятичный. 3. В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон. 4. Введите минимальное, максимальное или точное значение. Можно ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6 % от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение. Примечание: Чтобы пользователи могли вводить проценты, например "20 %", в поле Разрешить выберите значение Десятичное число, в поле Данные задайте необходимый тип ограничения, введите минимальное, максимальное или определенное значение в виде десятичного числа, например 0,2, а затем отобразите ячейку проверки данных в виде процентного значения, выделив ее и нажав кнопку Процентный формат на вкладке Главная в группе Число.

119

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Чтобы:

Разрешить вводить только даты в заданном интервале времени

Разрешить вводить только время в заданном интервале

Сделайте следующее: 1. Выполнить действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше. 2. В поле Разрешить выберите значение Дата. 3. В поле Данные выбрать необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше. 4. Ввести дату начала, окончания или определенную дату. Можно ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения. 1. Выполнить действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек. 2. В поле Разрешить выберите значение Время. 3. В поле Данные выбрать необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше. 4. Указать время начала, окончания или определенное время, которое необходимо разрешить. Если нужно ввести точное время, используйте формат чч:мм. Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 — время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время окончания.

120

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Чтобы:

Разрешить вводить только текст определенной длины

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

Сделайте следующее: 1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше. 2. В поле Разрешить выберите значение Длина текста. 3. В поле Данные выберите необходимый тип ограничения. Например, для установки определенного количества знаков выберите ограничение Меньше или равно. 4. В данном случае нам нужно ограничить длину вводимого текста 25 символами, поэтому выберем меньше или равно в поле Данные и введем 25 в поле Максимальное значение.

1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек. В поле Разрешить выберите нужный тип данных. 2. В поле Данные выберите необходимый тип ограничения. 3. В поле или полях, расположенных под полем Данные, выберите ячейку, которую необходимо использовать для определения допустимых значений. Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение "Меньше или равно" в списке "Данные", а в поле Максимальное значение введите >=E1.

121

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

Введите формулу

Значение в ячейке, содержащей код = И(ЛЕВСИМВ(C2;3)="ID-";ДЛСТР(C2)>9) продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) знаков.

Ячейка с наименованием продукта =ЕТЕКСТ(D2) (D2) содержала только текст.

122

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Значение в ячейке, содержащей чью- =ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE) то дату рождения (B6), было больше числа лет, указанного в ячейке B4.

=ЕЧИСЛО(НАЙТИ("@";B4)

Адрес электронной почты в ячейке B4 содержал символ @.

123

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Все данные в диапазоне ячеек =СЧЁТЕСЛИ($A$2:$A$10;A2)=1 A2:A10 содержали уникальные значения.

Примечание: Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. Часть A2)=1 изменится на A3)=1, A4)=1 и т. д.

6. СОЗДАНИЕ СПАРКЛАЙНОВ Спарклайны — это небольшие диаграммы внутри отдельных ячеек на листе. Благодаря своей компактности спарклайны выразительно и наглядно показывают закономерности в больших наборах данных. С помощью спарклайнов можно демонстрировать тренды в рядах значений (например, сезонные увеличения и уменьшения или экономические циклы) либо выделять максимальные и минимальные значения. Спарклайн выглядит понятнее всего, когда он расположен рядом с соответствующим набором данных. Чтобы создать спарклайн, нужно выделить диапазон значений, которые требуется проанализировать, а затем выбрать место для спарклайна. Диапазон данных для спарклайна

Место расположения спарклайна

124

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Спарклайны-гистограммы, который отображает продажи с начала года для Портленда, Сан-Франциско и Нью-Йорк

Создание спарклайнов 1. Выделите диапазон данных для спарклайна. 2. На вкладке " Вставка " нажмите кнопку спарклайны и выберите нужный тип спарклайна.

В диалоговом окне Вставка спарклайнов обратите внимание на то, что первое поле уже заполнено с учетом того, что вы выбрали на шаге 1. 3. Выделите на листе ячейку или диапазон ячеек, куда нужно поместить спарклайн. Важно: Размеры выбираемой области должны соответствовать диапазону данных. В противном случае Excel отобразит ошибки, свидетельствующие о том, что диапазоны не совпадают. Например, если в диапазоне значений три столбца и одна строка, следует выбрать смежный столбец и ту же строку. 4. Нажмите кнопку ОК. Совет: При изменении данных на листе спарклайны автоматически обновляются. Отметка точек данных на спарклайнах Можно отличить в определенных точках данных, максимальных или минимальных значений с помощью маркеров.

например

1. Щелкните спарклайн. 2. На вкладке " Конструктор спарклайнов " в группе Показать выберите маркеры, которые вы хотите, таких как максимальное и минимальное точек. Можно настроить цвета маркера, нажав кнопку Цвет маркера.

125

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Изменение стиля спарклайна 1. Щелкните спарклайн. 2. На вкладке " Конструктор спарклайнов " выберите необходимый стиль.

Чтобы просмотреть другие стили, наведите курсор на интересующий вас элемент и щелкните . Совет: Чтобы сразу же отменить примененный стиль, нажмите клавиши +Z. Обработка пустых ячеек и нулевых значений 1. Щелкните спарклайн. 2. На вкладке Конструктор спарклайнов щелкните Изменить данные, наведите указатель на скрытые и пустые ячейки и выберите нужный вариант.

Удаление спарклайнов 1. Щелкните спарклайн, который нужно удалить. 2. На вкладке Конструктор спарклайнов щелкните стрелку рядом с кнопкой Очистить и выберите нужный вариант.

Оси спарклайна В зависимости от данных, на которых основаны спарклайны, изменение осей может скорректировать их масштаб и точность, а также упростить сравнение. Горизонтальная ось Параметр Общая ось

Описание

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

126

Бизнес-информатика: дистанционный курс

Параметр

Т. Н. Кисиль

Описание

Ось дат

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

Показать ось

Отображается горизонтальная ось со спарклайном для улучшенной визуализации чисел больше и меньше нуля. Это особенно полезно при использовании спарклайнов в виде линий.

Параметр "Отобразить данные справа налево"

Направление горизонтальной оси изменяется на противоположное.

Вертикальная ось Можно задать наибольшее и наименьшее значения для одного спарклайна или всех спарклайнов в группе. Параметр Автоматическое для каждого спарклайна

Описание Минимальное и максимальное значения различны для каждого спарклайна и основаны на данных, составляющих спарклайн. Например, если диапазон данных для группы спарклайнов — A1:C4, то каждый из диапазонов A1:A4, B1:B4 и C1:C4 содержит минимальное и максимальное значения, основанные на наибольшем и наименьшем числах из этих трех диапазонов.

Одинаковое для Минимальное и максимальное значения являются всех спарклайнов одинаковыми для всех спарклайнов в группе и основаны на наибольших и наименьших значениях в диапазоне, составляющем группу спарклайнов. Например, если диапазон данных для группы спарклайнов — A1:C4, то минимальное и максимальное значения определяются наименьшим и наибольшим числами из диапазона A1:C4. Пользовательское В качестве минимального или максимального значения значение для группы спарклайнов задается конкретное число.

127

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

7. ИНТЕРАКТИВНАЯ ДИАГРАММА Качественная визуализация большого объема информации – это всегда нетривиальная задача, т.к. отображение всех данных часто приводит к перегруженности диаграммы, ее запутанности и, в итоге, к неправильному восприятию и выводам. Вот, например, данные по курсам валют за несколько месяцев:

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

128

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

В Excel 2010 к созданным диапазонам можно применить команду Форматировать как таблицу (Format as Table) с вкладки Главная (Home):

Это даст следующие преимущества: • Любые формулы в таких таблицах автоматически транслируются на весь столбец – не нужно копировать их вручную. • При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д. • Таблица быстро получает форматирование (чересстрочную заливку и т.д.) • Каждая таблица получает собственное имя (Таблица 1 и Таблица 2), которое можно затем использовать в формулах. 129

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Шаг 2. Добавляем флажки (checkboxes) для валют В Excel 2010 для этого необходимо отобразить вкладку Разработчик (Developer), а в Excel 2003 и более старших версиях – панель инструментов Формы (Forms). Для этого: • В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View – Toolbars – Forms) • В Excel 2007: нажать кнопку Офис – Параметры Excel – Отобразить вкладку Разработчик на ленте (Office Button – Excel options – Show Developer Tab in the Ribbon) • В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File – Options – Customize Ribbon – Developer) На появившейся панели инструментов или вкладке Разработчик (Developer) в раскрывающемся списке Вставить (Insert) выбираем инструмент Флажок (Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:

Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст (Edit text).

Привяжем флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта (Format Control), а затем в открывшемся окне задайте Связь с ячейкой (Cell link). Каждый флажок привязать к соответствующей ячейке над столбцом с валютой. При включении флажка в связанную ячейку будет выводиться ИСТИНА (TRUE), при выключении – ЛОЖЬ (FALSE). Это позволит проверять с помощью формул связанные ячейки и выводить в дополнительную таблицу либо значение курса из исходной таблицы для построения графика, либо #Н/Д (#N/A), чтобы график не строился.

130

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Шаг 3. Транслируем данные в дополнительную таблицу Заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):

При использовании команды Форматировать как таблицу (Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула имеет вид: =ЕСЛИ(F$1;B4;#Н/Д) Обратите внимание на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон. Теперь при включении-выключении флажков наша дополнительная таблица заполняется либо данными из исходной таблицы, либо искусственно созданной ошибкой #Н/Д, которая не дает линии на графике. Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования Добавим на лист Excel полосы прокрутки, с помощью которых пользователь сможет легко сдвигать график по оси времени и менять масштаб его увеличения. Полосу прокрутки (Scroll bar) берем там же, где и флажки – на панели инструментов Формы (Forms) или на вкладке Разработчик (Developer):

Рисуем на листе одну за другой две полосы – для сдвига по времени и масштаба: 131

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Каждую полосу прокрутки надо связать со своей ячейкой (синяя и зеленая ячейки на рисунке), куда будет выводиться числовое значение положения ползунка. Его будем использовать для определения масштаба и сдвига. Для этого щелкните правой кнопкой мыши по нарисованной полосе и выберите в контекстном меню команду Формат объекта (Format control). В открывшемся окне можно задать связанную ячейку и минимум-максимум, в пределах которых будет гулять ползунок:

Таким образом, должно быть две полосы прокрутки, при перемещении ползунков по которым значения в связанных ячейках должны меняться в интервале от 1 до 307. Шаг 5. Создаем динамический именованный диапазон Чтобы отображать на графике данные только за определенный интервал времени, создадим именованный диапазон, который будет ссылаться только на нужные ячейки в дополнительной таблице. Этот диапазон будет характеризоваться двумя параметрами: • Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка) • Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка) Этот именованный диапазон мы позже будем использовать как исходные данные для построения диаграммы. 132

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Для создания такого диапазона будем использовать функцию СМЕЩ (OFFSET) из категории Ссылки и массивы (Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа. В качестве точки отсчета берется стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами: =СМЕЩ(A3;4;1;5;2)

Константы в этой формуле можно заменить на ссылки ячеек с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ (OFFSET). Для этого: • В Excel 2007/2010 нажмите кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) • В Excel 2003 и старше – выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define) Для создания нового именованного диапазона нужно нажать кнопку Создать (Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне. Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:

133

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Теперь создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ (OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета: Перед именем диапазона используется имя текущего листа – это сужает круг действия именованного диапазона, т.е. делает его доступным в пределах текущего листа, а не всей книги. Это необходимо нам для построения диаграммы в будущем. В новых версиях Excel для создания локального имени листа можно использовать выпадающий список Область. Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:

А также диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:

Общий результат должен быть примерно следующим:

134

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Шаг 6. Строим диаграмму Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График (Line). Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка (Insert) и в группе Диаграмма (Chart) выбрать тип График (Line), а в более старших версиях выбрать в меню Вставка – Диаграмма (Insert – Chart). Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД (SERIES), обслуживающая выделенный ряд данных:

Эта функция задает диапазоны данных и подписей для выделенного ряда диаграммы. Необходимо подменить статические диапазоны в ее аргументах на динамические, которые были созданные ранее. Это можно сделать в строке формул, изменив: =РЯД(Лист1!$F$3;Лист1!$E$4:$E$10;Лист1!$F$4:$F$10;1) на: =РЯД(Лист1!$F$3;Лист1!Labels;Лист1!Euros;1) Выполнив эту процедуру последовательно для рядов данных доллара и евро, получим диаграмму, которая будет строиться по динамическим диапазонам Dollars и Euros, а подписи к оси Х будут браться из динамического диапазона Labels. При изменении положения ползунков будут меняться 135

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться нужные валюты. Таким образом построена интерактивная диаграмма, где можно отобразить тот фрагмент данных, необходимый для анализа прогнозирования. ТЕСТ 3. SMART-ТАБЛИЦЫ В MS EXCEL 2016 1 Как добавить срезы в Smart-таблицу Excel? Укажите основные этапы: выделить любую ячейку в таблице, для которой добавляется срез вкл. Вставка - кн. Срез д/о Вставка срезов - установить флажки для нужных полей, которые добавляются в срез - кн. ОК. вкл. Параметры - Вставить срез - Подключения к срезам 2 Какой функцией расчитывается ПРИБЫЛЬ в Smart-таблице Excel? =B2-C2 =[@Доходы]-[@Расходы] =[#Доходы]-[#Расходы] =[#B2]-[#C2] 3 Как переименовать Smart-таблицу в MS Excel установить курсор любом месте таблицы - Конструктор - гр. Свойство поле Имя таблицы контекстное меню таблицы - Таблица - Переименовать 4 Спарклайн - это... небольшая диаграмма, помещенная в одну ячейку графически представленная зависимость одной величины от другой инструмент отображения данных в интерактивном виде 5 Укажите основные этапы создания раскрывающегося списка в MS Excel на листе ввести данные, которые нужно отображать в раскрывающемся списке вызвать контексное меню - команда Присвоить имя в поле Имя ввести название списка - кн. ОК вкл. Данные - кн. Проверка данных в поле Источник - ввести (=) и имя присвоенного диапазона, присвоенное списку в шаге 3 136

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

6 Какие применяются правила в условном форматировании? Форматировать все ячейки на основании их значений Форматировать только ячейки, которые содержат Форматировать только первые или последние значения Форматировать только значения, которые находятся выше или ниже среднего Форматировать только уникальные или повторяющиеся значения Использовать формулу для определения форматируемых значений 7 Как преобразовать Smart-таблицу в диапазон установить курсор любом месте таблицы - Конструктор - гр. Сервис - кн. Преобразовать в диапазон установить курсор любом месте таблицы - Конструктор - гр. Свойства поле Имя таблицы контекстное меню таблицы - Таблица - Преобразовать в диапазон 8 Как расчитываются ИТОГИ в Smart-таблице Excel для поля ПРИБЫЛЬ? =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(6;[Прибыль]) =ПРОМЕЖУТОЧНЫЕ.ИТОГИ([@Прибыль]) =СУММ([@Прибыль]) =СУММ(D2:D6) 9 Как создать Smart-таблицу в MS Excel вкл. Главная - гр. Стили - кн. Форматировать как таблицу - выбрать Стиль вкл. Вставить - кн. Таблица вкл. Вставить - кн. Сводная таблица сочетание клавиш: нажмите CTRL+T или CTRL+L Сочетание клавиш: нажмите CTRL+С или CTRL+X 10 В ячейке В9 вывести ФАМИЛИЮ сотрудника с кодом 103: =ВПР(103; $A$2:$С$7; 2; 0) =ВПР(103; A2:С7; 2; ЛОЖЬ) =ВПР(А2; $A$2:$С$7; 2; 0)

137

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

ТЕМА 4. БАЗЫ ДАННЫХ В MS EXCEL 2016

1. РАБОТА С ФИЛЬТРАМИ В MS OFFICE EXCEL Рассмотрим, как можно искать информацию в MS Excel, используя фильтры. Для этого откроем эксель и набросаем в нем небольшую таблицу.

Выделите любую ячейку в строке с заголовками, затем перейдите на вкладку «Данные» и щелкните на кнопке «Фильтр»:

В строке с заголовками нашей таблицы в каждом столбце появятся «стрелки».

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

138

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Подготовительный этап завершен. Можно приступать к поиску информ ации. • Основы работы с фильтрами • Применение фильтров к таблице Щелкните на значке в столбце «Менеджер». Откроется следующее меню:

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

139

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

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

MS Excel поддерживает множественные фильтры, т.е. фильтр сразу по нескольким столбцам. Например, Вам необходимо найти все заказы менеджера Иванова от 18.01.2014. Для начала щелкните на в столбце «Менеджер» и выберите Иванова.

Теперь щелкните на в столбце «Дата отгрузки», снимите флажок с «(Выделить все)» и выберите 18.01.2014 или введите в строке поиска 18 и нажмите «ОК».

140

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Таблица примет следующий вид:

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

вкладку

Если необходимо снять фильтр только с одного столбца, оставив фильтры по другим, то щелкните на значке данного столбца, напр., «Дата отгрузки» и щелкните на пункте «Удалить фильтр с <Название столбца>»:

или

141

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Если необходимо полностью отказаться от фильтров в таблице, то перейдите на вкладку «Данные» и щелкните на кнопке «Фильтр». Она перестанет подсвечиваться, из строки с заголовками исчезнут значки и и в таблице отобразятся все данные. До

После

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

142

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. Условия «равно» или «не равно» предполагают, что искомое выраж ение стопроцентно совпадает с содержанием ячейки. Критерий «рав но» оставляет в таблице только те строки, в которых содержится выбранное значение. Соответственно, критерий «не равно» оставля ет все значения, кроме выбранного. Для упрощения задачи Вы можете выбрать нужное значение из выпадающего списка:

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

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

«Иванов» при опции «больше», то в таблице останутся только те ячейки, которые начинаются на букву «Й»(Картов, Йогуртов и т.п.). А при опции «меньше» - значения на букву «З» (Захаров, Букин). Единственное отличие условий «больше или равно» и «меньше или равно» от предыдущего пункта в том, что в фильтр включает и выбранное значение. Если необходимо найти все значения, которые начинаются на «Ива», то используйте условие «начинается с», а если хотите узнать, сколько в таблице значений, оканчивающихся на «рович», то выберите опцию «заканчивается на». Соответственно, условия «не начинается с» и «не заканчивается на» предполагают, что Вам не надо отображать в таблице значения, содержащие искомую фразу. При выборе условий «содержит» или «не содержит» можно указать любую фразу или сочетание букв, которые необходимо включить или исключить из фильтра. Отличие этого пункта от пунктов 1, 4 и 5, в том, что искомая фраза может находится в любом месте ячейки. Например, задав в качестве фильтра «Ива», в результате получим «Иванов Алексей», «Сергей Иваровский», «кривая» и т.п.

Числовые фильтры

Большинство условий те же самые, что и в текстовых фильтрах. Рассмотрим только новые. 1. Условие «между». При выборе данного условия, в появившемся окне сразу же устанавливаются нужные критерии. Из списка выбрать нужные значения или вести их с клавиатуры.

144

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. Условие «Первые 10». Данный пункт имеет следующие опции: • Показать наименьшие или наибольшие значения. • Сколько значений отобразить. В данном пункте требуется пояснение по второму значению: % от количества элементов. Например, в таблице 15 строк с числовыми значениями. При выборе 20% в таблице останется только 15/100*20 = 3 строки.

3. При выборе условий «Выше среднего» или «Ниже среднего» Excel автоматически высчитывает среднее арифметическое значение в столбце и фильтрует данные согласно критерию. Фильтр по дате Данные условия не требуют специальных расшифровок, поскольку их значение легко понять из названий. Единственное, что в стандартном окне выбора условий фильтра появляется кнопка Календарь для облегчения ввода даты.

145

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

2. УСЛОВНОЕ ФОРМАТИРОВАНИЕ В ФИЛЬТРАХ Есть еще один способ фильтрации данных – по условию. Например, необходимо найти все строки с Красоткиным.

146

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

Щелкаем правой кнопкой на ячейке с данным человеком и в появившемся меню выберите пункт «Фильтр». В новом меню есть несколько новых опций. В данном примере нам нужен пункт «Фильтр по значению…».

Если выбрать условие «Фильтр по цвету…», то в таблице останутся строки с ячейками того же цвета, что и активная ячейка (желтая заливка). Если щелкнуть на «Фильтр по цвету шрифта…», то в нашей таблице останутся только ячейки с красным или черным шрифтом, в зависимости от того, какая ячейка активна в данный момент. «Фильтр по значкам…» применяется только в том случае использования условного форматирования со значками. ТЕСТ 4. ФИЛЬТРАЦИЯ ДАННЫХ В MS EXCEL 1 Фильтрация данных используется … для упрощения процесса ввода и удаления записей; для упрощения процесса поиска информации; для сортировки данных; 2 Какой вычислительный критерий равносилен заданному критерию при фильтрации данных в БД MS Excel? =И(A8="Слива";C8=2;E8<=50000) =ИЛИ(A8="Слива";C8=2;E8<=50000) =A8="Слива";C8=2;E8<=50000 =A8:А13="Слива"; C8:С13=2; E8:Е13<=50000 3 Какой вычислительный критерий равносилен заданному критерию при фильтрации данных в БД MS Excel? =ИЛИ(И(A8="Яблоня";B8>3);И(A8="Вишня";C8>3)) =И(ИЛИ(A8="Яблоня";B8>3);ИЛИ(A8="Вишня";C8>3)) =ИЛИ(И(A8="Яблоня";B8>3;A8="Вишня";C8>3) =И(ИЛИ(A8="Яблоня";B8>3;A8="Вишня";C8>3) 147

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

4 Фильтрация данных в MS Excel осуществляется с помощью … команды Данные - Фильтр; команды Данные - Дополнительно - Расширенный фильтр; функций баз данных команды Данные - Автофильтр; команды Вставка - Сводная таблица; команды Конструктор - Вставить срез; команды Вставка - Срез; 5 Какой вычислительный критерий равносилен заданному критерию при фильтрации данных в БД MS Excel? =И(A3="Яблоня";B8>3;B8<6) =ИЛИ(A3="Яблоня";B8>3;B8<6) =И(A3="Яблоня";B8:В13>3;B8:В13<6) =ИЛИ(A3="Яблоня";B8:В13>3;B8:В13<6) 6 Какие аргументы используются в функциях баз данных MS Excel? база_данных поле критерий тип номер столбца 7 Каким требованиям должна отвечать база данных (БД) в MS Excel? должна обязательно содержать заголовки столбцов не должны содержать объединенных и пустых ячеек, пустых строк и столбцов таблица должна быть неделимая в каждом столбце должна содержаться однотипная информация 8 Какой вычислительный критерий равносилен заданному критерию при фильтрации данных в БД MS Excel? =И(A8="Яблоня";B8>3) =ИЛИ(A8="Яблоня";B8>3) =A7="Яблоня" =И(A11="Яблоня";B11>3) 148

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

9 Какие существуют критерии фильтрации данных в MS Excel? простой критерий сложный критерий сложенный критерий вычислительный критерий обычный критерий 10 Какой вычислительный критерий равносилен заданному критерию при фильтрации данных в БД MS Excel? =ИЛИ(A8="Яблоня";A8="Вишня") =И(A8="Яблоня";A8="Вишня") =A8="Яблоня";A8="Вишня" =A8="Яблоня";"Вишня"

149

Бизнес-информатика: дистанционный курс

Т. Н. Кисиль

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ 1. Введение в информационный бизнес: Учебное пособие. /Под ред. В.П.Тихомирова, А.В.Хорошилова. - М.: Финансы и статистика, 2007 2. Карминский А.М., Нестеров П.В. Информатизация бизнеса. - 2-е изд. - М.: Финансы и статистика, 2004 3. Зараменских Е.Н. - Основы бизнес-информатики. Учебник и практикум. – М.: Юрайт, 2017 – с. 407 4. Рыжко, А. Л. Информационные системы управления производственной компанией: учебник для академического бакалавриата / А. Л. Рыжко, А. И. Рыбников, Н. А. Рыжко. – М: Изд-во Юрайт, 2018. – 354 с. 5. Плахотникова, М. А. Информационные технологии в менеджменте: учебник и практикум для прикладного бакалавриата / М. А. Плахотникова, Ю. В. Вертакова. – 2-е изд., перераб. и доп. – М.: Изд-во Юрайт, 2018. – 326 с. 6. Экономическая информатика: учебник и практикум для прикладного бакалавриата / В. П. Поляков [и др.] ; под ред. В. П. Полякова. – М.: Издво Юрайт, 2018. – 495 с. 7. Інформатика: Підручник / О. Ф. Клименко, Н. Р. Головко; за заг. ред. О.Д. Шарапова — К.: КНЕУ, 2011. — 579 с. (Рекомендовано Міністерством освіти і науки України, Лист № 1/11-2255 від 18.03.11) 8. Інформатика: інноваційні технології навчання. Практикум: навч. посіб. / М.В.Сільченко, Ю.М.Красюк, Т.О.Кучерява, І.В.Шабаліна; за заг. ред. О.Д.Шарапова — К.: КНЕУ, 2010.— 467 с. (Рекомендовано Міністерством освіти і науки України, Лист № 1/11-3192 від 16.04.10) 9. Дибкова Л.М. Інформатика та комп’ютерна техніка: Навчальний посібник. Ви-дання 3-ге, перероблене, доповнене — К.: Академвидав, 2011. — 464 с. (Рекомендовано Міністерством освіти і науки України, Лист № 1/11-5461 від 21.06.10) 10. Термінологічний тлумачний словник з інформатики та інформаційних технологій з ілюстраціями // Кушерець В.І., Дибкова Л. М. — КиївДонецьк: Університет сучасних знань, 2010. — 304 с. 11. Дербенцев В.Д., Семьонов Д.Є., Шарапов О.Д. Словник термінів інформаційних систем і технологій. — К.: КНЕУ, 2008.— 256 с.

150

Бизнес-информатика

Articles inside

ТЕСТ 4. ФИЛЬТРАЦИЯ ДАННЫХ В MS EXCEL

СОЗДАНИЕ РАСКРЫВАЮЩИХСЯ СПИСКОВ В EXCEL5

ИНТЕРАКТИВНАЯ ДИАГРАММА7

УСЛОВНОЕ ФОРМАТИРОВАНИЕ В MS EXCEL4

1. РАБОТА С ФИЛЬТРАМИ В MS OFFICE EXCEL

УМНЫЕ ТАБЛИЦЫ. ДОПОЛНИТЕЛЬНЫЕ СВЕДЕНИЯ

СОЗДАНИЕ ТАБЛИЦЫ EXCEL

ПРИМЕР УМНОЙ ТАБЛИЦЫ EXCEL 2007 2016

СОЗДАНИЕ СПАРКЛАЙНОВ6

РАБОТА С ИЗОБРАЖЕНИЯМИ В MS WORD 2016

СОЗДАНИЕ ЗАПОЛНЯЕМОЙ ФОРМЫ

ДОБАВЛЕНИЕ НАЗВАНИЙ К РИСУНКАМ

ДОБАВЛЕНИЕ НАЗВАНИЙ К ТАБЛИЦАМ

ЗАПИСЬ МАКРОСА В EXCEL

ОСНОВНЫЕ НЕПЕЧАТАЕМЫЕ СИМВОЛЫ

ТЕХНОЛОГИЯ СОЗДАНИЯ РАССЫЛОК

ОСНОВНЫЕ ПРАВИЛА ОФОРМЛЕНИЯ ТЕКСТОВОЙ ДОКУМЕНТАЦИИ