Меню

Модель ячейки таблицы excel

Преимущества сводной по Модели Данных

При построении сводной таблицы в Excel в первом же диалоговом окне, где нас просят задать исходный диапазон и выбрать место для вставки сводной, есть внизу неприметная, но очень важная галочка — Добавить эти данные в Модель Данных (Add this data to Data Model) и, чуть выше, переключатель Использовать модель данных этой книги (Use Data Model of this workbook) :

Построение сводной по модели данных

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

Однако, перед тем, как рассматривать эти «плюшки» вблизи, давайте сначала разберёмся с тем, что такое, собственно, эта Модель Данных?

Что такое Модель Данных

Модель Данных (сокращенно — МД или DM=Data Model) — это специальная область внутри файла Excel, куда можно где можно хранить табличные данные — одну или несколько таблиц связанных, при желании, между собой. По сути, это маленькая база данных (OLAP-куб), встроенная внутрь книги Excel. По сравнению с классическим хранением данных в виде обычных (или умных) таблиц на листах самого Excel, у Модели Данных есть несколько серьезных преимуществ:

  • Размер таблиц может достигать 2 млрд. строк, а на лист Excel вмещается чуть больше 1 млн.
  • Не смотря на гигантские размеры, обработка таких таблиц (фильтрация, сортировка, вычисления по ним, построение сводных и т.д.) выполняются очень быстро — гораздо быстрее, чем в самом Excel.
  • С данными в Модели можно производить дополнительные (при желании — весьма сложные) вычисления с помощью встроенного языка DAX.
  • Вся информация, загруженная в Модель Данных, очень сильно сжимается с помощью специального встроенного архиватора и весьма умеренно увеличивает размер исходного Excel-файла.

Управлением Моделью и вычислениями по ней занимается специальная встроенная в Microsoft Excel надстройка — Power Pivot, о которой я уже писал. Чтобы её включить, на вкладке Разработчик нажмите кнопку Надстройки COM (Developer — COM Add-ins) и поставьте соответствующую галочку:

Подключаем надстройку Power Pivot

Если вкладки Разработчик (Developer) у вас на ленте не видно, то включить её можно через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) . Если же в показанном выше окне в списке COM-надстроек у вас нет Power Pivot, то значит она не входит в вашу версию Microsoft Office 🙁

На появившейся вкладке Power Pivot будет большая салатового цвета кнопка Управление (Manage) , нажатие на которую и откроет поверх Excel окно Power Pivot, где мы и увидим содержимое Модели Данных текущей книги:

Главное окно Power Pivot

Важное замечание по ходу: книга Excel может содержать только одну Модель Данных.

Грузим таблицы в Модель Данных

Для загрузки данных в Модель сначала превращаем таблицу в динамическую «умную» сочетанием клавиш Ctrl + T и даём ей понятное имя на вкладке Конструктор (Design) . Это обязательный этап.

Затем можно использовать любой из трех способов, на выбор:

  • Жмём кнопку Добавить в модель (Add to Data Model) на вкладке Power Pivot на вкладке Главная (Home) .
  • Выбираем команды Вставка — Сводная таблица (Insert — Pivot Table) и включаем флажок Добавить эти данные в Модель данных (Add this data to Data Model) . В этом случае по загруженным в Модель данным сразу строится ещё и сводная таблица.
  • На вкладке Данные (Data) жмём на кнопку Из таблицы/диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query. Этот путь самый долгий, но, при желании, здесь можно произвести дополнительную зачистку данных, правки и всяческие трансформации, в которых Power Query очень силён.
    Затем причёсанные данные выгружаются в Модель командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. ) . В открывшемся окне выбираем вариант Только создать подключение (Only create connection) и, главное, ставим галочку Добавить эти данные в Модель данных (Add this data to Data Model) .

Строим сводную по Модели Данных

Чтобы построить сводную Модели Данных можно использовать любой из трёх подходов:

  • Нажать кнопку Сводная таблица (Pivot Table) в окне Power Pivot.
  • Выбрать в Excel команды Вставка — Сводная таблица и переключиться в режим Использовать модель данных этой книги (Insert — Pivot Table — Use this workbook’s Data Model) .
  • Выбираем команды Вставка — Сводная таблица (Insert — Pivot Table) и включаем флажок Добавить эти данные в Модель данных (Add this data to Data Model) . Текущая «умная» таблица будет загружена в Модель и по всей Модели будет построена сводная таблица.

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

Преимущество 1. Связи между таблицами без помощи формул

Обычная сводная может быть построена только по данным из одной исходной таблицы. Если же у вас их несколько, например, продажи, прайс, справочник по клиентам, реестр договоров и т.д., то сначала придется собирать данные из всех таблиц в одну с помощью функций типа ВПР (VLOOKUP) , ИНДЕКС (INDEX) , ПОИСКПОЗ (MATCH) , СУММЕСЛИМН (SUMIFS) и им подобных. Это долго, муторно и вгоняет ваш Excel в «задумчивость» при большом количестве данных.

В случае сводной по Модели Данных всё гораздо проще. Достаточно один раз настроить связи между таблицами в окне Power Pivot — и дело в шляпе. Для этого на вкладке Power Pivot жмём кнопку Управление (Manage) и затем в появившемся окне — кнопку Представление диаграммы (Diagram View) . Останется перетащить общие (ключевые) названия столбцов (поля) между таблицами, чтобы создать связи:

Создание связей между таблицами

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

Строим сводну по модели

Преимущество 2. Подсчёт количества уникальных значений

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

Щёлкаем правой кнопкой мыши по полю — команда Параметры полей значений и на вкладке Операция выбираем Число разных элементов (Distinct count) :

Посчет количества уникальных элементов

Преимущество 3. Свои формулы на языке DAX

Иногда в сводных таблицах приходится выполнять различные дополнительные вычисления. В обычных сводных это делается с помощью вычисляемых полей и объектов, а сводной по Модели Данных для этого используются меры на специальном языке DAX (DAX = Data Analysis Expressions).

Для создания меры выберите на вкладке Power Pivot команду Меры — Создать меру (Measures — New measure) или просто щёлкните правой кнопкой мыши по таблице в списке полей сводной и выберите Добавить меру (Add measure) в контекстном меню:

Добавляем меру

В открывшемся окне задаём:

Параметры меры

  • Имя таблицы, где созданная мера будет храниться.
  • Название меры — любое понятное вам имя для нового поля.
  • Описание — по желанию.
  • Формула — самое главное, т.к. здесь мы либо вручную вписываем, либо жмём на кнопку fx и выбираем из списка функцию DAX, которая должна вычислять результат, когда мы потом забросим нашу меру в область Значений.
  • В нижней части окна можно сразу задать для меры числовой формат в списке Категория.

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

Преимущество 4. Свои иерархии полей

Часто при создании типовых отчётов приходится забрасывать в сводные таблицы одни и те же комбинации полей в заданной последовательности, например Год-Квартал-Месяц-День, или Категория-Товар, или Страна-Город-Клиент и т.п. В сводной по Модели Данных эта проблема легко решается созданием собственных иерархий — пользовательских наборов полей.

В окне Power Pivot переключитесь в режим диаграммы кнопкой Представление диаграммы на вкладке Главная (Home — Diagram View) , выделите с Ctrl нужные поля и щёлкните по ним правой кнопкой мыши. В контекстном меню будет команда Создать иерархию (Create hierarchy) :

Создание иерархии полей

Созданную иерархию можно переименовать и перетащить в неё мышью требуемые поля, чтобы потом в одно движение забрасывать их в сводную:

Добавление иерархии в сводную

Преимущество 5. Свои наборы элементов

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

Для этого на вкладке Анализ сводной таблицы в выпадающем списке Поля, элементы и наборы есть соответствующие команды (Analyze — Fields, I tems & Sets — Create set based on row/column items) :

Создание наборов в сводной по модели данных

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

Создаем набор

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

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

Преимущество 6. Выборочное скрытие таблиц и столбцов

Это хоть и небольшое, но весьма приятное в некоторых случаях преимущество. Щёлкнув правой кнопкой мыши по названию поля или по ярлычку таблицы в окне Power Pivot, можно выбрать команду Скрыть из набора клиентских средств (Hide from Client Tools) :

Читайте также:  Краткая история развития транспорта

Скрываем поле или таблицу от пользователя

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

Преимущество 7. Продвинутый drill-down

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

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

Экспресс-тенденции

После этого текущее значение (Модель = Explorer) уйдет в область фильтра, а сводная будет построена уже по офисам:

Результат drill-down

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

Преимущество 8. Преобразование сводной в функции кубов

Если выделить любую ячейку в сводной по Модели Данных и выбрать затем на вкладке Анализ сводной таблицы команду Средства OLAP — Преобразовать в формулы (Analyze — OLAP Tools — Convert to formulas) , то вся сводная будет автоматически преобразована в формулы. Теперь значения полей в области строк-столбцов и результаты в области значений будут извлекаться из Модели Данных с помощью специальных функций кубов: КУБЗНАЧЕНИЕ и КУБЭЛЕМЕНТ:

Преобразование сводной в функции кубов

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

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

Источник

Excel 2010 для начинающих: Создание первой электронной таблицы

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

Оглавление

  • Введение
  • Интерфейс и управление
  • Ввод и редактирование данных
  • Форматирование данных
  • Формат данных ячеек
  • Заключение

Введение

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

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

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

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

table_excel

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

Интерфейс и управление

Если вы уже знакомы с редактором Word, то разобраться в интерфейсе Excel не составит труда. Ведь в его основе лежит та же Лента, но только с другим набором вкладок, групп и команд. При этом чтобы расширить рабочую область, некоторые группы вкладок выводятся на дисплей только в случае необходимости. Так же ленту можно свернуть вовсе, щелкнув по активной вкладке два раза левой кнопкой мыши или нажав сочетание клавиш Ctrl+F1. Возвращение ее на экран осуществляется таким же способом.

Lenta_excel

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

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

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

Parametry_Excel

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

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

Panel_bystrogo_dostupa_excel

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

Ввод и редактирование данных

Создаваемые в Excel файлы называются рабочими книгами и имеют расширение «xls» или «xlsx». В свою очередь рабочая книга состоит из нескольких рабочих листов. Каждый рабочий лист – это отдельная электронная таблица, которые в случае необходимости могут быть взаимосвязаны. Активной рабочей книгой является та, с которой в данный момент вы работаете, например, в которую вводите данные.

После запуска приложения автоматически создается новая книга с именем «Книга1». По умолчанию рабочая книга состоит из трех рабочих листов с именами от «Лист1» до «Лист3».

Excel_2010

Рабочее поле листа Excel поделено на множество прямоугольных ячеек. Объединённые по горизонтали ячейки составляют строки, а по вертикали – столбцы. Для возможности исследования большого объема данных каждый рабочий лист программы имеет 1 048 576 строк пронумерованных числами и 16 384 столбцов обозначенных буквами латинского алфавита.

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

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

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

excel_2010

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

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

После окончания ввода данных нажмите:

  • Клавишу «Enter» — следующей активной ячейкой станет клетка снизу.
  • Клавишу «Tab» — следующей активной ячейкой станет клетка справа.
  • Щелкните мышью по любой другой ячейке, и она станет активной.

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

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

Форматирование данных ячеек

После ввода названий строк и столбцов получаем таблицу такого вида:

excel_2010

excel_2010

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

Читайте также:  Определение эффективности антибактериальной терапии пут м проведения терапевтического лекарственного мониторинга

table_excel

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

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

table_excel

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

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

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

vydelenie_excelvydelenie_excel

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

vydelenie_excelvydelenie_excel

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

vyravnivanie_excel

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

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

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

formatirovanie_excel

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

zvet_fona_excel

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

format_texta_excel

Так же с помощью уже знакомой команды Цвет заливки мы придали фону четных и нечетных строк с числами различный синий оттенок.

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

format_granits_excel

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

format_yacheek_excel

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

table_excel

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

Формат данных ячейки

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

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

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

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

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

format_yacheek

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

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

Теперь наша учебная таблица, наконец, приняла завершенный вид:

table_excel

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

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

Заключение

Наверное, многие из вас зададутся вопросом: «А зачем создавать подобного рода таблицы в Excel, когда все то же самое можно сделать в Word, используя готовые шаблоны?». Так-то оно так, только вот производить всевозможные математические операции над ячейками в текстовом редакторе невозможно. Саму же информацию в ячейки вы практически всегда заносите самостоятельно, а таблица является лишь наглядным представлением данных. Да и объемные таблицы в Word делать не совсем удобно.

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

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

Источник



Модель ячейки в MS Excel

Структура электронной таблицы

1. Общие сведения о табличном процессоре MS Excel.

2. Структура электронной таблицы.

3. Копирование формул.

4. Относительная и абсолютная адресация.

5. Построение диаграмм.

MS Excel относится к классу систем обработки числовой информации, называемых spreadsheet. Буквальный перевод термина «spreadsheet» c английского языка означает «расстеленный лист (бумаги)». В компьютерном мире под этим термином подразумевают класс программных средств, именуемых « электронными таблицами».

Области применения электронных таблиц:

– бухгалтерский и банковский учет;

– планирование распределение ресурсов;

– обработка больших массивов информации;

– исследование динамических процессов.

Основные возможности электронных таблиц:

– анализ и моделирование на основе выполнения вычислений и обработки данных;

– оформление таблиц, отчетов;

– форматирование содержащихся в таблице данных;

– построение диаграмм требуемого вида;

– создание и ведение баз данных с возможностью выбора записей по заданному критерию и сортировки по любому параметру;

– перенесение (вставка) в таблицу информации из документов, созданных в других приложениях, работающих в среде Windows;

– печать итогового документа целиком или частично.

Преимущества использования ЭТ при решении задач:

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

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

В таблице используются столбцы и строки.

Читайте также:  Нормативы испытаний тестов ВФСК Готов к труду и обороне

Строки пронумерованы от 1 до 65536, столбцы помечаются латинскими буквами от А до Z, и комбинациями букв АА, АВ. IV.

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

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

Каждая ячейка таблицы имеет следующие характеристики:

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

MS Excel позволяет использовать два стиля ссылок: стиль А1 и стиль R1C1.

Например. Пусть в ячейке D3 нужно получить произведение чисел, находящихся в ячейках А2 (второй ряд, первая колонка) и B1 (первый ряд, вторая колонка). Это может быть записано одним из следующих способов:

Вид ссылок Стиль А1 Стиль R1С1
относительный =A2 * B1 имя столбца, имя строки, которые будут относительно изменяться, при копировании формулы в другую ячейку =R[-1]C[-3] * R[-2]C[-2] смещение по строке, смещение по столбцу, относительно ссылающейся ячейки. Сама формула при копировании не изменяет вид, но ссылается уже на другие ячейки
абсолютный =$A$2 * $B$1 имя столбца, имя строки, которые останутся неизменным, при копировании формулы =R2C1 * R1C2 номер строки и номер столбца, относительно начала координат
смешанный =$A2*B$1=A$2 * $B1 =R[-1]C1*R1C[-2]= R2C[-3] * R[-2]C2

Содержимым ячейки может быть:

– число (целое или с плавающей точкой);

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

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

Изображение – то, что пользователь видит на экране монитора.

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

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

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

Формат ячейки – формат чисел, шрифт, цвет символов, вид рамки, цвет фона, выравнивание по границам ячейки, защита ячейки.

Имя – используется в формулах, как замена абсолютного адреса ячейки. Например, назначив ячейке С3 имя “Произведение” в ячейку D3 можно поместить формулу: =Произведение/3 (вместо формулы =С3/3). В этом случае, при копировании формулы, адрес ячейки меняться не будет.

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

Источник

Как работать в Excel с таблицами для чайников: пошаговая инструкция

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

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

Как создать таблицу в Excel для чайников

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

Посмотрите внимательно на рабочий лист табличного процессора:

Электронная таблица.

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Выделить столбец.

Для выделения строки – по названию строки (по цифре).

Выделить строку.

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

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

  1. Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши. Ширина столбца.
  2. Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы. Автозаполнение.
  3. Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Перенос по словам.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Ширина столбцов.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Автоподбор высоты строки.

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.

Место для вставки столбца.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Добавить ячейки.

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

  1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк. Данные для будущей таблицы.
  2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД. Формула.
  3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки. Автозаполнение ячеек.Результат автозаполнения.
  4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Все границы.

Теперь при печати границы столбцов и строк будут видны.

Границы таблицы.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

Меню шрифт.

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

Как создать таблицу в Excel: пошаговая инструкция

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

Сделаем «умную» (динамическую) таблицу:

  1. Переходим на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T). Вставка таблицы.
  2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая. Таблица с заголовками.

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

Умная таблица.

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

Плюс склад.

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

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» — «Конструктор».

Конструктор таблиц.

Здесь мы можем дать имя таблице, изменить размер.

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

Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter. Новая запись.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически. Заполнение ячеек таблицы.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).

Автосумма.Результат автосуммы.

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

Числовые фильтры.

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

Источник