Типы данных
В ячейки рабочего листа можно вводить данные следующих типов: числовые, символьные, формулы и функции, а также даты.
Число в Excel может состоять только из следующих символов: цифры от 0 до 9, +, -, (,), /, $, %, (.), Е, е. Запятая в числе интерпретируется как разделитель десятичных разрядов. Символ разделителя может быть изменен в приложении Язык и стандарты панели управления Windows.
Работа с формулами в excel
Формула — это краткая запись некоторой последовательности действий, приводящих к конкретному результату. Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис.
Все формулы в Excel должны начинаться со знака равенства. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение.
Формулы содержат вычисляемые элементы (операнды) и операторы. Операндами могут быть константы, ссылки или диапазоны ссылок, заголовки, имена, функции.
В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.
К арифметическим операторам относятся: +, -, *, /, %, ^.
Операторы сравнения используются для обозначения операций сравнения двух чисел. К операторам сравнения относятся: =, >, <, >=, <=, <>. Логические формулы могут содержать указанные операторы сравнения, а также специальные логические операторы:
#NOT#-логическое отрицание «НЕ»;
#AND#-логическое «И»
#OR#-логическое «ИЛИ».
Логические формулы определяют истинно или ложно выражение.
Адресные операторы объединяют диапазоны ячеек для осуществления вычислений.
Приоритет выполнения операций:
операторы ссылок (адресные) «:», «,», « »;
знаковый минус «-»;
вычисление процента %;
арифметические ^, *, /, +, -;
текстовые оператор &;
операторы сравнений =, <, >, <=, >=, <>.
После ввода формулы в ячейку рабочего листана экране в окне рабочего листа в ячейку выводится результат вычисления.
Ссылка является идентификатором ячейки или группы ячеек в книге. При создании формул, содержащих ссылки на ячейки, формула связывается с ячейками книги. Значение формулы зависит от содержимого ячеек, которые указывают ссылки, и оно изменятся при измени содержимого этих ячеек. С помощью ссылок в формулах можно ссылаться на той же ячейки, находящиеся на других листах книги, или в другой книге, и даже на данные другого приложения. Ссылки на ячейки других книг называются внешними. Ссылки на данные других приложений называются удаленными.
В Excel существуют три типа ссылок; относительные, абсолютные, смешанные.
Относительная ссылка указывает на ячейку, основывать на ее положении относительно ячейки, в которой находится формула. При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула =А1+В1,то при копировании ее в клетку С1 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1 - =В1+С1
Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем столбца и именем строки.
Смешанные ссылки – это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка 5D7.
Ссылки на ячейки других листов книги имеют следующий формат:
[имя книги]<имя листа>!ссылка на ячейку, например:[книга2]Лист 3!Е5:Е15
Вычисление-это процесс расчета формул с последующим выводом результатов в виде значений в ячейках, содержащих формулы. При изменении значений ячейках, на которые ссылаются формулы, значения формул обновляются (т. Е. происходит повторное вычисление). Этот процесс называется перерасчетом. Он затрагивает только те ячейки, которые содержат ссылки на изменившиеся ячейки.
Циклическая ссылка-это формула, которая зависит от своего собственного значения. При обнаружении циклической ссылки excel выдает сообщение об ошибке.
Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. В Microsoft Excel используют все листы, помещенные между начальным и конечным именами, указанные в ссылке.
Функция-это специальная, заранее подготовленная формула, которая заполняет операции над заданными значениями и возвращает результат. Значения, над которыми функция выполняет операции, используются аргументами. В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т.е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией. Excel допускает до семи уровней вложения функций в одной формуле.
В общем виде любая функция может быть записана в виде:
=<имя_функции>(аргументы)
Для ввода функций модно использовать Мастер функций, вызываемый нажатием на кнопки Вставка функции fx (в строке формул), либо нажатием клавиши SHIFT+F3, либо перейти на вкладку формулы, выбрать нужную категорию. Мастер функций позволяет выбрать нужную функцию из списка и выводит для нее панель формул. На панели формул отображаются имя и описание функций, количество и тип аргументов, возвращаемое значение. Далее выбрать необходимую функцию в поле Поиск функции можно ввести запрос с описанием операции, которую требуется выполнить, (например, по словам «сложение чисел» будет найдена функция СУММ). Кроме того, можно выбрать категорию в поле Категория. После этогои введите аргументы. Для ввода в качестве аргументов ссылок на ячейки нажмите кнопку свертывания диалогового окна (которая временно скрывает диалоговое окно), выделите ячейки на листе и нажмите кнопку развертывания диалогового окна. По завершении ввода формулы нажмите клавишу ВВОД.
Excel содержит широкий набор функций, позволяющих выполнять стандартные вычисления. Виды функций перечислены ниже.
арифметические и тригонометрические.
инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую).
информационные, предназначенные для определения типа данных, хранимых в ячейках.
логические, предназначенные для проверки выполнения условия или нескольуих условий (ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ).
статич=стические, предназначенные для выполнения статистического анализа данных.
финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.
функции без данных, предназначенные для анализа данных из списков или базы данных.
текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т.д.).
функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах.
нестандартные функции. Это функции, созданные пользователем для собственных нужд. Создание функций осуществляется с помощью языка Visual Basik.
Если при наборе формулы были допущены ошибки, то в ячейку будет выведено значение ошибки. В Excel определено семь ошибочных значений:
#ДЕЛ/0! – попытка деления на ноль. Эта ошибка обычно возникает, если в формуле делитель ссылается на пустую ячейку;
#ИМЯ? – в формуле используется имя, отсутствующее в саиске имен дталога Присвоение имени. Excel также вводит это ошибочное значение в том случеа, когда строка символов не заключена в двойные кавычки;
#ЗНАЧ! – выводится при указании аргумента или операнда недопустимого типа, например, введена математическая формула, которая ссылается на текстовое значение, а также в том случае, когда Excel не может исправить формулу средствами автоисправления;
#ССЫЛКА! – отсутсвует диапазон ячеек, на который ссылается формула (возможно, он удален);
#Н/Д – нет данных для вычислений. Аргумент функции или операнд формулы является ссылкой на ячейку, не содержащую данных. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д;
#ЧИСЛО! – задан неправильный аргумент функции, например, v(-5). #ЧИСЛО!, может также указывать на то, что значение формулы слишком велико или слишком мало и не может быть представлено на листе;
#ПУСТО! – в формуле указано перечисление диапазонов, но эти диапазоны не имеют общих ячеек.
Вложенные функции – это функции, в качестве одного из аргументов которых заданы другие функции. В формулу можно вложить до 64 функций.
Одну и ту же формулу можно быстро ввести в диапазон ячеек. Выделите нужный диапазон, введите формулу, а затем нажмите сочетание клавиш CTRL-ВВОД.
Для упрощения создания и изменения формул, а также для снижения необходимости ввода формул вручную и возникновения синтаксических ошибок рекомендуется использовать возможность автозавершения формул. После ввода знака равенства (=) и начальных букв (начальные буквы играют роль триггеров отображения) приложения Excel снизу ячейки выводит динамический список допустимых функций и имен. После ввода в формулу функции или имени с помощью триггера вставки (нажатия клавиши ТАВ или двойного щелчка элемента в списке) Excel выводит соответствующие аргументы. Если по мере ввода формулы вставить запятую, которая также может
Списком в Excel является таблица, строки которой содержат одноименную информацию. Список состоит из трех структурных элементов:
заглавная строка – это первая строка списка, состоящая из заголовков столбцов. Заголовки столбцов – это метки (названия) соответствующих полей;
запись – совокупность компонентов, составляющих описание конкретного элемента (строка таблицы);
поля – отдельные компоненты данных в записи (ячейки и столбце).
Существуют правила создания списка, которых необходимо придерживаться при его формировании, чтобы иметь возможность использовать так называемые функции списка.
Рабочий лист должен содержать только один список, т.к. некоторые операции, например, фильтрование, могут работать только с одним списком.
Если на рабочем листе кроме списка необходимо хранить и другие данные, список необходимо отделить пустой строкой и пустым столбцом. Причем лучше не размещать другие данные слева и справа от списка, иначе они могут быть скрыты во время фильтрации списка.
Заглавную строку лучше дополнительно отформатировать, чтобы выделить среди строк списка (использовать форматы, отличные от тех, которые применены к данным списка).
Метки столбцов могут содержать до 255 символов.
Не следует отделять заглавную строку от записи пустыми строками или строкой, содержащей линию из символов «дефис».
Список должен быть составлен так, чтобы столбец содержал во всех строках однотипные значения.
При вводе значения поля нельзя вставлять ведущие пробелы, это может привести к проблемам при поиске и сортировке.
В списках можно использовать формулы. Списки могут обрабатываться, как обычные таблицы.
Значительно упростить работу с записями списка позволяет Форма. Использование формы данных позволяет:
Добавить записи в список;
Организовать поиск записей в списке;
Редактировать данные записи;
Удалять записи из списка.
Сортировка списков – это переупорячивание одного или более столбцов. Для того, чтобы выполнить сортировку выберите столбец с данными в диапазоне ячеек или убедитесь, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные. На вкладке Главная в группе Редактирование выберите пункт Сортировка и Фильтр.
Для сортировки в определенном пользователем порядке можно использовать пользовательские списки. В Excel предоставляются встроенные пользовательские списки дней недели и месяцев года, однако также могут создаваться собственные пользовательские списки. Для этого нажмите кнопку Microsoft Office, нажмите кнопку Параметры Excel, выберите категорию Основные, а затем в группе Основные параметры работы с Excel нажмите кнопку Изменить списки. В диалоговом окне Списки нажмите кнопку Импорт, а затем дважды нажмите кнопку ОК. На вкладке Начальная страница, а затем выберите в списке в списке пункт Специальная сортировка. Отобразится диалоговое окно Сортировка. В группе Столбец и поле Сортировать по или Затем по укажите столбец для сортировки по настраиваемую списку. В поле Порядок выберите пункт Настраиваемый список. Выберите необходимый список в диалоговом окне Списке. В предыдущем примере был бы выбрал вариант Высокий, Средний, Низкий. Нажмите кнопку ОК.
Фильтрация – это быстрый способ выделения из списка подмножества данных для последующей работы с ними. В результате фильтрации на экран выводятся те строки списка, которые либо содержат определенные значения, либо удовлетворяют некоторому набору условий поиска, так называемому критерию. Остальные записи скрываются и не участвуют в работе до отмены фильтра.
Выделенное подмножество списка можно редактировать, форматировать, печатать, использовать для построения диаграмм.
В Microsoft Office Excel 2007 существует несколько способов фильтрации уникальных значений или удаления повторяющихся значений. Для фильтрации уникальных значений используйте команду Дополнительно в группе Фильтр и сортировка на вкладке Данные.
Для удаления повторяющихся значений используйте команду Удалить дубликаты в группе Работа с данными на вкладке Данные.
Чтобы выделить уникальные или повторяющиеся значения, используйте команду Условное форматирование в группе Стиль на вкладке Главная.
Чтобы выполнить отбор диапазона ячеек с помощью сложных условий (Условие, Ограничение, заданное для отбора записей, включаемых в список результатов запроса, отбора, воспользуйтесь командой Дополнительно (вкладка Данные, группа Сортировка и фильтр). Наряду с автофильтром используется расширенный фильтр. Использовать его разумно в следующих случаях:
Отбор по нескольким условиям в одном столбце, когда любые условия могут быть истинными
Отбор по нескольким условиям в нескольких столбцах, когда все условия должны быть истинными
Отбор по нескольким условиям в нескольких столбцах, когда любые условия могут быть истинными
Отбор по нескольким наборам условий, каждый из которых включает условия для нескольких столбцов
Отбор по нескольким наборам условий, каждый из котрых включает условия для одного столбца
Использование подстановочных знаков при отборе текстовых строк, в которых совпадают некоторые знаки
Использование формул для отбора значений, превышающих среднее значение для диапазона данных
Использование формул для отбора значений с учетом регистра
Между командой Дополнительно и командой Фильтр есть несколько важных отличий. Вместо меню «Автофильтр» отображается диалоговое окно Расширенный фильтр. Расширенные условия отьора вводятся в отдельный диапазон условий листа над диапазоном ячеек или таблицей, которые требуется отфильтровать. В Microsoft Office Excel в качестве источника расширенных условий отбора используется отдельный диапазон условий в диалоговом окне Расширенный фильтр.
-Графические формы представления информации.
-Формирование свободной информации.
Графическое представление данных повышает наглядность получаемых результатов и показывает соотношение различных значений динамику их изменения.
Удобство представления данных в графической форме во многом обусловлено тем, что автоматически выполняются выбор масштаба, разметка оси и цветное оформление. При изменении исходных данных в ячейках таблицы графические изображения автоматически корректируются.
Чтобы создать в Excel базовую диаграмму, которую впоследствии можно изменять и форматировать, сначала введите на лист данные для этой диаграммы. Затем просто выделите эти данные и выберите нужный тип диаграммы на ленте интерфейса Office Fluent (вкладка Вставка, группа Диаграммы). Приложение Excel позволяет представлять данные с помощью диаграмм разнообразных типов, можно создать комбинированную диаграмму. Диаграммы полностью совместимы с другими приложениями выпуска 2007 office, таким как Office PowerPoint 2007 Office Word 2007. Оба эти приложения содержат те же инструменты для создания диаграмм, что и Excel.
Основные компоненты диаграммы:
Тип диаграммы
Исходные данные диаграммы
Заголовки диаграммы
Оси диаграммы
Линии сетки
Легенда диаграммы
Заголовки значений
Подписи данных
Таблица данных Excel может внедрить диаграмму в рабочий лист или поместить ее на отдельном листе диаграммы.
Диаграмма сохраняется вместе с книгой в которой она находится. Диаграмма становится доступной, когда открыта книга.
В Excel имеется широкий ассортимент команд, с помощью которых можно изменять внешний вид диаграмм. Создав диаграмму, можно вносить в нее изменения. Например, можно изменить вид, добавить название диаграммы, переместить или скрыть легенду, а также добавить название дополнительные элементы диаграммы. Вместо того, чтобы добавлять элементы диаграммы и форматировать их вручную, можно быстро применить к своим данным встроенный макет или стиль диаграммы. В приложении Excel существует много удобных встроенных макетов и стилей, из которых можно выбрать нужный. Помимо применения встроенного стиля диаграммы можно легко изменить форматирование ее отдельных элементов, например, маркеров данных (Маркер данных. Полоса, область, точка, сегмент или другой элемент на диаграмме, соответствующий одному значению одной ячейки листа. Маркеры данных одного цвета на диаграмме образуют ряд данных.), области диаграммы, области построения, чисел и текста в названиях и подписях, что привлечет внимание и сделает диаграмму оригинальной. Можно также применять стили фигур и стили WordArt, а можно форматировать фигуры и текст в элементах диаграммы вручную.
Если нужно многократно использовать диаграмму, настроенную на определенные требования, ее можно сохранить как шаблон диаграммы (CRTX-файл) в папке шаблонов диаграммы. При создании диаграммы можно применить шаблон так же, как и другой встроенный тип диаграммы. Поскольку шаблоны диаграмм являются типами диаграмм, с их помощью можно изменять тип существующей диаграммы.
При форматировании диаграммы можно выполнять следующие действия.
изменить рассположене элементов диаграммы.
изменить размеры и переместить область построения. Область построения-это часть диаграммы, в которой отображаются данные.
форматировать текстовые элементы диаграммы (в том числе изменять ориентацию текста).
форматировать и изменять шкалы осей.
задавать вывод о форматирование сетки.
форматировать ряды и маркеры данных.
в некоторых случаях диаграмма выглядит лучше, если разные ряды данных изображаются разными типами диаграмм. Excel позволяет смешивать несколько различных типов диаграмм. Excel может применять сглаживание к рядам данных на графиках и точечных диаграммах.
отображать линии проекции в графиках и диаграммах с областями.
отделять сектора круга и кольца.
форматировать фоновые области.
настраивать объемные виды диаграммы.
добавлять, удалять, изменять данные в диаграммах, изменять порядок отображения рядов.
использовать многоуровневые категории. Excel позволяет группировать категории. Например, рядами данных являются месяцы, а категориями – различные города, в которых расположены предприятия, но категории классифицируются еще и по государствам, в которых находятся города.
применять линии тренда. Линия тренда-это линия регрессии, которая аппроксимирует (сглаживает) точки данных, или линия скользящего среднего.
Формирование сводной информации.
Excel содержит средства сводной информации для проведения анализа данных. Сводная информация может быть получена:
На основе данных нескольких таблиц, полученных в результате консолидации данных;
На основе данных сводной таблицы;
На основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т.д.
Для создания сводной таблицы необходимо выделить источник данных и запустить программу Мастер сводных таблиц. Чтобы создать сводную таблицу, на вкладке Вставка в группе Таблицы выберите раздел Сводная таблица, а затем пункт Сводная таблица.
Мастер сводных таблиц выполняет свою работу в диалоговом пежиме в четыре шага. На первом шаге выбирается источник данных. На втором шаге в зависимости от выбранного источника данных следует указать диапазон ячеек списка, по которому создается сводная таблица. На третьем шаге определяется структура сводной сводной таблицы: новый лист или существующий лист, то необходимо указать адрес ячейки левого верхнего угла диапазона, начиная с которого будет вставлена сводная таблица.
После создания отчета сводной таблицы или сводной диаграммы добавить к нему поля можно при помощи списка полей сводной таблицы. Для того, что бы изменить отчет сводной таблицы или диграммы используйте список полей для изменения порядка и их удаления. По умолчанию список полей сводной таблицы имеет две секции: секцию полей в верхней части – для добавления и удаления полей, и секцию макета в нижней части – для изменения порядка полей и их положения.
Вожно понимать, как работает список полей таблицы и как можно настроить различные типы полей так, чтобы добиться нужного результата при выводе отчета сводной таблицы или сводной диаграммы.
внешний источник данных содержит структурированные данные, организованные в одно или несколько полей (также называемых столбцами), которые выводятся в списке полей.
переместите поле в область фильтра отчета в списке полей, одновременно с этим в область фильтра отчета будет перемещено поле в отчете сводной таблицы.
переместите поле в область названия столбца в списке полей, одновременно с этим в область названия строки будет перемещено поле в отчете сводной таблицы.
Объединением данных промежуточных итогов;
Методом консолидации;
Формированием сводных таблиц.
При помощи команды Промежуточные итоги в группе Структура на вкладке Данные можно автоматически подсчитать промежуточные и общие итоги Вт списке для столбца. Промежуточные итоги подсчитываются с помощью итоговой функции. Общие итоги вычисляются на основе подробных данных. Если для книги выбран режим автоматического вычисления по формулам, то с помощью команды Промежуточные итоги можно автоматически пересчитывать значения промежуточных и общих итогов при каждом изменении подробных данных. Используя команду Промежуточные.
После выполнения команды Итоги создается структура, в которой данные (таблица) структурированы, т.е. разбиты на несколько уровней. С помощью уровней структуры можно управлять выводом данных соответствующего уровня из таблицы на экран, указывая, выводить данные или скрывать.
Консолидация – это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных:
Консолидация данных с помощью трехмерных ссылок;
Консолидация данных по расположению;
Консолидация данных по категориям.
Первый способ наиболее простой. Он позволяет объединить данные консолидируемых областей формулами.
Консолидация данных по расположению используется, если консолидируемые данные находятся в одном и том же месте разных листов и размещены в одном и том же порядке.
Консолидация данных по категориям используется, если данные исходных областей не упорядочены, но имеют один и тот же заголовки.
Сводная таблица – это таблица, предназначенная для более наглядного представления и анализа данных из существующих списков и таблиц.
Сводная таблица может быть создана:
На основе данных любой таблицы или области таблицы рабочего листа;
Перместите поле а область значений в списке полей, одновременно с этим в область значений будет перемещено поле в отчете сводной таблицы.
По умолчанию Excel генерирует общие итоги для всех внешних полей сводной таблицы и промежуточные итоги для всех внутренних полей за исключением самого внутреннего поля. Пользователь может как удалить создаваемые по умолчанию итоги, так и сгенерировать промежуточные итоги для самых внутренних полей.
К числовым полям, помещенным в область данных сводной таблицы, Excel по умолчанию применяет функцию Сумма, а к любым несисловым полям – Количество значений. Но можно применять и другие формы вычислений. Кроме того, в сводных таблицах можно использовать вычисляемые поля и элементы. Дополнительные формы вычислений задаются в диалоговом окне Вычисление поля сводной таблицы Вычисляемое поле – это новое поле, полученное с помощью операций над существующими полями сводной таблицы. Вычисляемый элемент – это новый элемент в существующем поле, полученный с помощью операций над другими элементами этого поля. После создания вычисляемых полей и элементов их можно использовать так, как будто они являются частью исходных данных. При создании вычисляемых полей и элементов можно использовать арифметические операции с любыми данными сводной таблицы (включая данные, генерируемые другими вычисляемыми полями и элементами),но нельзя ссылаться на данные рабочего листа, находящиеся вне таблицы.
Я открыла приложение Microsoft Excel;
Подготовила основной листа сметы.
Выделила ячейки А1:Е1, нажала кнопку « объединить ячейки и поместить в центре»
ввел текст : Стоимость салата «Любимый»
внесла данные в ячейки в соответствии с рис
Переименовала листы:
Лист1-Стоимость салата;
Лист2-Кукуруза консервированная;
Лист3-Томаты свежие;
Лист4-Огурцы свежие;
Лист5-Грудка куриная
Лист6-Перец болгарский
Лист7- Ананас консервированный
Лист8-Майонез
Лист9-Капуста пекинская
Лист10-Сметана
Для этого
Я установила курсор на ярлычке Лист 1(2,3,4,5,6) и вызвала правой кнопкой мыши контекстное меню;
выполнила команду Переименовать и ввела новое имя — стоимость салата(Кукуруза консервированная и т.д).
В ячейку С3 ввела текущую дату
Для этого перешла на закладку Формулы, нажала кнопку вставить функцию
В появившемся диалоговом окне выбрала категорию «дата/время», функцию- «сегодня»
2 раза нажала на ОК
Теперь после каждой загрузки конфигурации в ячейке С3 автоматически будет появляться значение текущей даты;
в ячейку F3 ввела текущий курс доллара.
Для того, чтобы весь список умещался на экране я выделила диапазон ячеек А1:А27, перешла на закладку Вид в группе Масштаб нажала кнопку Масштабировать выделенный фрагмент
