Форматирование объектов электронной таблицы
При форматировании электронной таблицы используются основные операции: форматирование ячеек и данных, изменение ширины строк и высоты столбцов.
Форматирование ячеек включает в себя установку и изменение:
- границ, цвета, типа линий ячеек и т.д.;
- формата числовых данных;
- цвета фона, типа заливки и т.д.;
- шрифта, размера, ориентации текста и др.;
- защиту ячеек и режим скрытия формул.
Для выполнения этих операций используются элементы вкладки «Главная» и вкладки «Формат ячеек». Для вызова окна «Формат ячеек» можно воспользоваться контекстным меню. Для этого достаточно нажать правой кнопкой мыши на ячейку электронной таблицы. Окно состоит из шести вкладок.
Вкладка «Число». Отвечает за назначение форматов ячеек. Стоит заметить, что формат можно задавать как до ввода данных, так и после. По умолчанию в табличном редакторе для любой ячейки установлен «Общий» формат.
Вкладка «Выравнивание». Используется для операций объединения ячеек, выравнивания и направления текста и переноса слов. В этой вкладке также можно объединить ячейки и задать отступ, чтобы текст не прилипал к границам ячейки.
Вкладка «Шрифт». С помощью окна данной вкладки можно форматировать шрифт в ячейках. Пользователю доступны операции смены шрифта, размера, начертания и цвета.
Вкладка «Граница». Инструменты этой вкладки позволяют придать таблице завершенный вид с помощью типа, цвета и толщины границы ячеек.
Вкладка «Заливка». В окне вкладки доступны возможности добавления и изменения цвета и узора фона.
Вкладка «Защита». Позволяет защищать ячейки от возможности редактирования.
Самые часто используемые инструменты для форматирования ячеек доступны в верхней панели табличного процессора.
Microsoft Excel предоставляет пользователям возможность стилевого форматирования. С его помощью можно оформлять объекты таблицы. Стандартный набор стилей находится во вкладке «Главная», группа «Стили». Готовые решения можно изменять, а также создавать свои собственные стили.
Формат ячейки можно удалять и копировать для другой ячейки. Для удаления достаточно перейти в группу «Редактирование» (вкладка «Главная») и выполнить команду «Очистить форматы». Для применения формата к другим ячейкам используется инструмент «Формат по образцу», который находится на вкладке «Главная» в группе «Буфер обмена».
Дополнительные вычисления в сводных таблицах. Настройка вычислений в сводных таблицах
- Выделить необходимое количество ячеек. Для удобства можно выбрать первую строку или столбец, зажать кнопку Shift, выбрать последнюю ячейку. Таким образом, будет выделен весь необходимый диапазон.
- Установить курсор на границе любой выделенной ячейки. Для автоподбора высоты курсор следует установить на панели слева, а для автоподбора ширины — сверху. Указатель должен принять вид стрелки, направленной в две стороны.
- Дважды нажать левой кнопкой мыши. После этого табличный процессор изменит размер ячейки по содержимому.
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Размах вариации
Размах вариации – разница между максимальным и минимальным значением:
Ниже приведена графическая интерпретация размаха вариации.
Видно максимальное и минимальное значение, а также расстояние между ними, которое и соответствует размаху вариации.
Статистические функции в Excel. Статистические расчеты в excel
- Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза — наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
| 23 | 25 | 24 | 25 | 30 | 24 | 30 | 26 | 28 | 26 |
| 32 | 33 | 31 | 31 | 25 | 33 | 25 | 29 | 30 | 28 |
| 23 | 30 | 29 | 24 | 33 | 30 | 30 | 28 | 26 | 25 |
| 26 | 29 | 27 | 29 | 26 | 28 | 27 | 26 | 29 | 28 |
| 29 | 30 | 27 | 30 | 28 | 32 | 28 | 26 | 30 | 26 |
| 31 | 27 | 30 | 27 | 33 | 28 | 26 | 30 | 31 | 29 |
| 27 | 30 | 30 | 29 | 27 | 26 | 28 | 31 | 29 | 28 |
| 33 | 27 | 30 | 33 | 26 | 31 | 34 | 28 | 32 | 22 |
| 29 | 30 | 27 | 29 | 34 | 29 | 32 | 29 | 29 | 30 |
| 29 | 29 | 36 | 29 | 29 | 34 | 23 | 28 | 24 | 28 |
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
Создаем собственный числовой формат в MS Excel — Вектор развития. Офисные системы для бизнеса
- Выбор нового рабочего листа поместит его на новый лист, начиная с ячейки A1.
- Выбор существующего листа разместит в указанном вами месте на существующем листе. В поле «Диапазон» выберите первую ячейку (то есть, верхнюю левую), в которую вы хотите поместить свою таблицу.
Под описательной статистикой понимают систематизацию эмпирических данных по целому ряду основных статистических критериев. Причем на основе полученного результата из этих итоговых показателей можно сформировать общие выводы об изучаемом массиве данных.
Тип данных: Формулы MS Excel
Если содержимое ячейки начинается со знака “=” (впрочем, также можно начинать со знака “+” или “-“), то значение этой ячейки MS Excel воспринимается как формула. Формула представляет собой не итоговое значение введенное в ячейку, а результат выполнения каких-то действий над значениями других ячеек.
Это важно понимать – в ячейке с формулой содержаться не сами данные, а только результат вычислений. Иными словами, если мы складываем значения ячеек А (5) и Б (7), то в ячейке В с формулой, будет содержатся не итоговое число 13 (хотя именно результат 13 и будет выводится), а сумма значений А и Б (5+7).
Работают формулы просто: сперва вы инициируете формулу (“=”), затем указываете ячейку из которой берутся данные для вычислений (щелкаете в неё левой кнопкой мыши), действие которое нужно выполнить (вписываете знак сложения, умножения, вычитания, степени и т.п.), затем ещё одну ячейку с данными и нажимаете Enter.
Простейший пример формулы в Excel. Формулы – один из трех основных типов данных в Excel
В итоге, получится простейшая формула, мгновенно производящая нужное вычисление. Порядок выполнения действий в формуле соответствует
принятым в математике правилам: сначала умножение и деление, затем сложение и вычитание. Действия в скобках выполняются вперед всех других.
Впрочем, даже указывать ячейки с источниками данных не обязательно, если инициировать формулу (“=”), а затем ввести в ту же ячейку данные “вручную”, например 2+2, то после нажатия Enter в ячейке окажется результат вычисления, т.е. число 4.
Именно за формулы мы и любим редактор Excel. Нам нужно только ввести исходные данные и указать что с чем нужно вычитать и складывать, а уж программа сделает все автоматически.
Магия Excel: 10 самых полезных «фишек» для работы с таблицами — БизнесБизнес
По умолчанию Microsoft Excel использует функцию «Сумма» для числовых показателей, которые вы помещаете в область «Значения». Когда вы помещаете нечисловые (текст, дата или логическое значение) или пустые значения в эту область, к ним применяется функция «Количество».
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов. Поэтому важно уметь работать с любой структурой.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Шаг 2: расчет среднего арифметического
- Текст вместо чисел
- Отрицательные числа там, где их быть не может
- Числа с дробной частью там, где должны быть целые
- Текст вместо даты
- Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.
В Экселе не существует отдельно функции для вычисления этого показателя, но имеются формулы для расчета стандартного отклонения и среднего арифметического ряда чисел, а именно они используются для нахождения коэффициента вариации.
Как создать новый числовой формат в Excel
В примере приведенном далее, я буду использовать за образец такие требования: мне необходимо создать новый формат для ввода телефонных номеров. Причем в ячейку я буду вводить простой ряд цифр, а к соответствующему формату (“+7-9ХХ-ХХХ-ХХ-ХХ“) этот ряд цифр должен будет привести уже сам MS Excel.
Первым делом не мешало бы выделить столбец нашего будущего шаблона, а затем на вкладке главная вызвать выпадающий список “Числовой формат” (в группе “число”) и выбрать самый нижний пункт списка – “Другие числовые форматы“.
Выбираем шаблон похожий на то что нам нужно и модифицируем его
Нужная нам вкладка “Число” доступна здесь по умолчанию. Во всем многообразии предложенных числовых форматов я разбираться не стану и сразу же выберу пункт “(все форматы)”, чтобы увидеть всё и сразу.
Обратите внимание: в самом низу в этом списке уже есть шаблон телефонного номера который выглядит как “(###) ###-####”. Очевидно, что значки “решетки” в этом шаблоне заменяют цифры, и введи я реальный номер, например, в виде числа 9141108501, он отобразился бы как “(914) 110-8501”.
Как видите – неплохо, однако на мой задуманный шаблон походит только отчасти. Поэтому я просто переписываю шаблон на “+7 (###) ###-##-##” и нажимаю кнопку “Ок” (обратите внимание – если в экспериментальной ячейке введено число, при изменении шаблона вы сможете видеть как оно будет выглядеть после изменений).
Ввожу обычный числовой ряд, а MS Excel делает всю остальную работу по введенному ранее шаблону
Все отлично работает, особенно хорошо это заметно по строке формул – в ней мой телефонный номер выглядит как обычный числовой ряд, а в ячейке отображается как “нормальный” телефон.
Также обратите внимание: если второй раз открыть окно “Другие числовые форматы”, то вы обнаружите в нем не только “новый” шаблон для ввода телефонных номеров, но и “старый”. Сохранение это происходит автоматически и служит надежной страховкой от того, чтобы всерьез что-то в программе “сломать”.
Удаление Сводной таблицы
Знаете сколько числовых форматов предлагает MS Excel? 9 штук! А ведь почти каждый из них имеет ещё и свои внутренние настройки – количество знаков после запятой, спецобозначения валют и т.п. Казалось бы во всем этом многообразии могут найтись варианты на все случаи жизни… Однако это далеко не так.
Быстрый анализ
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
Как работать
- Откройте таблицу с данными для анализа.
- Выделите нужный для анализа диапазон.
- При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Создаем и размещаем макет
- Откройте файл с таблицей, данные которой надо проанализировать.
- Выделите диапазон данных для анализа.
- Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
- Должно появиться диалоговое окно «Создание сводной таблицы».
- Настройте отображение данных, которые есть у вас в таблице.
В примере приведенном далее, я буду использовать за образец такие требования: мне необходимо создать новый формат для ввода телефонных номеров. Причем в ячейку я буду вводить простой ряд цифр, а к соответствующему формату (“+7-9ХХ-ХХХ-ХХ-ХХ“) этот ряд цифр должен будет привести уже сам MS Excel.
