Формулы и математические вычисления в Excel

Ввод формулы начинается со знака "=" (равно) после которого следует математическое выражение. Оно может включать в себя константы, ссылки на ячейки, функции, знаки математических действий" + " (сложить), "-" (вычесть), " / " (поделить), " * " (умножить), " ^ " (возвести в степень). Если в формулевстречаетсяссылка на ячейку, то при вычислении используется значение, находящееся в этой ячейке.

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

Абсолютная и относительная адресация

Ссылки на ячейки вида A12, С41, которые при копировании формул пересчитываются, называются относительными ссылками. В таких ссылках при копировании формулы на строку вниз к номеру строки каждой ссылки добавляется единица: например,формула =A12+С41 преобразуется в формулу =A13+С42.

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

Абсолютная ссылка в формуле, в отличие от относительной, указывает именно ту ячейку, которая записана и не зависит от местоположения формулы, выглядит $A$12, то есть перед буквой и перед цифрой стоит знак доллара.

Чтобы преобразовать относительную ссылку в абсолютную, нужно в формуле, использующей данную ссылку поставить курсор в любое место ссылки и нажать на клавиатуре кнопку F4 или вручную, поставить знаки доллара перед номером строки или столбца.

Ссылки, в которых закреплен только номер строки A$12 или только номер столбца $A12 называются смешанными.

Функции в математической обработке данных

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

Имя функции может содержать русские и латинские буквы, а также цифры.

Аргументы функции записываются сразу после имени в круглых скобках, разделяются точкой с запятой. Есть функции, которые не требуют аргументов. Тогда скобки всё равно записывают, но оставляют их пустыми. Пример – функция СЕГОДНЯ(). Аргументом функции может быть числовая константа, ссылка на ячейку или на диапазон ячеек.

Функции для удобства подразделяются на категории:

Наименование функций

Написание функций

Выполняемые действия (функция возвращает

Математические

ПИ

=ПИ()

Не имеет аргументов

Число Пи 3,1415926535897932

СУММ

=СУММ(число1;число2…)

Сумму по диапазону ячеек

Статистические

МАКС

=МАКС(число1;число2…)

Максимальное по диапазону ячеек

МИН

=МИН(число1;число2…)

Минимальное по диапазону ячеек

СРЗНАЧ

=СРЗНАЧ(число1;число2…)

Среднее значение по диапазону ячеек

СЧЕТ

=СЧЕТ(значение1;значение2…)

Подсчитывает количество чисел в списке аргументов

СЧЕТЗ

=СЧЕТЗ(значение1;значение2…)

Подсчитывает количество непустых ячеек

СЧЕТЕСЛИ

=СЧЕТЕСЛИ(диапазон;критерий)

Подсчитывает количество ячеек, удовлетворяющих условию

Дата и время

СЕГОДНЯ()

=СЕГОДНЯ()

Текущую дату в формате даты

ТДАТА

=ТДАТА()

Текущую дату и время в формате даты и времени

ДАТА

=ДАТА(год;месяц;день)

Число, представляющее дату в коде даты-времени

МЕСЯЦ

=МЕСЯЦ(дата_в_числовом_формате)

Месяц в виде числа от 1(январь) до 12(декабрь)

ЧАС

=ЧАС(время_в_числовом_формате)

Часы в виде числа от 0 до 23

МИНУТЫ

=МИНУТЫ(время_в_числовом_формате)

Минуты в виде числа от 1до 59

Логические

=И(логическое_значение1;логическое_значение2…)

Значение ИСТИНА, если все аргументы ИСТИНА

ИЛИ

=ИЛИ(логическое_значение1;логическое_значение2…)

Значение ИСТИНА, если хотя бы один аргумент ИСТИНА

ЕСЛИ

=ЕСЛИ(логическое_выражение1; значение_если_истина; значение_если_ложь)

Если выполняется условие, то возвращает значение_если_истина, в противном случае возвращает значение_если_ложь

НЕ

=НЕ(логическое_значение1)

Изменяет значение ИСТИНА на значение ЛОЖЬ, и наоборот

Текстовые

ТЕКСТ

=ТЕКСТ(значение;формат)

Преобразует число в текст

ПРАВСИМВ

=ПРАВСИМВ(текст;количество_знаков)

Указанное количество знаков с конца строки

ЛЕВСИМВ

=ЛЕВСИМВ(текст;количество_знаков)

Указанное количество знаков с начала строки

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

Диаграммы

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

Работа с Мастером диаграмм состоит из четырёх шагов.

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

Определение диапазона источника данных диаграммы. Задание числа рядов данных и их имён, выбор таблиц категорий (ось X) и значений (ось Y);

Настройка параметров диаграммы. На этом шаге можно ввести заголовки всей диаграммы и ее осей, отобразить подписи данных и легенду.

Выбор размещения диаграммы - на отдельном листе или на текущем.

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

Списки в Excel

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

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

Записи таблицы удобно просматривать и редактировать, используя электронную форму. Электронная форма- это специальный электронный бланк, в котором последовательно может отображаться каждая запись таблицы. Форма может быть вызвана командой (Данные - Форма). При этом курсор должен быть внутри таблицы (списка).

Чтобы найти нужную запись в форме нужно нажать кнопку Критерии.В открывшейся пустой форме ввести необходимые критерии,нажимая кнопкуДалее,в форме можно будет последовательно просмотреть отобранные записи. В форме можно изменить любую запись, чтобы сохранить изменения, необходимо нажать кнопку Enter. Для добавления записи, следует нажать кнопкуДобавить.

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

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

Перед тем как пользоваться Расширенным фильтром, надо создать диапазон условий, в котором записать или скопировать заголовки полей, по которым ведется отбор, и записать условия отбора. При записи условий отбора надо помнить, чтодаты вводятся в виде 1.01.65, после - это знак >, а до или раньше - это знак <, текстовая информация вводится в кавычках через знак =, например ="жен".

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

Глава 3. Базы данных

Базы данных.

База данных (БД) – это объективная форма представления и организации совокупности данных (статей, расчетов и т. д.), систематизированных таким образом, чтобы эти данные могли быть найдены и обработаны с помощью ЭВМ^{^[1]}.

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

Для создания и работы с БД используется система управления базами данных (СУБД) – специализированная программа (чаще комплекс программ), предназначенная для организации и ведения базы данных.

Можно выделить следующие модели баз данных: иерархические, сетевые, реляционные, объектно-ориентированные, объектные, объектно-реляционные.

Наиболее часто встречаются первые четыре.

– В Иерархической модели БД одни объекты вкладываются в другие по древовидному признаку: каждый нижестоящий объект подчиняется только одному вышестоящему. Так организована файловая структура жесткого диска.

– В Сетевой модели БД каждый элемент может быть связан с любым другим элементом базы.

– В Реляционной модели БД все данные хранятся в двумерных, связанных между собой таблицах.

– В Объектно-ориентированной БД данные оформлены в виде моделей объектов, включающих прикладные программы, которые управляются внешними событиями.

СУБД Access. Объекты БД

СУБД Access поставляется в пакете MicrosoftOfficeProfessional и позволяет создавать базы данных, реляционного типа. БД Access представляет собой файл с расширением .mdb (MicrosoftDataBase) и состоит из набора объектов: таблиц, запросов, форм, отчетов, страниц, макросов и модулей. Создание и выбор объекта выполняется в основном окне программы. Каждый из объектов базы данных Access имеет свое имя, но все они хранятся на диске в одном файле. Имена объектов разного типа могут совпадать (таблица Товары и форма Товары).

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

Запросы – это объект базы данных, выполняющий отбор сведений или изменение существующих данных по желанию пользователя.

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

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

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

Отчеты – это объект базы данных, представляющий собой выборку сведений из базы данных, подготовленную к печати на принтере.

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

Страницы – это объекты базы данных, подготовленные к просмотру через Internet.

Макросы – это программы на Visual Basic, автоматизирующие последовательность действий.

Модули – это программы, расширяющие функциональные возможности СУБД Access.