EXCEL. Формульные выражения.

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

Пример формулы: =А1+В1

Пример функции: =ВПР(A4;$A$34:$D$40;4;ЛОЖЬ)

4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

Арифметические операторы используются для выполнения основных математических вычислений над числами. Результатом является число. К арифметическим операторам относятся: +, -, *, /, %,^.

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом являются логические значения Истина или Ложь. К операторам сравнения относятся: =, >, <, >=, <=, <>.

Текстовый оператор & осуществляет объединение последовательностей символов в единую последовательность.

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений. К адресным операторам относятся:

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

, - оператор объединения, который ссылается на объединение ячеек диапазона. Например, СУММ(В5:В15,С15:С25);

“ “ – оператор пересечения, который ссылается на общие ячейки диапазона. Например, в формуле СУММ(В4:С6 В4:D4) ячейки В4 и С4 являются общими для двух диапазонов. Результатом вычисления формулы будет сумма этих ячеек.

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

В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». С1 записана формула: =А1+В1, то при копировании ее в С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1: =В1+С1.

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.Например: $A$1

Смешанные ссылки - это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D6.

Ссылки на ячейки других листов книги имеют следующий формат:

<имя раб.листа>!ссылка на ячейку, например: Лист2!А1:А10.

Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например: Лист1: Лист5!$A$1:$D$3.

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

 

26. ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL. СРЕДСТВА ФОРМАТИРОВАНИЯ ТАБЛИЦ. ПОЛЬЗОВАТЕЛЬСКИЕ ФОРМАТЫ. УСЛОВНОЕ ФОРМАТИРОВАНИЕ Форматирование рабочего листа — это оформление табличных данных, находящихся на рабочем листе, с целью повышения их наглядности, улучшения визуального восприятия. Форматирование рабочего листа сводится к форматированию его ячеек, т. е. к определению параметров форматирования ячеек рабочего листа. Различают следующие параметры форматирования: -формат данных; -формат шрифта. Шрифт — это гарнитура, рисунок цифр и символов; -выравнивание содержимого ячеек; -обрамление ячеек. Рамка — это линия, очерчивающая ячейку или выделяющая одну из ее сторон; -палитра. Форматы могут задаваться для активной ячейки или выделенного диапазона ячеек, до ввода данных или после него. Форматирование данных заключается в задании форматов чисел и текста. По умолчанию Excel вводит значения в соответствии с числовым форматом Общ. Excel анализирует значения и автоматически присваивает им нужный формат, например 100 — число 100, а 10:52 — числовой формат даты и времени. Выбор формата данных осуществляется на вкладке Число диалогового окна команды Ячейки меню Формат. Поле списка Числовые форматы содержит категории форматов. Для форматирования дробей и чисел с десятичной точкой в форматах используются следующие символы шаблонов: # — символ указывает, что в данную позицию выводятся только значащие цифры, незначащие нули не отображаются. Количество символов шаблона в формате определяет правила округления числа при выводе. Если дробная часть числа содержит больше цифр; чем символов шаблона в формате, то число округляется так, чтобы количество разрядов соответствовало количеству символов в шаблоне. Если целая часть числа содержит больше цифр, чем количество символов в шаблоне, то отображаются все значащие разряды. Если в формате целой части числа представлены только символы #, а число меньше 1, то число начинается с десятичной точки. В числовом формате можно задавать цветовое представление значений. В этом случае первым элементом в шаблоне должен быть код цвета. Например, [синий] # ##0,00;[красный]-# ##0,00.==> все положительные значения будут выведены синим цветом, все отрицательные — красным. В числовом формате можно задавать формат, используемый только для чисел, удовлетворяющих заданному условию. Условие должно состоять из оператора сравнения и значения, заключенных в квадратные скобки. Форматы даты и времени. Для форматирования данных типа данных и времени используются следующие символы: Д — для отображения дней: Д — дни 1-31; ДД — 01-31; ДДД — Пн — Вс; ДДДД — Понедельник — Воскресенье. М — для отображения месяца: М — 1-12; ММ — 01-1 12; МММ — янв — дек; ММММ — январь — декабрь; МММММ — первая буква месяца. Г - для отображения лет: ГГ - 00-99; ГГГГ - 1900-9999. ч — для отображения часов: ч — 0-23; чч — 00-23. м — для отображения минут: м — 0-59; мм — 00-59., с — для отображения секунд: с — 0-59; cc — 00-59. 0 (ноль) — для отображения долей секунд: ч:мм:сс.00. АМ/РМ — для отображения двенадцатичасовой системы. [ ] — для отображения интервалов времени. Позволяет выводит значения, превышающие 24 часа, 60 минут или 60 секунд. Форматы могут быть составными, т. е. иметь числовую и текстовую часть. Текстовая часть всегда является последней в формате. Символы шаблона: -"" — двойные кавычки или обратная косая черта указывают на необходимость вставки, начиная с позиции шаблона, текстовых символов, следующих за ним в формате. Например, 0,00р. «Излишки»;[красный]-0,00р. «Недостатки»; -@ — указывает, что начиная с этой позиции формата могут выводиться любые символы (текст); -* — указывает, что, начиная с позиции символа в формате, необходимо многократно вывести символ, следующий за * (пока столбец не окажется заполненным по ширине); -_ — символ подчеркивания, используется для выравнивания содержимого ячейки; вместо него проставляется пробел, по ширине равный следующему за ним символу.

Условное форматирование — это форматирование выделенных ячеек на основе условий, заданных числами и формулами. Предназначено для выделения данных. Если данные ячейки удовлетворяют заданным условиям, то к ячейке будут применены установленные форматы. Критерий условного форматирования состоит из условных форматов. В критерии можно указать до трех условных форматов. Условный формат задается в виде условия <что сравниваем> операция сравнения <с чем сравниваем> Параметр Что сравниваем может быть задан значением выделенной ячейки, формулой выделенной ячейки (формула должна начинаться с символа «=», результатом формулы должно быть логическое значение ИСТИНА или ЛОЖЬ). Параметр С чем сравниваем может быть задан константой или формулой. Формула должна начинаться с символа «=» и может содержать абсолютные и относительные ссылки. Операции сравнения кроме всех известных операций содержат также операцию Между — для задания интервала значений, в который может попадать значение параметра Что сравнивать, и операцию Вне для указания интервала, вне которого может находиться значение параметра Что сравнивать. В Excel существует возможность поиска ячеек, имеющих условный формат. Для этого следует: 1)Выделить любую ячейку или ячейку, имеющую условный формат; 2)выполнить команду Перейти меню Правка; 3)выбрать кнопку Выделение; 4)в диалоговом окне Выделение группы ячеек выбрать и установить переключатель Условные форматы; 5)для выделения всех ячеек листа, для которых заданы условные форматы, выбрать переключатель Всех; 6)для выделения ячеек листа с теми же условными форматами, что и активная ячейка, выбрать переключатель Этих же.

СРЕДСТВА ЗАЩИТЫ ДАННЫХ В Excel есть.несколько команд, позволяющих защищать книги, структуры книг, отдельные ячейки, графические объекты, диаграммы. По умолчанию Excel блокирует (защищает) ячейки, графические объекты, диаграммы, сценарии и окна, но эта защита не вступает в силу, пока не выполнена команда Защита — Защитить лист меню Сервис. Установленные в диалоговом окне команды варианты защиты применяются только к текущему листу. После включения защиты нельзя изменить заблокированный элемент. При попытке сделать это на экран выводится соответствующее сообщение. В большинстве случаев нет необходимости в блокировке всех ячеек рабочего листа. Прежде чем защищать лист, следует выделить ячейки, которые нужно оставить незаблокированными. Затем снять флажок Защищаемая ячейка на вкладке Защита команды Ячейки меню Формат. Можно также предотвратить изменение структуры книги и зафиксировать положение ее окна. Для этого используется команда Защита — Защитить книгу меню Сервис. Содержимое рабочего листа книги можно скрыть. В этом случае строка ярлыков не содержит ярлык скрытого рабочего листа. Чтобы сделать рабочий лист невидимым, следует выполнить команду Лист — Скрыть меню Формат. В скрытых листах все данные и результаты вычислений сохраняются, они просто скрыты от просмотра. Чтобы затем вернуть режим вывода содержимого листа, следует выполнить команду Лист — Отобразить меню Формат, в поле списка скрытых листов диалогового окна команды выбрать нужный и нажать клавишу Enter. К ячейкам рабочего листа можно применить режим скрытия формул. При активизации таких ячеек содержащиеся в них формулы не выводятся в строке формул. Сами формулы в ячейках по-прежнему сохраняются, они просто недоступны для просмотра, а результаты вычислений остаются видимыми. Чтобы включить режим скрытия формул, необходимо выполнить следующие действия: выделить ячейки, которые нужно скрыть; установить флажок Скрыть Формулы на вкладке Защита команды Ячейки меню Формат; установить флажок Содержимого в диалоговом окне команды Защита — Защитить лист меню Сервис. В окне диалога команд Защитить лист или Защитить книгу меню Сервис можно назначить пароль, который должен быть введен для того, чтобы снять установленную защиту. Можно использовать разные пароли для всей книги и отдельных листов. Чтобы назначить пароль, необходимо выполнить команду Защита — Защитить лист (Защитить книги) меню Сервис, ввести пароль, после запроса Excel подтвердить пароль, для чего ввести его повторно, нажать ОК для возврата в окно книги. После назначения пароля нет способа снятия защиты с листа или книги без ввода этого пароля. Следует запоминать свои пароли с точностью до регистра букв.

27. Табличный процессор EXCEL. Списки и средства их обработки. Фильтры, виды фильтров и их применение.

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

Список состоит из трех структурных элементов: 1) Заглавная строка – это первая строка списка, состоящая из заголовков столбцов. Заголовки столбцов – это метки (названия) соответствующих полей. 2) Запись – совокупность компонентов, составляющих описание конкретного элемента (строка таблицы). 3) Поля – отдельные компоненты данных в записи (ячейки в столбце).

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

Правила формирования списка таковы: 1) Рабочий лист должен содержат только один список, т.к. некоторые операции, например, фильтрование, могут работать только с одним списком. 2) Если на рабочем листе кроме списка необходимо хранить и другие данные, список необходимо отделить пустой строкой и пустым столбцом. Причем лучше не размещать другие данные слева и справа от списка иначе они могут быть скрыты во время фильтрации списка. 3) Заглавную строку лучше дополнительно отформатировать, чтобы выделить среди строк списка (использовать форматы, отличные от тех, которые применены к данным списка). 4) Метки столбцов могут содержать до 255 символов.

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

Значительно упростить работу с записями списка позволяет команда Форма меню ДАННЫЕ. Использование формы данных позволяет: добавить записи в список; организовать поиск записей в списке; редактировать данные записи; удалять записи из списка.

Диалоговое окно команды Форма содержит шаблон для ввода и обработки записи, представленный в виде нескольких полей ввода, каждое из которых соответствует полю записи списка. Значения полей записи могут выводиться в шаблон по-разному:

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

Перед значениями полей в шаблон выводятся имена полей, составленные на основании заглавной строки, а если она отсутствует – на основе первой строки списка.

Перемещение между полями в шаблоне формы осуществляется нажатием клавиши Tab или установкой мышью места ввода в нужное поле ввода.

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

Существует три способа для поиска записей в списке:

- с помощью командных кнопок Далее/Назад;

- с помощью полосы прокрутки;

- с помощью командной кнопки Критерии.

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

При задании критериев можно использовать операторы сравнения.

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

* - для указания произвольного количества символов;

? – для указания одного символа.

Поиск записей по заданному критерию осуществляется нажатием на кнопку Далее. Последующее нажатие на эту кнопку или на кнопку Назад позволяет просмотреть се найденные записи в любом направлении. Восстановление доступа ко всему списку осуществляется нажатием кнопки Очистить в окне поиска команды Форма меню ДАННЫЕ.

Сортировка списков – это переупорядочивание одного или более столбцов. Сортировка выполняется с помощью одноименной команды меню ДАННЫЕ. Excel позволяет упорядочить записи списка по трем столбцам (полям).

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

- Подписям - если в качестве меток столбцов использовать данные первой строки;

- Обозначениям столбцов листа – если в качестве меток столбцов использовать заголовки столбцов рабочего листа (А, В, С и т.д.).

Командная кнопка Параметры в окне команды сортировки выводит окно Параметры сортировки, в котором можно

- установить параметр Учитывать регистр, для различия строчных и прописных символов при сортировке;

- указать, как будут сортироваться записи списка: по строкам (по умолчанию) или по столбцам;

- задать пользовательский порядок сортировки.

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