Как называются строки в базе данных ms excel

База данных в Excel

Хотя в MS Office для создания обширных сложно связанных баз данных и последующей работы с ними предназначена программа Access, миллионы пользователей по всему миру предпочитают создавать простые (и не очень) базы данных в Excel. Причин этому несколько, и самая.

. главная из них – широчайшая распространенность, доступность и известность программы Excel, имеющей огромную аудиторию пользователей в отличие от программы Access, в которой работают в основном профессиональные программисты!

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

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

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

Чуть-чуть теории.

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

Столбцы таблицы Excel – это поля базы данных, а строки – это записи базы данных.

Поле (столбец) содержит информацию об одном признаке для всех записей базы данных.

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

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

Все вышесказанное очень важно понимать, знать, и помнить!

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

Основные правила создания базы данных в Excel.

1. Первая строка базы данных должна содержать заголовки столбцов!

2. Каждая последующая строка базы данных должна содержать хотя бы одну заполненную данными ячейку.

3. Не следует применять объединенные ячейки в пространстве таблицы базы данных!

4. Каждый столбец должен содержать только один тип данных – или текст, или числа, или даты!

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

6. Необходимо присвоить диапазону базы данных имя.

7. Следует объявить диапазон базы данных списком.

Пример.

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

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

Загружаем программу MS Excel и приступаем к работе.

1. Заголовок базы данных «Выпуск металлоконструкций участком №2» располагаем в объединенных ячейках A6…F6, оставляя сверху рабочего листа несколько строк пустыми. Эти строки могут понадобиться в будущем при анализе данных.

Написанный заголовок не будет находиться в области базы данных! Строка №6 не имеет отношения к таблице базы данных, обратите на это внимание.

2. В ячейки A7…F7 записываем заголовки столбцов – полей базы данных.

3. Далее построчно в ячейки A8…E17 заносим записи о выпуске металлоконструкций.

4. В ячейку F8 вписываем формулу: =D8*E8 и копируем ее в ячейки F9. F17 .

5. Присваиваем диапазону базы данных имя.

Для этого выделяем область базы данных вместе с заголовками столбцов A7…F17; в главном меню выбираем «Вставка» — «Имя» — «Присвоить…». В выпавшем окне «Присвоение имени» пишем имя, например — БД2 и нажимаем на кнопку «ОК» – диапазону присвоено имя!

6. Объявляем диапазон базы данных списком.

Для этого вновь выделяем область базы данных вместе с заголовками столбцов; в главном меню выбираем «Данные» — «Список» — «Создать список».
В выпавшем окне «Создание списка» проверяем правильность указанной области расположения данных и наличие галочки у надписи «Список с заголовками». Нажимаем на кнопку «ОК» – список создан!

База данных в Excel готова!

Итоги.

Если активировать любую из ячеек внутри списка («встать мышью»), то мы увидим:

1. Объявленный список окаймлен синей жирной граничной линией.

2. На заголовки списка наложен автофильтр, кнопки которого появились в ячейках с заголовками столбцов.

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

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

Для чего мы уделили столько внимания созданию элементарной таблицы?! Это всем станет понятно, когда будем фильтровать, сортировать данные и формировать различные отчеты по запросам, но об этом читайте в последующих статьях. Эти азы работы с информацией в Excel я постараюсь изложить максимально кратко, интересно и просто. Правильная и продуманная предварительная подготовка «скелета» для хранения информации — это залог того, что в будущем вы легко найдете необходимые данные!

Как создать базу данных в Excel: разбираем на примере

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

Иллюстрация: Meery Mary для Skillbox Media

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

Специализированных программ для формирования и ведения баз данных много. В том же MS Office для этого есть отдельное приложение — Microsoft Access. Но и в Excel достаточно возможностей для создания простых баз и удобного управления ими. Есть ограничение — количество строк базы данных в Excel не должно быть больше одного миллиона.

В статье разберёмся:

база данных в Excel;
• как её создать и настроить;
• как работать с готовой базой данных в Excel — фильтровать, сортировать и искать записи.

Как выглядит база данных в Excel

База данных в Excel — таблица с информацией. Она состоит из однотипных строк — записей базы данных. Записи распределены по столбцам — полям базы данных.

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

Каждая запись — строка таблицы — относится к одному объекту базы данных и содержит информацию о нём. В нашем примере записи характеризуют продажи, совершённые менеджерами автосалона.

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

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

Создание базы данных в Excel

Шаг 1

Создаём структуру базы данных

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

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

• «Менеджер»;
• «Дата продажи»;
• «Марка, модель»;
• «Цвет»;
• «Год выпуска»;
• «Объём двигателя, л»;
• «Цена, руб.».

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

Шаг 2

Создаём записи базы данных

В нашем примере запись базы данных — одна продажа. Перенесём в таблицу всю имеющуюся информацию о продажах.

При заполнении ячеек с записями важно придерживаться одного стиля написания. Например, Ф. И. О. менеджеров во всех строках вводить в виде «Иванов И. И.». Если где-то написать «Иван Иванов», то дальше в работе с БД будут возникать ошибки.

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

В нашем примере данные в столбцах A, C и D должны быть в текстовом формате. Данные столбца B должны быть в формате даты — его Excel определил и присвоил автоматически. Данные столбцов E, F — в числовом формате, столбца G — в финансовом.

Чтобы изменить формат ячейки, выделим нужный столбец, кликнем правой кнопкой мыши и выберем «Формат ячеек».

В появившемся меню выберем нужный формат и настроим его. В нашем примере для ячейки «Цена, руб.» выберем финансовый формат, уберём десятичные знаки (знаки после запятой) и выключим отображение символа ₽.

Также изменить формат можно на панели вкладки «Главная».

Шаг 3

Присваиваем базе данных имя

Для этого выделим все поля и записи базы данных, включая шапку. Нажмём правой кнопкой мыши и выберем «Имя диапазона».

В появившемся окне вводим имя базы данных без пробелов.

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

Работа с базой данных в Excel

Сейчас в созданной базе данных все записи расположены хаотично — не упорядочены ни по датам, ни по фамилиям менеджеров. Разберёмся, как привести БД в более удобный для работы вид. Все необходимые для этого функции расположены на вкладке «Данные».

Фильтрация записей

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

Подробнее о фильтрах в Excel говорили в этой статье Skillbox Media.

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

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

Предположим, нужно показать только сделки менеджера Тригубова М. — нажмём на стрелку поля «Менеджер» и оставим галочку только напротив него. Затем нажмём «Применить фильтр».

Готово — в базе отражены данные о продажах только одного менеджера. На кнопке со стрелкой появился дополнительный значок. Он означает, что в этом столбце настроена фильтрация. Чтобы её снять, нужно нажать на этот дополнительный значок и выбрать «Очистить фильтр».

Записи БД можно фильтровать по нескольким параметрам одновременно. Для примера покажем среди продаж Тригубова М. только автомобили дешевле 2 млн рублей.

Для этого в уже отфильтрованной таблице откроем меню фильтра для столбца «Цена, руб.» и нажмём на параметр «Выберите». В появившемся меню выберем параметр «Меньше».

Затем в появившемся окне дополним условие фильтрации — в нашем случае «Меньше 2000000» — и нажмём «Применить фильтр».

Готово — фильтрация сработала по двум параметрам. Теперь БД показывает только те проданные менеджером авто, цена которых ниже 2 млн рублей.

Кроме этого, в Excel можно установить расширенный фильтр. Он позволяет фильтровать БД по сложным критериям сразу в нескольких столбцах. Подробно о том, как настроить расширенный фильтр, говорили в статье.

Сортировка записей

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

Для примера отсортируем сделки выбранного менеджера в хронологическом порядке — по датам. Для этого выделим любую ячейку в поле «Дата продажи» и нажмём кнопку «Сортировка».

В появившемся окне выберем параметр сортировки «От старых к новым» и нажмём «ОК».

Готово — теперь все сделки менеджера даны в хронологическом порядке.

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

Кроме стандартной сортировки в Excel, можно настроить сортировку по критериям, выбранным пользователем. Эта функция полезна, когда нужные критерии не предусмотрены стандартными настройками. Например, если требуется отсортировать данные по должностям сотрудников или по названиям отделов.

Подробнее о пользовательской сортировке в Excel говорили в этой статье Skillbox Media.

Поиск записей

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

Предположим, нужно найти в БД автомобиль стоимостью 14 млн рублей. Перейдём на вкладку «Главная» и нажмём на кнопку «Найти и выделить». Также быстрый поиск можно задавать с любой вкладки Excel — через значок лупы в правом верхнем углу экрана.

В появившемся окне введём значение, которое нужно найти, — 14000000 — и нажмём «Найти далее».

Технология работы со списками в Ехсеl

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

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

База данных в MS Excel это просто список, состоящий из одного или более столбцов.

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

Чтобы содержимое рабочего листа рассматривалось как база данных в MS Excel, необходимо придерживаться строгих правил:

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

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

На рабочем листе, содержащем список, выделяются следующие области:

• диапазон данных – область, где хранятся данные списка;
• диапазон критериев – область на рабочем листе, в которой задаются критерии для поиска информации. В диапазоне критериев указываются имена полей и отводится область для записи условий отбора;
• диапазон для извлечения – область, в которую MS Excel копирует выбранные данные из списка. Этот диапазон должен быть расположен на том же листе, что и список.

Существуют следующие способы ввода данных в список:

• использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды Данные → Форма;
• ввод данных во вставляемые в список пустые строки, т.е. непосредственно ввод данных;
• использование средства Автоввод и команды Выбрать из списка для ускорения работы;
• использование форм MS Access и дальнейший перенос данных на лист MS Excel;
• использование мастера шаблонов для преобразования рабочего листа в MS Excel в форму;
• применение VBA – соответствующая программа будет предоставлять форму или окно диалога для ввода данных и их последующего помещения в определенные ячейки рабочего листа MS Excel.

Электронная таблица, оформленная в MS Excel в виде списка, состоящего из одного или более столбцов, содержащих однородную информацию, представляет собой простейшую базу данных.

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

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

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

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

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

На рабочем листе, содержащем список, различают следующие области:

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

К наиболее часто используемым способам ввода данных в список относятся:

а) использование формы данных, которая автоматически создается после определения заголовка списка с помощью команды Данные → Форма;

б) ввод данных в пустые строки списка, т.е. непосредственный ввод данных;

в) использование средства Автоввод и команды Выбрать из списка для ускорения работы.

Работа с подготовленным списком в MS Excel может осуществляться по трем направлениям:

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

2. Фильтрация (отбор данных) – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями). Для поиска и фильтрации данных в MS Excel существует 3 средства: форма данных (кнопка Критерии), автофильтр и расширенный фильтр.

Отбор данных с помощью формы данных производится следующим образом: нужно установить указатель ячейки в любое место внутри списка, выбрать команду Данные → Форма, затем нажать кнопку Критерии; в открывшемся окне в необходимых полях ввести критерии поиска, например первую букву в названии (для перехода к записи, удовлетворяющей критерию, следует использовать кнопки Далее или Назад).

Задавая критерии можно пользоваться символами подстановки: * — для замены любого количества символов (например, Ж* может соответствовать таким наименованиям товаров как жакет и жилет), ? – для замены одного символа.

Поиск с помощью автофильтра производится в следующем порядке:

1. Установление указателя ячейки в список данных.
2. Выполнение команды Данные → Фильтр → Автофильтр. Возле каждого поля строки заголовка появятся раскрывающиеся списки в виде кнопки с треугольником.
3. Переход к нужному полю.
4. Выбор необходимого критерия поиска или использование пользовательского автофильтра Условие (больше или равно, не равно и т.д.), который позволяет также задать комплексный критерий, объединяя его компоненты с помощью логического оператора И либо ИЛИ.

Для отображения имеющихся значений (отмене условия) следует обратиться к фильтру Все.

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

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

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

В качестве условия отбора можно также использовать вычисляемое значение, являющееся результатом выполнения формулы. При создании условия отбора с помощью формулы нельзя выбирать заголовок столбца в качестве заголовка столбца условий, нужно оставить условие отбора без заголовка либо использовать заголовок, не являющийся заголовком столбца в списке. Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце C значение, превышающее среднее значение ячеек диапазона C7:C10.

=C7>СРЗНАЧ($C$7:$C$10)

Формула, используемая для создания условия отбора, должна использовать относительную ссылку на заголовок столбца (например, «Реализация») или на соответствующее поле в первой записи. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В данном примере «C7» является ссылкой на поле (столбец C) первой записи (строка 7) списка.

Чтобы объединить критерии с помощью оператора И, их следует указывать в одной строке. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения «Брюки» в столбце «Вид изделия», «Костюмная ткань» в столбце «Вид материала» с объемом реализации более 100 руб.

Вид изделия	Вид материала	Реализация
Брюки	Костюмная ткань	>100

Чтобы объединить критерии с помощью оператора ИЛИ, их следует указывать в различных строках. Так, при наличии для одного столбца двух и более условий отбора необходимо ввести эти условия отбора непосредственно друг под другом в отдельные строки. Например, следующий диапазон условий отбора отбирает строки, содержащие в столбце «Вид изделия» значения «Брюки», «Юбка» или «Бриджи».

Вид изделия

Брюки

Юбка

Бриджи

Для того чтобы найти данные, отвечающие одному условию, в одном столбце, или отвечающие другому условию, в другом столбце, следует ввести условия отбора в разные строки диапазона условий отбора. Например, следующий диапазон условий отбора отображает все строки, содержащие значение «Брюки» в столбце «Вид изделия», «Костюмная ткань» в столбце «Вид материала», либо объем реализации, превышающий 100 шт.

Вид изделия	Вид материала	Реализация
Брюки
Костюмная ткань
>100

Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, нужно ввести эти условия отбора в отдельные строки. Например, следующий диапазон условий отбора отображает строки, содержащие как значение «Брюки» в столбце «Вид изделия», так и объем реализации, превышающий 300 шт., а также строки по виду изделия «Юбка» с объемом реализации более 100 руб.

Вид изделия	Продажи
Брюки	>300
Юбка	>100

Для того чтобы найти строки, отвечающие более чем двум наборам условий, нужно включить несколько столбцов с одинаковыми заголовками. Например, следующий диапазон условий отбора возвращает реализацию товаров от 200 до 400 шт., а также реализация менее 50 шт.

Реализация	Реализация
>200	<400
<50

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

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

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

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

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

2) Автоматическое подведение итогов: итоги необходимы для создания разнообразных отчетов и для обобщения большого количества однотипной информации. Промежуточные итоги формируются с помощью команды Данные → Итоги. В диалоговом окне указывается, в каком столбце должны отслеживаться изменения данных, после каждого из которых производится выбранная вычисляемая операция. Также указывается, по какому столбцу будут производиться вычисления и где будут располагаться итоги. Перед применением команды к данным следует применить фильтр.

3) Консолидация данных — предназначается для обобщения однородных данных и выполняется в том случае, когда нужно подытожить данные, хранящиеся на разных листах или в различных книгах. При помощи функции консолидации для значений из несмежных диапазонов можно выполнить те же операции, что и при автоматическом определении промежуточных итогов: вычислить сумму, минимальное, максимальное или среднее значение и т. д.

4) Сводные таблицы и диаграммы — представляют собой средства для группировки, обобщения и анализа данных, находящихся в списках MS Excel или таблицах, созданных в других приложениях. Сводные таблицы могут использоваться: для обобщения большого количества однотипных данных; для отбора, группировки или реорганизации данных (с помощью перетаскивания), а также для построения диаграмм.

Рассмотрим принципы работы с инструментами Сводные таблицы и Консолидация данных.

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

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

В этом случае для расчета итоговых данных составляются обычные или так называемые, трехмерные формулы — ссылки вида ‘Лист1:Лист3’ !D3, которые содержат ссылки на диапазоны, включающие ячейки разных листов. Для их создания следует открыть первый лист из числа выбираемых, нажать клавишу Shift и, щелкая кнопкой мыши, указать остальные листы диапазона (они должны быть расположены подряд). После этого нужно:

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

Может получить выражение вида: = СУММ (поставщики!C4;’материал верха’!C5), где поставщики и материал верха – названия листов, С4 и С5 – ячейки листов «Поставщики» и «Материал верха» соответственно.

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

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

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

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

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

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

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

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

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

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

Примеры практического использования инструмента «Консолидация» приведены в учебном пособии.

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

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

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

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

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

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

После вызова мастера появляется диалоговое окно Мастер сводных таблиц и диаграмм — шаг 1 из 3, в котором указываются источник данных для сводной таблицы и тип отчета — сводная таблица или диаграмма.

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

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

В области Вид создаваемого отчета по умолчанию активизирован переключатель сводная таблица. После щелчка на кнопке Далее осуществляется переход к следующему диалоговому окну мастера.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Однако, если в источнике данных появились новые строки или столбцы, необходимо вернуться к мастеру сводных таблиц и диаграмм, а именно: вызвать команду Данные → Сводная таблица или открыть список Сводная таблица панели инструментов и выбрать команду Мастер, в результате чего откроется третье диалоговое окно мастера сводных таблиц, щелкнуть на кнопке Назад для возврата ко второму окну мастера и выбрать источник данных заново или нажать клавишу Shift и расширить область выделения, для закрытия окна мастера щелкнуть на кнопке Готово.

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

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

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

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

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

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

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

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

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

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

Электронные таблицы. Организация расчетов и построение диаграмм

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

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

Рисунок 1 – Основные функции электронных таблиц

Как правило, электронные таблицы предназначены для решения следующих задач:

— хранение разнородных данных в электронном виде в табличной форме;

— организация вычислений, выполняемых в автоматическом режиме;

— статистический анализ данных и поиск управленческих решений;

— построение графиков и диаграмм для наглядного представления данных;

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

Рабочий лист и книга, ячейка и ее адрес, диапазон ячеек

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

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

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

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

Ячейка Excel – это хранилище одного элемента данных таблицы, доступ к которому осуществляется по адресу ячейки – номерам столбца и строки, на пересечении которых находится ячейка. Например, ячейка, расположенная в столбце «B» строки «6», будет иметь адрес «B6».

Диапазоном ячеек называют прямоугольную область, охватывающую стразу несколько строк и/или столбцов. Такие области имеют составную адресацию. Например, диапазон, охватывающий столбцы от «A» до «E» и строки от «4» до «9» включительно, будет иметь адрес «A4:E9».

Интерфейс MS Excel: строка заголовка, строка меню, панель инструментов

Интерфейс электронной таблицы Excel видоизменяется с каждым выпуском, следуя общему стилю и функциональности всего пакета MS Office. Тем не менее некоторые ключевые элементы, такие как строка заголовка, меню и панель инструментов присутствуют в каждой версии.

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

Рисунок 2 – Строка заголовка Excel

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

Рисунок 3 – Строка меню

При выполнении определенных задач состав меню может динамически видоизменяться, дополняясь новыми пунктами. Например, при редактировании диаграмм добавляются «Конструктор диаграмм» и «Формат».

Рисунок 4 – Динамически добавляемые пункты меню

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

Рисунок 5 – Фрагмент панели инструментов для пункта меню «Главная»

Типы данных в Excel

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

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

Основные типы данных Excel:

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

Рисунок 6 – Команда контекстного меню для выбора формата данных

В появившемся окне «Формат ячеек», в первой его вкладке «Число», можно указать формат данных.

Рисунок 7 – Окно «формат ячеек»

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

Виды ссылок: абсолютные и относительные

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

Ссылка в Excel – это адрес элемента или группы элементов данных, заданный в абсолютном или относительном виде.

Относительная ссылка – это простой адрес вида «столбец, строка», используемый в качестве аргумента в формуле. Относительной она называется потому, что Excel запоминает расположение адресуемой ячейки относительно ячейки с формулой, и при изменении положения формулы на листе будет меняться и ссылка.

Примеры относительных ссылок: «B3», «F2», «AP34».

Абсолютная ссылка – это адрес вида «$столбец, $строка», ссылающийся на ячейку, позиция которой остается неизменной при перемещении ячейки с формулой. Допускается отдельно «фиксировать» столбец или строку, указывая перед ними знак «$».

Примеры абсолютных ссылок:

— на ячейку E32: «$E$32»;

— на столбец F: «$F2»;

Порядок создания формулы в Excel

Рассмотрим шаги создания формулы на примере произведения чисел.

При правильном выполнении всех шагов, в ячейке C1 отобразится произведение чисел из ячеек A1 и B1. Более того, это произведение будет автоматически изменяться при изменении множителей.

Ошибки при вводе формул

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

Самые распространенные ошибки при вводе формул:

«#ДЕЛ/0!» – произошло деление на ноль или на пустую ячейку;

«#Н/Д» – один из аргументов функции в данный момент недоступен;

«#ИМЯ?» – некорректно задано название функции или аргумента;

«#ПУСТО!» – указанный диапазон не содержит ячеек;

«#ЧИСЛО!» – ячейка содержит значение, которое нельзя преобразовать в число;

«#ССЫЛКА!» – ссылка некорректна;

«#ЗНАЧ!» – один или несколько аргументов функции принимают недопустимые значения.

Встроенные функции и их использование

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

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

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

Рисунок 13 – Библиотека функций на панели инструментов

Рассмотрим использование встроенных функций на конкретном примере.

Пример вычисления математической функции

Допустим, перед нами стоит задача определения среднего балла ученика по имеющемуся списку оценок.

Шаг 1. На пустом листе в столбце B создайте список дисциплин, а в столбце C – соответствующих им оценок. Под списком дисциплин разместите ячейку с текстом «Средний балл».

Шаг 2. Поместите курсор в ячейку столбца C, расположенную напротив ячейки с текстом «Средний балл». В меню выберите пункт «Формулы» и нажмите на панели инструментов кнопку «Вставить функцию».

Шаг 3. Из списка функций выберите «СРЗНАЧ» — вычисление среднего значения, и нажмите кнопку «ОК». Появится окно заполнения аргументов функции, в которое Excel уже автоматически вписал столбец оценок.

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

Шаг 5. Подтвердите выбор, нажав кнопку «ОК». В ячейке C10 при этом появится среднее значение.

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

Шаг 7. В появившемся окне выберите формат «Числовой», число десятичных знаков – 1.

Теперь расчет и отображение среднего балла работают как нам нужно.

Диаграмма. Виды и порядок построения диаграммы

Диаграмма в excel – это форма наглядного графического представления набора данных.

Доступ к панели инструментов «Диаграммы» осуществляется через меню «Вставка».

В Excel имеется множество шаблонов диаграмм, объединенных в группы, самые популярные среди которых:

Рассмотрим пошаговый порядок построения диаграммы успеваемости по четвертям учебного года. В качестве наиболее подходящего вида диаграммы определим столбчатую (гистограмму).

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

Шаг 2. Выделите на листе область, охватывающую все введенные данные и подписи.

Шаг 3. В меню «Вставка» — «Диаграммы» выберите первый элемент – «Гистограмма».

Шаг 4. Проверьте корректность создания диаграммы, при необходимости отмените шаги 2,3 и выделите диапазон заново.

Шаг 5. Измените название диаграммы. Щелкнув по нему мышью, введите «Успеваемость по четвертям».

Шаг 6. Слева на оси оценок мы видим значения 0 и 6. Таких оценок не бывает, поэтому исправим формат вывода. Наведите курсор мыши на ось оценок, нажмите правую кнопку и выберите «Формат оси…».

Шаг 7. В открывшемся окне параметров введите минимум – 1, максимум – 5, основные и промежуточные деления – 1.

Шаг 8. Добавьте к диаграмме таблицу оценок, нажав кнопку «+» в правом верхнем углу диаграммы и выбрав «Таблица данных».

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

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