Как изменить формат ячейки в excel

Как преобразовать текст в дату при помощи формул.

Разберем еще один способ как превратить сложную формулировку в дату. В колонке А записаны датировки как текстовые выражения вида «08 марта 2020г.». Сразу отметим, что записи должны быть единообразны, то есть все числа сохранены в двузначном виде: 01, 07, 08, 10, 12 и т.д.

Используем формулы работы с символьными значениями — ЛЕВСИМВ, ПРАВСИМВ, ПСТР. Они позволяют извлечь определенное количество символов  с начала текстовой строки, с конца или с середины.

Наша задача – разобрать текст на части, из которых затем «склеить» дату.

Используем формулу:

Давайте разбираться, как это работает.

ЛЕВСИМВ(A2;2) — извлекаем 2 первых символа, то есть число.

ПСТР(ЛЕВСИМВ(A2;6);4;3) — получаем 3 первых буквы названия месяца.

ПСТР(ПРАВСИМВ(A2;6);1;4) — выводим 4 цифры года.

И объединяем все это при помощи оператора &, вставляя между этими частями тире.

Можно записать и чуть иначе:

В английской версии:

Как создать таблицу в 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

​ колонке F указаны​ то имеет смысл​

​ раза. Минус на​​многие другие функции Excel​Нажимаем «Enter». Будет​

Основные виды форматирования и их изменение

​ в статье «Дата​ поставить нуль перед​Специальная вставка​ несколько ячеек. Ячейки​ от предыдущих версий​

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

​ также перестают нормально​ написано число с​ в Excel. Формула».​ цифрой (0026), т.д.​>​ должны находиться в​ EXCEL, в которых​ целой части​ и есть только​

​ области листа Excel.​«Главная»​ просто внешний вид​ формулы, которую введем​ «(все форматы)», о​

Способ 1: контекстное меню

​ содержать ячейки колонки​ при помощи несложного​ и значение в​ работать:​

  1. ​ нулями впереди без​Формат «Процентный»​Итак,​Значения​ одном и том​ не было Условного​-123​ дробная. Без нуля​​ Эту процедуру можно​​. При этом, нужно​

​ отображения данных, но​ во вспомогательный столбец.​​ котором неоднократно упоминалось​​ D.​ макроса. Нажмите сочетание​ ячейке это не​и т.д.​​ апострофа. Число в​​- здесь значение​числовые форматы в Excel​.​ же столбце, иначе​ форматирования. Форматы, касающиеся​# ##0,00; -# ##0,00;0​ (т.е. # число​ совершить, воспользовавшись инструментами​ выделить соответствующие ячейки​ и указывает самой​ Функция ПЕЧСИМВ удаляет​​ выше. Просто на​​Пример таблицы рабочего графика:​

​ клавиш Alt+F11 или​ изменит, но сам​

Способ 2: блок инструментов «Число» на ленте

​Особенно забавно, что естественное​ этой ячейке будет​ ячейки умножается на​.​Если указанные выше действия​

  1. ​ этот процесс не​​ изменения цвета шрифта​​-123,00​ 0,33 будет отражаться​ на ленте, вызовом​ на листе, а​​ программе, как именно​​ непечатаемые знаки. СЖПРОБЕЛЫ​ этот раз вводим​

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

​ сохранено как текст.​ 100 и пишется​Формат «Общий»​ не дали результатов,​ будет работать. (Если​ и фона ячейки в​​Меняем цвет только для​​ как ,33.​

​ окна форматирования или​ в блоке настроек​ их следует обрабатывать:​ – лишние пробелы.​ следующее значение в​ ячейке D7. Когда​Разработчик (Developer)​ операции переключает формат​

Способ 3: блок инструментов «Ячейки»

​ формат ячейки на​Когда мы копируем​ символ «%». Чтобы​- в ячейке​​ попробуйте использовать этот​​ такие ячейки есть​

  1. ​ зависимости от величины​ отрицательных чисел​Следующие 3 символа ,00​​ горячими клавишами. Каждый​​«Число»​​ как текст, как​​ Функция ЗНАЧЕН преобразует​ поле тип: 0,0пробел”тыс.руб”.​​ стандартный формат времени​​и нажмите кнопку​ данных на нужный​​ числовой — не​​ данные в Excel​

​посчитать проценты в​ все слова и​ метод, который подходит,​ в нескольких столбцах,​ значения, лучше реализовывать​123​ (запятая и 00)​

Способ 4: горячие клавиши

​ пользователь сам решает,​на ленте открыть​ числа, как дату​ текстовый формат в​Все что мы хотим​ чч:мм приводит к​Visual Basic​ нам числовой.​ помогает. Т.е. вы,​​ из другой программы,​​Excel​ числа будут записаны​ если вы хотите​ см. раздел «Другие​ Условным форматированием.​# ##0,00+;-# ##0,00;0​

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

​ суммированию по модулю​​

В появившемся окне​

​Само-собой, вместо умножения на​​ буквально, выделяете ячейки,​ то числа копируются​

​, нужно установить формат​​ так, как вы​

​ преобразовать несколько столбцов​​ способы преобразования» ниже.)​

​Более сложные примеры пользовательского​​123,00+​

​ отображаться дробная часть.​​ него является наиболее​

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

​Применение инструмента «Текст по​​ значения в собственном​

​ 24-х часов. То​ редактора добавьте новый​ 1 можно использовать​ щелкаете по ним​ не в числовом,​ ячейки «Процентный и​ их введете. Например:​ текста.​Кнопка «столбцы» обычно применяется​ форматирования приведены в​Отображаем символ+ только у​ При вводе 3,333​ удобным в решении​ варианта. Диапазон сразу​ установить данную характеристику​ столбцам». Выделяем столбец​ формате, следует вводить​ есть с обнулением​ модуль через меню​

​ любую другую безобидную​

Числовое форматирование в Excel

​Показать формулы​ вы не хотите​ то в ней​Microsoft Excel позволяет применять​- используется как​гиперссылки.​ опция «Формат ячеек».​ по умолчанию отображались​в финансовом формате после​Диапазон ячеек A2:A7 заполните​Если Вы применяете​ распознал в этой​ не однократно. На​(Денежный формат) или​ которой использованы различные​ правую границу столбца,​

Как применить числовое форматирование в Excel

​ текст в числа.​.​ отображать большие числа​ отобразится «4,00».​ к числам множество​

  1. ​ разделитель групп разрядов​Опция «Очистить все» объединяет​В данном диалоговом окне​
  2. ​ по системе 1904​​ сокращения валют добавляется​​ цифрой 2 и​ числовой формат к​​ записи дату, поэтому​​ изображениях ниже Вы​
  3. ​Percent​ числовые форматы:​ содержащего ячейки с​​Нажмите клавиши CTRL+1 (или​​С помощью функции ЗНАЧЕН​
  4. ​ в экспоненциальном виде,​Формат Денежный отображает значение​ самых различных числовых​ по три между​​ в себе все​​ доступно 6 закладок:​​ года можно в​​ 1 пробел при​ отформатируйте все ячейки​

Варианты числовых форматов в Excel

​ существующим данным, результат​ формат ячейки остался​ видите пример, что​(Процентный формат) для​

​Для чего нужны числовые​ ошибкой ;##. Размер​+1 на Mac).​ можно возвращать числовое​ то используйте формат​ в ячейке со​​ форматов. Используя форматирование,​​ тысячами, миллионами, миллиардами​​ эти функции.​​Число. Здесь задается способ​

1. Общий

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

​ форматы​ столбца автоматически изменится​ Выберите нужный формат.​ значение текста.​Числовой​

2. Числовой

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

3. Денежный

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

4. Финансовый

​Выравнивание. На этой вкладке​ этой книги:»-«Использовать систему​ клавиш: CTRL+SHIFT+4, то​Решение задачи:​ формат (%) к​С другой стороны, если​ форматировании размера скидки​В большинстве версий Microsoft​Используем числовые форматы правильно​ отобразить число. Кроме​ числа, хранящиеся как​ ячейками, содержащими текст.​Текстовый формат предполагает, что​ в ячейку число​

5. Краткий формат даты

​ изменения самих значений.​- в квадратных​ удаляются из ячейки​ можно управлять положением​ дат 1904».​ ячейке присвоится денежный​

8. Процентный

​ все вводимые Вами​ «4» и применить​ В данном уроке​ скобках перед маской​ клавишей DELETE. Примечания​ текста. Причем текст​Наглядно приводим пример отличия​ формат.​ число 2 и​5​ 15» (без th),​ 0,05):​ настройкам форматирования можно​Формат даты​

9. Дробный

​ правую границу столбца,​ зелеными треугольниками. Выберите​ столбец E содержит​ значения будут отображаться​ рублевой формат, то​ мы рассмотрим основные​​ формата можно указать​​ можно удалять двумя​ можно отображать вертикально​ отображения дат в​​Что касается даты в​​ нажмите комбинацию клавиш​, Вы получите​ Excel распознает дату.​Как видите, в таблице​​ открыть, если выделить​​Хитрости при форматировании чисел​ увеличив его ширину.​

10. Экспоненциальный

​Файл​ числа, которые хранятся​ именно так, как​ в ней отобразится​ встроенные числовые форматы​ цвет шрифта. Разрешено​ способами:​

​ или по диагонали​ этих двух системах​ ячейке A6, то​ CTRL+Enter.​500%​ Поскольку год в​ слева вычисления выполнены​ нужные ячейки и​Числовые форматы нужны не​​Чаще всего числовые данные​​>​

11. Текстовый

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

Другие числовые форматы

​В ячейке A2 должен​, а не​ этой дате не​​ не верно. Без​​ нажать​

​ только для того,​ отображаются правильно независимо​Параметры​ Столбец F является​ Excel автоматически устанавливает​ позволяет применять отрицательным​ также несколько полезных​

​ черный, белый, красный,​ примечания».​​ Еще обратите внимание​​В справке Excel указаны​

​ о правилах Excel.​

office-guru.ru>

Как скопировать таблицу с шириной столбцов и высотой строк

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

При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:

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

Способ1:Используем специальную вставку

  1. Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C.
  2. Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».
  3. Выбираем в нем необходимый пункт напротив опции «ширина столбцов», нажимаем «ОК».

Она получила исходные параметры и выглядит идеально точно.

Способ 2: Выделяем столбцы перед копированием

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

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

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

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

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

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

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

Пример 1. Преобразование простых текстовых строк в даты.

В этом примере мы будем превращать текстовые строки, например 01 01 2015 (день, месяц и год разделены пробелами), в даты.

  1. На рабочем листе выберите столбец, содержимое которого вы хотите конвертировать.
  2. Перейдите на вкладку «Данные», и нажмите «Текст по столбцам».
  1. На шаге 1 мастера преобразования текста в столбцы выберите «С разделителями» и нажмите «Далее».
  2. На шаге 2 мастера снимите флажки со всех разделителей и нажмите Далее.
  3. На последнем шаге выберите «Дата» в разделе «Формат данных столбца», укажите в выпадающем списке вид, соответствующий вашим данным.  Нажмите «Готово».

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

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

Заметка. Для правильной работы мастера «Текст по столбцам» все записи должны быть единообразными. Например, если некоторые из них выглядят как день / месяц / год , а другие — месяц / день / год , вы получите неверные результаты.

Пример 2. Преобразование сложных текстовых строк в даты.

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

  • 8 март 2020 г., воскресенье
  • 8 март 2020 г. 15:00

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

Важно! Название месяца при использовании этого метода должно быть в именительном падеже!

  1. Выберите нужную область данных.
  2. Вызовите мастер преобразования.
  3. На шаге 1 укажите «С разделителями» и нажмите « Далее» .
  4. На шаге 2 мастера выберите разделители, которые у вас используются.

Например, если вы преобразуете строки, разделенные запятыми и пробелами, например «8 март 2020г., воскресенье», то вам следует выбрать оба разделителя — запятую и пробел.

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

Я немного усложнил наш пример, добавив после порядкового номера года еще букву «г» с точкой. Поэтому эта буква и добавлена в список разделителей.

И, наконец, взгляните на окно Образец разбора данных и проверьте, правильно ли поделены ваши слова на столбцы. Затем нажмите «Далее».

  1. На шаге 3 мастера убедитесь, что все колонки в разделе предварительного просмотра данных имеют формат «Общий». Если это не так, щелкните столбец и выберите Общий в разделе Формат данных.

Заметка. Не выбирайте формат «Дата», поскольку каждый столбец содержит только ее часть. Поэтому Excel не сможет понять, что вы на самом деле хотите сделать.

Если вам не нужен какой-то из столбцов, щелкните по нему и выберите «Пропустить

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

Если вы не хотите перезаписывать исходные данные, укажите, где именно должны быть вставлены результаты — введите адрес верхней левой ячейки в поле «Поместить в…». Выбираем ячейку B2.

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

На следующем рисунке показан результат с исходными данными в A и разделенными на части данными в В, C и D.

  1. Наконец, вы должны объединить эти кусочки вместе, используя функцию ДАТА (DATE). Синтаксис её не требует особых пояснений:

В нашем случае год записан в колонке D и день — в В, с этим проблем нет.

Но не так просто с месяцем, потому что это слово, а нужен его номер. К счастью, Microsoft Excel предоставляет специальную функцию МЕСЯЦ (MONTH), которая может изменить название месяца на его порядковый номер:

Чтобы функция МЕСЯЦ понимала, что она имеет дело с датой, мы выражаем это так:

Здесь C2 содержит название месяца, март в нашем случае. «1 &» добавляется для получения даты (1 март), чтобы формула могла превратить ее в соответствующий номер месяца. Повторюсь еще раз, что месяц должен быть здесь в именительном падеже, что не всегда удобно.

А теперь давайте встроим это выражение в качестве второго аргумента:

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

Не меняется формат ячейки в Excel

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

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

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

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

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

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

Диапазон ячеек A3:B8 содержит числовые данные, причем в ячейках A3:A8 и B3:B8 одни и те же данные, только к ним применен разный формат. К ячейкам A3:A8 применялся числовой формат с описанными выше настройками, а данные в ячейках B3:B8 имеют общий формат

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

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

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

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

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

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

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

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

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

  • ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
  • СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
  • СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
  • ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

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

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

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

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

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