1 || || 2 || || Актуальные статьи || || Цифра || || Автолюбитель || || Компьютерные азы || || ПОСТАПОКАЛИПСИС: без компьютеров || || Назад

Списки, базы данных, сводные таблицы в программе Excel

Создание пользовательских списков в Excel

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

Чтобы создать пользовательский список, надо выполнить следующие действия:

1. В меню "Сервис" выбрать команду "Параметры" и в открывшемся окне диалога щелкнуть на вкладке "Списки".

2. В списке "Списки" выбрать пункт НОВЫЙ СПИСОК и в списке "Элементы списка" ввести значения, которые надо включить в данный список. Обязательно надо вводить в том порядке, в котором они должны появляться в рабочем листе.

3. Нажать кнопку "Добавить", чтобы включить свой список в перечень пользовательских списков.

4. Нажать ОК.

Пример 1: Менеджер по рекламе: чтобы понять на какие дни недели давать интернет-рекламу, необходимо проанализировать самые активные дни в интернете по определенной группе товаров и услуг.

Пример 2: Руководитель отдела продаж: в какие дни недели за последние три месяца в офис поступает больше всего звонков от потенциальных клиентов?

Решение

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

1. ДЕНЬНЕД ( дата_в_числовом_формате ;тип)
Дата_в_числовом_формате - это число, соответствующее дате, день недели которой необходимо найти.
Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Проблемы могут возникнуть, если даты вводятся как текст.
Тип - это число, которое определяет тип возвращаемого значения. Т.е. если мы хотим, чтобы первый день недели был понедельник и его номер был равен единице, то мы ставим тип=1.

Тип Описание 1 или опущен Число от 1 (воскресенье) до 7 (суббота). Аналогично предыдущей версии Microsoft Excel
2 Число от 1 (понедельник) до 7 (воскресенье)
3 Число от 0 (понедельник) до 6 (воскресенье)

2. ТЕКСТ(значение;формат)
Значение - либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение.
Формат - числовой формат в текстовой форме из списка Числовые форматы с вкладки Число диалогового окна Формат ячеек.
Конечная формула Предположим, что в ячейке B2 заведена дата в формате ДАТА.

Тогда итоговая формула имеет вид: =ТЕКСТ(ДЕНЬНЕД(B2;1);"дддд") " "дддд" - название дня недели будет полное, т.е. "понедельник", "вторник" и т.д. " "ддд" - название дня недели будет сокращенное, т.е. "Пн.", "Вт." и т.д.

Сортировка базы данных в Excel

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

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

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

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

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

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

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

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

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

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

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

Сводная таблица

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

Сводная таблица в Excel

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

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

Для удобной работы со сводными таблицами Excel предоставляет в распоряжение пользователя специальную панель инструментов - Сводные таблицы.

Создание сводной таблицы пошагово:

1. Поместите указатель ячейки в любой позиции в пределах списка.
2. Программа в этом случае автоматически определит список. Выберите команду Данные/Сводная таблица. Запускается мастер сводных таблиц и диаграмм. Укажите источник данных.
3. По умолчанию переключатель установлен в положение В списке или базе данных Microsoft Excel.
4. Укажите, что должен создать мастер: сводную таблицу иди сводную диаграмму и таблицу. Нажмите кнопку Далее.
5. Во втором окне мастера проверьте ссылку на диапазон с исходными данными, предлагаемый программой автоматически.
6. При необходимости измените диапазон с помощью мыши. После нажатия кнопки Обзор вы получаете доступ к закрытым рабочим книгам.
7. В третьем окне мастера сводных таблиц и диаграмм в поле Поместить таблицу в автоматически выбрана опция Новый лист.
8. В результате Excel создаст сводную таблицу на отдельном листе, который будет вставлен перед текущим листом в рабочую книгу, и отобразит во вставленном листе сводную таблицу, начиная с ячейки А1.
Если вы хотите разместить сводную таблицу на существующем листе, то выберите опцию Существующий лист и укажите левую верхнюю ячейку диапазона для размещения сводной таблицы.
9. Нажав кнопку Макет, определите макет сводной таблицы.

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

10. Нажмите кнопку Готово.

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

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

Терминология сводных таблиц в Excel

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

  • Урок второй. Использование мыши при вводе и редактировании

  • Урок третий. Создание пользовательских списков в Excel

  • Урок четвертый. Автозамена в Excel

  • Урок пятый. Ввод и заполнение данных в ячейки Excel

  • Урок шестой. Структура и ввод формул

  • Урок седьмой. Защита от несанкционированного доступа