Балльно-рейтинговая система Лекции 6 Лабораторные работы 16 балла

Зарегистрируйтесь, чтобы просмотреть полный документ!

Балльно-рейтинговая система Лекции (6) Лабораторные работы (16) балла (17) Практические занятия (8) Реферат Самостоятельное задание Excel Итого Участие в конкурсе Выступление на конференции ВСЕГО - 2 балла 32 - 16 баллов (10) 25 баллов (14) -------100 баллов 10 баллов -------120 баллов -

БРС < 55 баллов ≥ 55 баллов - незачет

Электронная таблица Представляет собой компьютерный эквивалент обычной таблицы, в клетках которой могут быть записаны любые данные: текст, числа, даты, формулы Для управления электронной таблицей созданы специальные программные продукты – табличные процессоры

Окно Excel • • Предназначено для ввода электронной таблицы. Содержит следующие элементы стандартные элементы окна Windows поле имени – содержит имя или адрес активной ячейки или диапазона ячеек строка формул – отображение и редактирование содержимого активной ячейки строка состояния – выводит информацию о режиме работы, состоянии индикаторов режимов и клавиатуры

Рабочая книга файл, предназначенный электронной таблицы Имеет расширение. XLSX для хранения

Рабочий лист – составляющая рабочей книги Рабочий лист имеет имя (ярлык рабочего листа). По умолчанию листы именуются Лист1, Лист2, Лист3, Диаграмма 1. Типы листов: - рабочий лист – электронная таблица - лист диаграммы – графическое представление данных электронной таблицы Максимально рабочая книга может содержать 255 листов. Пользователь может управлять количеством рабочих листов (меню Файл, команда Параметры, вкладка Общие, группа При создании новых книг, параметр Число листов.

Структура рабочего листа Рабочий лист представляет собой сетку из строк и столбцов Максимальный размер рабочего листа – 16 384 столбца, 1 048 576 строк. Столбцы именуются латинскими буквами от A до Z и от AA до XFD Строки именуются числами от 1 до 1 048 576

Ячейка Располагается на пересечении столбца и строки Каждая ячейка имеет адрес, который образуется: <имя столбца><имя строки>, например А 10 Активная ячейка – это ячейка, в пределах которой осуществляется ввод или редактирование данных Активная ячейка выделяется жирной рамкой. Ее имя содержится в поле имени

Диапазон ячеек Прямоугольная область в таблице, несколько выделенных ячеек Адрес диапазона образуется: <адрес 1 -ой ячейки> : <адрес последней ячейки>, например А 1: А 5, В 10: D 10, С 3: Е 7

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

Перемещение по рабочему листу • • Перемещение может осуществляться клавишными командами: вниз – Enter вверх – Shift + Enter вправо – Tab влево – Shift + Tab Для закрепления части электронной таблицы на экране используется команда меню ВИД группа ОКНО, команда ЗАКРЕПИТЬ ОБЛАСТИ. Эта команда позволяет закрепить на экране нужное количество строк и / или столбцов. Перед выполнением команды соответствующие области таблицы должны быть выделены

Ввод данных в рабочий лист Ввод данных в активную ячейку осуществляется нажатием клавиши Enter после набора данных на клавиатуре. При этом выделение по умолчанию перемещается вниз по активному столбцу Направление перемещения задается командой меню Файл, команда Параметры, вкладка Дополнительно, группа Параметры правки, параметр Переход к другой ячейке после нажатия клавиши Ввод: Направление Для ввода данных в диапазон необходимо выделить нужный интервал ячеек и ввести данные последовательно в ячейки диапазона. Чтобы ввести одну и то же значение сразу в несколько ячеек, нужно сначала выделить эти ячейки, а затем ввести значение и при нажатой клавише Ctrl нажать Enter

Типы данных • Константы – Числовые – Текстовые (надписи) – Даты и времени – Логические значения – Ошибочные значения • Формулы

Числовые константы Число в Excel может содержать следующие символы: • цифры от 0 до 9 • знаки числа + и – • круглые скобки ( ) • знак / • знак денежной единицы (р. ) • знак % • знак разделения целой и дробной части числа (, ) • пробел • латинская буква Е или е

Правила ввода чисел 1. 2. 3. 4. 5. 6. 7. 8. Ввод числа может начинаться со знака «плюс» или «минус» . По умолчанию знак «+» опускается, «-» сохраняется Числовые значения, заключенные в круглые скобки, интерпретируются. как отрицательные. При вводе больших чисел позволяется вставлять пробел для отделения сотен от тысяч, тысяч от миллионов и т. д. Если ввод числа закончить знаком денежной единицы, к ячейке будет применен денежный формат Если ввод числа закончить знаком %, к ячейке будет применен процентный формат. Для ввода простой дроби следует ввести целую часть, затем пробел, затем дробную часть Символ Е или е используется при вводе чисел в экспоненциальном представлении. Независимо от количества отображаемых разрядов числа хранятся с точностью до 15 разрядов. Все разряды в числе после 15 преобразуются в нули

Общий формат числа Числа в ячейке отражаются в привычном виде Если длина числа не превышает ширину ячейки, то оно отображается в том виде, в котором водится Если длина числа превышает ширину ячейки, то число будет выведено в экспоненциальной форме Если значение числа превышает допустимое по формату значение, то в ячейке выводится признак переполнения - # # # По умолчанию числа выравниваются по правому краю

Текстовые константы Текст – это произвольная последовательность символов, не воспринимаемая как число, дата, время суток или формула

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

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

Числовой текст Предназначен для интерпретации специальных символов, используемых при вводе числа, как обычных Может состоять из текста и чисел или только из чисел Ввод числового текста начинается со знака апостроф (‘) или со знака равно. В последнем случае сам числовой текст должен быть заключен в кавычки

Константы типа «дата и время» Дата и время суток интерпретируются как числа Основной единицей измерения времени в Excel являются сутки. Они представляются последовательными десятичными значениями от 1 до 2 958 465 Базовая дата, представляемая десятичным числом 1, - это воскресенье, 1 января 1900 г. Максимальное десятичное значение даты 2958465 представляет 31 декабря 9999 года При вводе даты она сохраняется в виде десятичного значения, которое равно количеству дней между заданной и базовой датой Время суток – это десятичная дробь, которая представляет часть суток между их началом (12: 00 ночи) и заданным временем

Форматирование даты и времени Если при вводе даты год указывается двумя последними цифрами, то по умолчанию 00 – 29 подразумевает 2000 – 2029 года, остальные 1900 года. В противном случае год нужно указывать полностью При вводе даты и времени нет различий между строчными и прописными буквами При использовании 12 -часового формата после ввода времени через пробел следует ввести AM (A) – для ввода времени до полудня и PM (P) – для ввода времени после полудня Дату и время можно ввести в одну ячейку. Тогда их следует разделить пробелом. Внешнее представление в ячейках рабочего листа зависит от формата, назначенного ячейке В форматах даты и времени используются следующие разделители: «. » , «/» , “ – для даты, «: » – для времени

Операции над данными типа «дата и время» • Сложение даты и числа. Результат – новая дата • Вычитание из даты числа. Результат – предшествующая дата • Вычитание из одной даты другой. Результат – количество дней между датами

Формулы Формула – это краткая запись некоторой последовательности действий, приводящих к конкретному результату Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис Все формулы в Excel должны начинаться со знака равенства. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение По умолчанию вычисления по формуле осуществляется слева направо, начиная с символа «=» . Для изменения порядка вычисления в формуле используются скобки

Формулы содержат вычисляемые элементы (операнды) и операторы Операндами могут быть • константы • ссылки или диапазоны ссылок • заголовки • имена • функции

Виды операторов • • Математические Сравнения (отношения) Текстовые Адресные

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

Операторы отношения Используются для обозначения операций сравнения двух величин Результатом вычисления формул, содержащих операторы отношения, являются логические значения ИСТИНА или ЛОЖЬ К операторам сравнения относятся: • = • > • < • >= • <>

Текстовый оператор Осуществляет сложение текста - объединение последовательностей символов в единую последовательность Обозначение: &

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

Оператор диапазона Ссылается на все ячейки, расположенные между границами диапазона, включая сами границы Обозначается знаком «: » Пример: СУММ(А 2: В 3) = А 2 + А 3 + В 2 + В 3

Оператор объединения диапазонов Ссылается на все ячейки, входящие в состав объединяемых диапазонов Обозначается символом «; » Пример: СУММ(А 2: А 4; В 3: В 5) = А 2 + А 3 + А 4 + В 3 + В 4 + В 5

Оператор пересечения диапазонов Ссылается на общие ячейки пересекаемых диапазонов Обозначается символом « » (пробел) Пример: СУММ(А 5: С 7 В 3: Е 6) = В 5 + В 6 + С 5 + С 6

Приоритеты выполнения операторов • • • Адресные операторы «: » , «, » , « » знаковый минус ‘-‘ вычисление процента % арифметические ^, *, /, +, текстовый оператор & операторы сравнений =, <, >, <=, >=, <>

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

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

Виды ссылок • Относительные • Абсолютные • Смешанные

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

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

Смешанная ссылка Ссылка, являющаяся комбинацией относительной и абсолютной ссылок Может состоять из фиксированного столбца и относительной строки или фиксированной строки и относительного столбца

Ссылка на ячейки других рабочих листов Формат ссылки: <имя раб. листа>!<ссылка на ячейку> Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной Структура объемной ссылки: <диапазон рабочих листов>!<ссылка на ячейки>

Ссылка на ячейки других рабочих книг Формат ссылки: [имя книги]<имя листа>!ссылка на ячейку

Стиль ссылок R[n]C[m] При использовании этого стиля Excel ссылается на ячейки по номерам строк и столбцов. В этом режиме относительные ссылки выводятся в терминах их отношения к ячейке, в которой расположена формула, а не в терминах их действительных координат Числа n и m задают смещение на n строк и m столбцов относительно ячейки с формулой Положительные значения n и m задают смещение вниз и направо, отрицательные – вверх и налево Значения n и m, заключенные в квадратные скобки, задают относительную ссылку, без скобок – абсолютную

Установка стиля ссылок RC Меню Файл, команда Параметры, вкладка Формулы, группа Работа с формулами, флажок Стиль ссылок R 1 C 1

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

Правила задания имен • • • Имя может содержать до 255 -ти символов Первым символом должна быть буква, знак подчеркивания (_) или обратный слеш () Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания, кроме букв C, c, R и r Имена не могут быть такими же, как ссылки на ячейки Пробелы в именах не допускаются, в качестве разделителей слов можно использовать символы подчеркивания (_) и точки (. ) Имя может состоять как из строчных, так и из прописных букв, так как программа не делает между ними различия

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

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

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

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

Создание имен Производится командой меню Формулы группа, Определенные имена Можно использовать команду Имя диапазона контекстного меню Команда Диспетчер имен позволяет работать со всеми именами книги – создавать, изменять, удалять

Присвоение имен константам Можно создавать имена для констант и формул, даже если эти константы и формулы не отображаются в ячейках листа Для этого следует выполнить команду Присвоить имя (меню Вид, группа Определенные имена). В появившемся диалоговом окне в поле Имя задать нужное имя, а в поле Диапазон – то значение, которое нужно использовать под эти именем После этого созданное имя можно использовать в формулах

Формула массивов Это формула, действующая на диапазон значений и генерирующая столько же результатов, сколько значений в диапазоне Формула массивов использует несколько множеств значений, называемых массивами аргументов Диапазон массива – это блок ячеек, который имеет общую формулу массива Использование формул массивов позволяет заменить большое число формул, повторяющих одни и те же действия, одной компактной формулой

Создание формулы массива • выделить диапазон ячеек для размещения результатов • ввести знак «=» . • ввести формулу, выделяя диапазон аргументов и вводя нужные знаки операций • одновременно нажать клавиши Ctrl, Shift и Enter Формула массива будет автоматически заключена в фигурные скобки { } Создавая формулу массива, следует помнить, что: • форма и размер диапазона результатов должен строго соответствовать форме и размеру тех диапазонов, которые лежат в основе этих результатов • диапазон ячеек в формуле массивов является одним элементом, поэтому редактировать формулу в отдельной ячейке диапазона нельзя, необходимо выбирать весь диапазон

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

Ручной пересчет В этом режиме пересчет выполняется только по явной команде пользователя Режим устанавливается командой меню Файл команда Параметры вкладка Формулы группа Параметры вычислений переключатель Вручную Для пересчета формул в этом режиме следует нажать клавишу F 9 Даже если установлено ручное обновление вычислений, рабочая книга обычно пересчитывается при ее сохранении. Отмена этого режима осуществляется снятием флажка Пересчет перед сохранением

Циклическая ссылка Это формула, которая зависит от своего собственного значения (ссылается на ячейку с этой формулой) При обнаружении циклической ссылки Excel выдает сообщение об ошибке

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

Функции Excel Это специальная заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат Значения, над которыми функция выполняет операции, называются аргументами В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами Формулы в свою очередь могут содержать другие функции, т. е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной. Допускается до семи уровней вложения функций в одной формуле

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

Виды функций • • • Математические Инженерные - предназначены для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую) Информационные - предназначены для определения типа данных, хранимых в ячейках Логические - предназначены для проверки выполнения условия или нескольких условий Статистические - предназначены для выполнения статистического анализа данных Финансовые - предназначены для осуществления типичных финансовых расчетов Баз данных - предназначены для анализа данных из списков Текстовые - предназначены для обработки текста Даты и времени - позволяют работать со значениями даты и времени в формулах Нестандартные - создаются пользователем для собственных нужд. Создание функций осуществляется с помощью языка Visual Basic

Автосуммирование Командная кнопка ∑ находится в группе Редактирование меню Главная или в группе Библиотека функций меню Формулы Раскрывающийся список кнопки позволяет выбрать нужную итоговую функцию из набора статистических или перейти к другим функциям

Функция СУММЕСЛИ Суммирует ячейки, отвечающие заданному условию Синтаксис функции: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) Диапазон – определяет интервал проверяемых ячеек Условие – задает критерий, которому должны соответствовать проверяемые ячейки для суммирования Диапазон_суммирования – диапазон фактически суммируемых ячеек Если диапазон суммирования отсутствует, то суммируются ячейки аргумента диапазон

Функция СЧЕТЕСЛИ Считает количество ячеек, отвечающих заданному условию Синтаксис функции: =СЧЕТЕСЛИ(диапазон; условие) Диапазон – определяет интервал проверяемых ячеек Условие – задает критерий, которому должны соответствовать проверяемые ячейки для суммирования

Функция ЕСЛИ Используется для проверки значения и формул в ячейке Синтаксис функции: =ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь) Логическое_выражение – задает условие проверки Значение_если_истина – задает действие, выполняемое при соблюдении условия. Если этот аргумент отсутствует, а результат логического выражения истинен, то возвращается значение ИСТИНА Значение_если_ложь – задает действия, выполняемое при несоблюдении условия. Если этот аргумент отсутствует, а результат логического выражения ложен, то возвращается значение ЛОЖЬ

Логическое выражение в функции ЕСЛИ Используется для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения Любое логическое условие должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения Результатом логического выражения являются логические значения ИСТИНА (1) или ЛОЖЬ (0)

Создание сложных условий Создаются с помощью логических функций И ИЛИ НЕ Синтаксис функций: • =И(лог_знач1; лог_знач2» ; …; лог_знач30) • =ИЛИ(лог_знач1; лог_знач2» ; …; лог_знач30) • =НЕ(логическое_значение) И возвращает логическое значение Истина только, если все логические значения – аргументы истины ИЛИ возвращает логическое значение Истина, если истинно хотя бы одно из логических значений – аргументов НЕ меняет значение своего аргумента на противоположное логическое значение, обычно используется в сочетании с другими функциями

Вложенная функция ЕСЛИ В качестве Значения_если_истина и Значения_если_ложь может в свою очередь использоваться функция ЕСЛИ Это позволяет создавать условия любой сложности Допускается до семи уровней вложения

Функция ВПР Функции Вертикального просмотра (поиска значений) Функция просматривает первый столбец массива в поисках определенного значения и возвращает значение из ячейки в найденной строке и указанном столбце Синтаксис функции =ВПР(искомое_значение; таблица_массив; номер_индекса_столбца ; интервальный просмотр) Искомое_значение - это то значение, которое должно быть найдено в первом столбце таблицы_массива. Таблица_массив – это таблица, в которой осуществляется поиск данных. Номер_индекса_столбца – это номер столбца в таблице_массиве, из которого должно быть взято найденное значение. Интервальный_просмотр – это логическое значение, которое определяет тип просмотра. Если этот аргумент опущен или имеет значение ИСТИНА, то возвращается приблизительное наибольшее значение, которое меньше, чем искомое. Если аргумент имеет значение ЛОЖЬ, то ищется точное значение

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

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

Отслеживание зависимостей позволяет • просмотреть все влияющие ячейки. Будут указаны все ячейки, на которые есть ссылки в формуле активной ячейки • просмотреть все зависимые ячейки. Будут указаны все ячейки, в которых есть ссылка на активную ячейку • установить режим отображения формул • проверить ячейки таблицы на наличие ошибок и установить источник ошибки - прослеживается путь появления ошибки до ее источника Отслеживание зависимостей выполняется командами списка Зависимости формул меню Формулы

Функции проверок ошибочных значений Функции ЕОШ, ЕОШИБКА и ЕНД, позволяющие перехватывать ошибки и значения #Н/Д и предотвращать их распространение по рабочему листу Синтаксис функций: • =ЕОШ(значение) • =ЕОШИБКА(значение) • =ЕНД(Значение) Функции проверяют значение аргумента и определяют, содержит ли он ошибочное значение При наличии ошибки функции возвращают значение Истина, в противном случае - Ложь ЕОШИБКА отслеживает все ошибочные значения ЕОШ проверяет значение на все ошибки, за исключением #Н/Д ЕНД проверяет только появление значения #Н/Д Обычно функции ЕОШ, ЕОШИБКА и ЕНД используются в качестве логических выражений функции ЕСЛИ

Автоввод Позволяет автоматически закончить ввод на основе уже введенного текста Применяется, если при вводе текста несколько символов совпадают с текстом ячеек данного столбца Отменить автоввод можно, выполнив меню Файл, команда Параметры, вкладка Дополнительно, группа Параметры правки, флажок Автозавершение значений ячеек

Автозаполнение Предназначено для ввода повторяющихся данных или данных, продолжающих начатые ряды Работает с числами, датами, днями недели и месяцами

Автозаполнение для дат По умолчанию выделенный диапазон заполняется порядковыми датами, начиная с выделенной, с шагом 1 Для автозаполнения повторяющимися значениями следует нажать клавишу Ctrl Для управления процессом автозаполнения используется команда меню Файл, группа Редактирование, кнопка Прогрессия

Автозаполнение чисел По умолчанию выделенный диапазон заполняется повторяющимися значениями При нажатой клавише Ctrl производится заполнение порядковыми значениями с шагом 1 Для управления процессом автозаполнения используется команда меню Файл, группа Редактирование, кнопка Прогрессия

Автозаполнение текста Осуществляется в соответствии с заранее определенными списками значений Создание списков производится командой меню Файл, команда Параметры, вкладка Дополнительно, группа Общие, кнопка Изменить списки

Удаление содержимого ячеек • Нажатием клавиши Del • С помощью команды меню Файл, группа Редактирование, кнопка Очистить. В этом случае можно выбрать объект удаления: – все – удаляет содержимое ячеек, форматирование и примечания – форматы – удаляет из ячейки только ее форматирование – содержимое – удаляет из ячейки только ее содержимое – примечание – удаляет только примечание, относящееся к ячейке – гиперссылки – удаляет из ячейки только гиперссылки

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

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

Команда Вставить ячейки Позволяет производить копирование или перемещение ячеек с сохранением содержимого ячеек назначения (т. е. в режиме вставки) Для этого следует указать направление сдвига ячеек относительно указанного места: вправо или вниз

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

Защита данных на рабочем листе По умолчанию все ячейки, графические объекты, диаграммы, сценарии и окна изначально блокируются (защищаются), но эта защита не вступает в силу, пока не выполнена команда Защитить лист (меню Главная группа Ячейки список Формат) Установленные в диалоговом окне команды варианты защиты применяются только к текущему листу. После включения защиты нельзя изменить заблокированный элемент. При попытке сделать это на экран выводится соответствующее сообщение

Защита некоторых ячеек В большинстве случаев нет необходимости в блокировке всех ячеек рабочего листа Прежде чем защищать лист, следует выделить ячейки, которые нужно оставить незаблокированными, и выполнить команду меню Главная, группа Ячейки, список кнопки Формат, отжать кнопку Блокировать ячейку

Скрытие рабочих листов Содержимое рабочего листа книги можно скрыть. В этом случае строка ярлыков не содержит ярлык скрытого рабочего листа Выполняется командой меню Главная, группа Ячейки, список кнопки Формат, команда Скрыть или отобразить. В скрытых листах все данные и результаты вычислений сохраняются, они просто скрыты от просмотра Чтобы вернуть режим вывода содержимого листа, следует повторно выполнить ту же команду, в поле списка скрытых листов диалогового окна команды выбрать нужный и нажать клавишу Enter

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

Группировка листов Позволяет выполнять одновременно редактирование или форматирование нескольких рабочих листов Группировка листов происходит при их одновременном выделении. После этого все действия, выполняемые на одном листе, будут автоматически применяться к аналогичным ячейкам остальных листов, включенных в группу Для отмены группирования следует выполнить команду Разгруппировать листы оперативного меню

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

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

Применение форматов • Команда Формат ячейки – предназначена для выбора параметров форматирования и их применения. Позволяет выполнить детальное форматирование для каждой отдельной ячейки или диапазона ячеек • Меню Главная группа Стили список Форматировать как таблицу – предназначена для применения автоформатов – комбинаций встроенных форматов Excel, в соответствии с которыми выполняется форматирование выбранных ячеек. Автоформаты содержат все параметры форматирования Эта команда выполняется для быстрого форматирования рабочего листа • Команда Стили/Стили ячеек – предназначена для применения некоторого стандартного (заранее определенного) набора стилей. Стиль – это поименованная совокупность параметров форматирования, предназначенная для многократного использования. Команду целесообразно использовать, если состав и значения параметров форматирования удовлетворяют требованиям пользователя

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

Форматирование данных Заключается в задании форматов чисел и текста По умолчанию значения вводятся в соответствии с числовым форматом Общий: значения отображаются в том виде, в каком они были введены с клавиатуры. При этом анализируются значения и автоматически присваивается нужный формат Выбор формата данных осуществляется на вкладке Число диалогового окна команды Формат/ячейки Поле списка Числовые форматы содержит категории форматов

Числовые форматы • • • Для форматирования дробей и чисел с десятичной точкой в форматах используются следующие символы шаблонов: # - символ указывает, что в данную позицию выводятся только значащие цифры, незначащие нули не отображаются. Например, ###, # 123, 46 => 123, 5; 0, 12=>, 1 0 (ноль) – символ указывает на отображение незначащих нулей. Например, #, 00 1, 2=>1, 20; 1=>1, 00 ? – при использовании этого символа до или после десятичной точки вместо незначащих нулей отображаются пробелы. Например, ? ? ? 1, 2=> 1, 2 . - (пробел) - символ указывает на необходимость вставки пробела в качестве разделителя групп разрядов числа. Например, # ### 10000=>10 000 , - символ определяет положение десятичной точки Количество символов шаблона в формате определяет правила округления числа при выводе. Если дробная часть числа содержит больше цифр, чем символов шаблона в формате, то число округляется. Если целая часть числа содержит больше цифр, чем количество символов в шаблоне, то отображаются все значащие разряды

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

Форматы даты и времени • • • Для форматирования данных типа данных и времени используются следующие символы: Д – для отображения дней: Д – дни 1 – 31; ДД – 01 – 31; ДДД – Пн –Вс; ДДДД – Понедельник – Воскресенье М – для отображения месяца: М – 12; ММ – 01 – 12; МММ – янв – дек; ММММ – январь – декабрь; МММММ – первая буква месяца Г – для отображения лет: ГГ – 00 – 99; ГГГГ – 1900 – 9999 ч – для отображения часов: ч – 0 – 23; чч – 00 – 23 м – для отображения минут: м – 0 – 59; мм – 00 – 59 с – для отображения секунд: с – 0 – 59; сс – 00 – 59 0 (ноль) – для отображения долей секунд: ч: мм: сс. 00 AM/PM – для отображения двенадцатичасовой системы [ ] – для отображения интервалов времени. Позволяет выводит значения, превышающие 24 часа, 60 минут или 60 секунд

Форматы текста • • Форматы могут быть составными, т. е. иметь числовую и текстовую часть. Текстовая часть всегда является последней в формате. Символы шаблона: “” - двойные кавычки или обратная косая черта указывают на необходимость вставки, начиная с позиции шаблона, текстовых символов, следующих за ним в формате @ - указывает, что, начиная с этой позиции формата, могут выводиться любые символы (текст) * - указывает, что, начиная с позиции символа в формате, необходимо многократно вывести символ, следующий за * (пока столбец не окажется заполненным по ширине); _ - символ подчеркивания, используется для выравнивания содержимого ячейки; вместо него проставляется пробел, по ширине равный следующему за ним символу

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

Форматирование шрифтов Параметры формата шрифтов задаются на вкладке Шрифт диалогового окна команды Формат/ячейки. Можно определить: • вид шрифта • начертание • размер • цвет • вид подчеркивания • задать вывод данных в виде над- и подстрочного текста Если ячейка содержит числа и формулы, то применять форматирование шрифтов нельзя

Выравнивание содержимого ячеек Задается на вкладке Выравнивание диалогового окна команды Формат/ячейки Можно определить: Параметры выравнивания данных в ячейке • Параметры вертикального выравнивания содержимого ячейки • Параметры определения ориентации текста и задания угла поворота текста. Если угол поворота задан положительным значением, то текст поворачивается против часовой стрелке, если отрицательным – по часовой • Параметр Переносить по словам определяет необходимость переноса текста при достижении правого края ячейки. • Параметр Автоподбор ширины задает автоматическое изменение размера символов текста ячейки при изменении ширины столбца • Параметр Объединение ячеек

Обрамление ячеек Задается на вкладке Границы команды Формат/ячейки Можно определить: • тип линии; • толщину линии; • цвет линии • вид границы (внешние, внутренние, отдельные) Для отмены установленных границ следует выбрать Нет из группы Все

Палитра Задается на вкладке Вид диалогового окна команды Формат/ячейки Параметры: • палитра цветов • узор для заполнения Отмена установленного ранее оформления выполняется с помощью команды Главная/Редактирование/Очистить/ Очистить форматы

Ширина и высота ячеек Позволяет установить нужную ширину столбцов и высоту строк Элементом форматирования является также параметр Скрыть/показать столбцы/строки. Информация из скрытых строк/столбов не выводится на экран и не выводится при печати таблицы

Форматирование с помощью стиля Стиль – это поименованная совокупность параметров форматирования, предназначенная для многократного использования. Для применения встроенных стилей предназначена команда Формат/стиль Возможны следующие варианты создания пользовательского стиля: • Если совокупность параметров уже применена к ячейке или диапазону ячеек, то для создания стиля необходимо выделить ячейку или диапазон, выполнить команду Формат/стиль, ввести в поле Имя стиля новое имя, нажать ОК • Если стиль создается на основании совокупности форматов диапазона ячеек, которые отличаются друг от друга, то в стиль будут включены только те форматы, которые совпадают для указанного диапазона. • Создание стиля с указанием значений параметров. Для этого необходимо выполнить команду меню Главная группа Стили список Стили ячеек команда Создать стиль ячейки

Условное форматирование Это форматирование выделенных ячеек на основе условий, заданных числами и формулами. Предназначено для выделения данных. Если данные ячейки удовлетворяют заданным условиям, то к ячейке будут применены установленные форматы Выполняется с помощью команды меню Главная группа Стили список Условное форматирование подменю Правила выделения ячеек Критерий условного форматирования состоит из условных форматов <Что сравниваем> Операция сравнения <С чем сравниваем> Параметр Что сравниваем может быть задан значением выделенной ячейки или формулой выделенной ячейки, результатом вычисления которой является логическое значение Истина или Ложь Параметр С чем сравниваем может быть задан константой или формулой

Поиск ячеек с условным форматом Можно найти ячейки, на которые наложен условный формат Для этого следует выполнить команду меню Главная группа Редактирование список Найти и выделить команда Выделение группы ячеек переключатель Условные форматы Искать можно все ячейки, на которые наложен какой-либо условный формат, или только ячейки, формат которых совпадает с форматом текущей ячейки. Выбор варианта поиска осуществляется с помощью переключателя всех или этих же.

Графические средства Excel Построение диаграммы осуществляется с помощью меню Вставка группа Диаграммы Создание диаграмм производится мастером, которые по выделенным данным и указанному типу создает нужную диаграмму Создать диаграмму «По умолчанию» можно, выделив необходимый диапазон и нажав клавишу F 11. На отдельном листе будет создана гистограмма со всеми параметрами, заданными по умолчанию

Основные компоненты диаграммы • • Тип Исходные данные Оси диаграммы Заголовки диаграммы и осей Легенда диаграммы Заголовки значений Подписи данных Таблица данных

Тип диаграммы Диаграммы предназначены для наглядного отображения данных с целью их анализа Тип диаграммы выбирается в зависимости от целей анализа Круговая диаграмма отображает вклад каждого значения в общую сумму Гистограмма используется для непосредственного сравнения значений по категориям График используется для отображения зависимости одной величины от другой (развитие процесса во времени) Лепестковая диаграмма отображает значения относительно начала координат (при невозможности непосредственного сравнения) Пузырьковая диаграмма сравнивает три набора значений, относящихся к независимым измерениям Линейчатая диаграмма используется для отображения сравниваемых значений по горизонтали (значения представляют собой длительности)

Исходные данные Разбиваются по рядам (множество значений одного параметра) В свою очередь ряды состоят из точек – конкретных значений параметра По умолчанию считается, что диаграмма должна содержать меньше рядов, чем точек. Соответственно выбирается ориентация диаграммы Пользователь может изменить ориентацию диаграммы, нажав клавишу Строка/столбец в окне выбора источника данных

Оси диаграммы Наличие и количество осей определяется типом диаграммы. Пользователь может контролировать вывод осей на диаграмме (меню Макет группа Оси список Сетка) Если данные для оси X являются датами, то к оси автоматически применяется временное масштабирование Особенности временного масштабирования: • точки данных автоматически отображаются в возрастающем хронологическом порядке, даже если исходные значения дат не были отсортированы по возрастанию • точки располагаются с учетом относительных значений дат • в качестве цены деления на оси времени по умолчанию используется минимальная разница между значениями дат, но пользователь может изменить временной масштаб

Заголовки Пользователь может задать заголовок диаграммы и каждой из ее осей При выводе текста в диаграмме используются установленные по умолчанию шрифт, выравнивание и расположение Заголовки – это обычные поля надписей, которые можно перемещать, форматировать и редактировать после создания диаграммы Задаются заголовки диаграммы и осей в меню Работа с диаграммами (появляется на ленте при выделении диаграммы) меню Макет группа Подписи списки Название диаграммы и Название осей

Легенда диаграммы Это заголовки рядов, размещаемые по умолчанию в правой части диаграммы Выбор заголовков рядов производится автоматически, если в выделенный диапазон включены заголовки строк и столбцов. В противном случае ряды именуются как Ряд 1, Ряд 2 и т. д. Пользователь может изменить имя, введя нужный текст или ячейки с нужным текстом в поле Имя ряда диалогового окна Выбор источника данных Можно изменить местоположение или вообще удалить легенду из диаграммы Команды форматирования позволяют изменить используемый в диаграмме шрифт

Заголовки значений Идентифицирует точки в рядах данных Обычно отображаются вдоль оси X Выбор заголовков значений производится автоматически, если в выделенный диапазон включены заголовки строк и столбцов. В противном случае для точек используются стандартные обозначения 1, 2, 3 и т. д. Их можно изменить, введя свои текстовые значения или ссылки на диапазон рабочего листа с нужными значениями в поле Подписи горизонтальной оси (категории) окна Выбор источника данных

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

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

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

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

Форматирование рядов и маркеров данных При большом различии диапазонов изменения значений для разных рядов данных, можно отобразить один или несколько из них на вспомогательной оси. Вспомогательная ось обычно располагается в правой части диаграммы. Для этого следует выбрать Формат ряда данных и установить переключатель Построить ряд по вспомогательной оси В некоторых случаях диаграмма выглядит лучше, если разные ряды данных изображаются разными типами диаграмм. Для смешивания разных типов диаграмм следует выбрать команду Изменить тип диаграммы для ряда в контекстном меню Можно создать гистограмму или диаграмму с областями, в которых маркеры данных заменены рисунками. Команда Формат ряда данных вкладка Заливка переключатель Рисунок или текстура - выбрать текстуру из списка или вставить из файла. . Можно задать режим отображения маркеров отрицательных значений контрастным цветом. Команда – Формат ряда данных вкладка Заливка установить флажок Инверсия для чисел < 0. . Можно применять сглаживание к рядам данных на графиках и точечных диаграммах. Команда Формат ряда данных вкладка Тип линии флажок Сглаженная линия

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

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

Использование многоуровневых категорий На графиках можно применять группировку категорий (подписей по оси Х) При этом исходные данные должны быть правильно организованы

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

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

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

Правила форматирования таблицы данных • Желательно, чтобы рабочий лист должен содержат только одну таблицу • Если на рабочем листе кроме таблицы необходимо хранить и другие данные, таблицу необходимо отделить пустой строкой и пустым столбцом. Причем лучше не размещать другие данные слева и справа от таблицы • Заглавную строку лучше дополнительно отформатировать, чтобы выделить среди строк таблицы • Названия столбцов могут содержать до 255 символов • Не следует отделять заглавную строку от записей пустыми строками или строкой, содержащей линию из символа «дефис» • Таблица должна быть составлена так, чтобы каждый столбец содержал во всех строках однотипные значения • При вводе значения поля нельзя вставлять ведущие пробелы • В таблицах данных можно использовать формулы • Рекомендуется использовать для поля один формат

Форма данных • • Значительно упростить работу с записями таблицы помогает Форма Использование формы данных позволяет: добавить записи в таблицу организовать поиск записей в таблице редактировать данные таблицы удалять записи из таблицы Для начала работы с формой следует щелкнуть по одноименной кнопке (кнопку можно разместить на ленте, выбрав в команде Параметры вкладку Настройка ленты)

Сортировка таблиц данных Позволяет переупорядочить строки таблицы по значениям одного или более столбцов Выполняется командой Данные/Сортировка Кнопка Параметры в диалоговом окне этой команды позволяет указать дополнительные факторы: • учитывать регистр для различия прописных и строчных букв • задать сортировку по строкам или столбцам

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

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

Преимущества расширенного фильтра • возможность сохранения критериев и их многократного использования • возможность оперативного внесения изменений в критерии в соответствии с потребностями • возможность располагать результаты фильтрации в любой области рабочего листа

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

Варианты критериев расширенного фильтра • Критерий содержит одно или несколько условий, накладываемых на одно поле • Критерий содержит несколько условий, накладываемых на несколько полей одновременно ü Критерии связаны логической операцией ИЛИ • Используется вычисляемый критерий

Правила создания вычисляемого критерия • Имена полей в диапазоне критерия должны отличаться от имен полей исходного диапазона. Можно ввести новое имя или оставить ячейку пустой • В формуле вычисляемого критерия используется первая строка таблицы (первая ячейка в сравниваемом столбце) • если в формуле используются ссылки на ячейки списка, они задаются, как относительные • если в формуле используются ссылки на ячейки вне списка, они задаются, как абсолютные • не следует обращать внимания на результат, выдаваемой формулой в области критерия (ИСТИНА или ЛОЖЬ)

Формирование сводной информации Сводная информация может быть получена: • объединением данных с помощью промежуточных итогов • методом консолидации • формированием сводных таблиц

Подведение промежуточных итогов Выполняется командой Промежуточные итоги (меню ДАННЫЕ группа Структура) Для выполнения этой команды необходимо: • командой Сортировка упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги • выполнить команду Промежуточные итоги • в диалоговом окне Промежуточные итоги задать нужные параметры: ü из списка При каждом изменении в выбрать имя группообразующего поля ü из списка Операция выбрать функцию, необходимую для подведения итогов ü в списке Добавить итоги по выбрать поля, по которым необходимо подвести итог

Итоговые функции • • Для подведения промежуточных итогов могут использоваться следующие итоговые функции: Сумма Количество значений Среднее Максимум Минимум Произведение Другие статистически функции По умолчанию для числовых данных используется функция Сумма, а для текстовых Количество значений

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

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

Консолидация данных Это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе

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

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

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

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

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

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

Скачать презентацию Балльно-рейтинговая система Лекции 6 Лабораторные работы 16 балла

Excel.pptx

Количество слайдов: 146