Excel формула в таблице: Обзор формул

Содержание

Как вставить и сделать формулу в Excel-таблице

Для полного знакомства с электронными таблицами требуется понять, как сделать формулу в Excel. Э…


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


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

Какие функции доступны
для пользователя Эксель:

  • арифметические
    вычисления;
  • применение готовых
    функций;
  • сравнения числовых
    значений в нескольких клетках: меньше,
    больше, меньше (или равно), больше (или
    равно). Результатом будет какое-то из
    двух значений: «Истина», «Ложь»;
  • объединение текстовой
    информации из двух и более клеток в
    единое целое.



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

Основы
работы с формулами в
Excel

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

  1. Каждая из них должна
    начинаться со значка равенства («=»).
  2. В вычислениях
    допускается использование значений
    из ячеек, а также функции.
  3. Для применения
    стандартных математических знаков
    операций следует вводить операторы.
  4. Если происходит
    вставка записи, то в ячейке (по умолчанию)
    появляются данные итоговых вычислений.
  5. Увидеть конструкцию
    пользователь может в строчке над
    таблицей.

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

«Эксель» допускается
использовать, как настольный калькулятор,
при этом сложные формулы могут состоять
из элементов:

  • постоянных
    чисел-констант;
  • операторов;
  • ссылок на иные
    клетки-ячейки;
  • математических
    функций;
  • имён диапазонов;
  • встроенных формул.

При необходимости
пользователь может производить любые
вычисления.

Как фиксировать
ячейку

Для фиксации значения
в клетке применяют стандартный символ
«$». Ставить знак допускается с помощью
«быстрой» клавиши F4. Используют
три варианта фиксации:

  • полная;
  • по вертикали;
  • по горизонтали.

Чтобы предотвратить
сдвиг ячейки по горизонтали и по вертикали
используют обозначения вида: $A$1. Это
может понадобиться, если в расчётах
используется постоянное значение,
например, расход топлива или курс валют.

Если требуется закрепление
по вертикали, то обозначение будет
таким: $A1.

Для фиксации по
горизонтали понадобится указать адрес
в виде: A$1.

Делаем таблицу в Excel
с математическими формулами

Следует разобраться,
с какого символа начинается формула в
Excel. Здесь всё просто. Чтобы
применить одну из математических формул,
требуется:

  • поставить значок
    «=» в клетку — здесь будут отображаться
    результаты вычислений;
  • далее
    выделить клетки с исходными
    значениями и указание нужного оператора
    – это знаки: «+», «-», «*», «/»;
  • повторяем то же с
    другими «клеточками», которые будут
    участвовать в вычислениях;
  • нажать клавишу
    «равно».

Это самый доступный
метод создания формулы.

Пошаговый
пример №1

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

Шаг 1

Перед названиями товаров
требуется вставить дополнительный
столбец.

Шаг 2

Выделить клетку в 1-ой
графе и щелкнуть правой кнопкой мышки.

Шаг 3

Нажать «Вставить». Или
использовать комбинацию «CTRL+ПРОБЕЛ» –
чтобы выделить полный столбик листа.
Затем кликнуть: «CTRL+SHIFT+»=»» – для
вставки столбца.

Шаг 4

Новую графу рекомендуется
назвать, например, – «No п/п».

Шаг 5

Ввод в 1-ю клеточку «1»,
а во 2-ю – «2».

Шаг 6

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

Шаг 7

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

Шаг 8

Ввод в первую клетку
информации: «окт.18», во вторую – «ноя.18».

Шаг 9

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

Шаг 10

Для поиска средней
стоимости товаров, следует выделить
столбик с указанными ранее ценами + еще
одну клеточку. Затем открыть меню кнопки
«Сумма», набрать формулу, которая будет
использоваться для расчёта усреднённого
значения.

Шаг 11

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

Пошаговый
пример №2

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

Шаг 1

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

Шаг 2

Чтобы сгенерировать
результаты вычислений в процентах (в
Эксель), не требуется умножать полученное
ранее частное на 100. Следует выделить
клетку с нашим результатом и нажать
«Процентный формат». Также допускается
использование комбинации горячих
клавиш: «CTRL+SHIFT+5».

Шаг 3

Теперь необходимо
скопировать формулу на столбец: будет
изменяться первое значение в нашей
формуле (относительная ссылка). А второе
(абсолютная ссылка) останется прежним.
Проверка правильности вычислений –
поиск итога. Если равен 100%, то вычисления
выполнены верно.

Заключение

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

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

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

excel — Как при добавлении новых строк в таблицу автоматически включать их значения в формулу?


Вопрос задан


Изменён
3 месяца назад


Просмотрен
734 раза

Есть таблица в excel. Есть формулы,которые считают кол-во часов. Как мне сделать так,чтобы при добавлении строки , формула подсчета включала автоматически данные из добавленной строки?
Сумма считается простой формулой в ячейке . =СУММ(C13:C14) и т.п.

Есть скрин таблицы

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

Вот ссылка на таблицу https://www. dropbox.com/scl/fi/rswrd6ff4z5gea7o2wlda/.xls?dl=0&rlkey=ndgt9ah623k7j2hhj3bhvp6m4

  • excel
  • vba






18

Определяем положение последнего числа в столбце:

=СУММ(C3:ИНДЕКС(C:C;ПРОСМОТР(9E+307;C3:C1000;СТРОКА(C3:C1000))))

Сложно…

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

Можно задать статичный диапазон, на который не влияет добавление/удаление строк:

=СУММ(C3:ИНДЕКС(C:C;1000)

Но такой вариант не избавляет от лишних вычислений в пустых строках.

Функция СУММЕСЛИ умеет определять используемый диапазон, поэтому просматривать будет ровно столько строк, сколько нужно:

=СУММЕСЛИ(C:C;">0")

Или по значению другого столбца:

 =СУММЕСЛИ(B:B;"модуль1";C:C)

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

Если условия суммирования добавляются (например, значение других столбцов, диапазон дат…), можно использовать более продвнутую сестричку — СУММЕСЛИМН






2







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации


Почта

Необходима, но никому не показывается





Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки


Сумма данных в таблице Excel

Столы

Справка по Excel и обучение

Столы

Столы

Суммируйте данные в таблице Excel

  • Создание и форматирование таблиц

    Статья

  • Сортировать данные в таблице

    Статья

  • Фильтрация данных в диапазоне или таблице

    Статья

  • Суммируйте данные в таблице Excel

    Статья

  • Используйте слайсеры для фильтрации данных

    Статья

Следующий:

Графики

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Дополнительно. ..Меньше

Вы можете быстро суммировать данные в таблице Excel, включив Total Row , а затем используйте одну из нескольких функций, представленных в раскрывающемся списке для каждого столбца таблицы. В параметрах строки итогов по умолчанию используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или игнорировать скрытые строки таблицы, однако вы также можете использовать другие функции.

  1. Щелкните в любом месте таблицы.

  2. Перейти к Табличные инструменты > Design и установите флажок для Total Row .

  3. org/ListItem»>

    Итоговая строка вставляется внизу таблицы.

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

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

    .

    Вы увидите, что Excel создал следующую формулу: =ПРОМЕЖУТОЧНЫЙ ИТОГ(109,[Средний Запад]) . Это функция ПРОМЕЖУТОЧНЫЙ ИТОГ для СУММ, а также формула структурированной ссылки, которая используется исключительно для таблиц Excel. Узнайте больше об использовании структурированных ссылок с таблицами Excel.

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

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

Вы можете быстро суммировать данные в таблице Excel, включив параметр Total Row , а затем использовать одну из нескольких функций, представленных в раскрывающемся списке для каждого столбца таблицы. В параметрах строки итогов по умолчанию используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая позволяет включать или игнорировать скрытые строки таблицы, однако вы также можете использовать другие функции.

  1. Щелкните в любом месте таблицы.

  2. Перейти к таблице > Итоговая строка .

  3. Итоговая строка вставляется внизу таблицы.

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

  4. org/ListItem»>

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

    .

    Вы увидите, что Excel создал следующую формулу: =ПРОМЕЖУТОЧНЫЙ ИТОГ(109,[Средний Запад]) . Это функция ПРОМЕЖУТОЧНЫЙ ИТОГ для СУММ, а также формула структурированной ссылки, которая используется исключительно для таблиц Excel. Узнайте больше об использовании структурированных ссылок с таблицами Excel.

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

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

Вы можете быстро суммировать данные в таблице Excel, включив параметр Toggle Total Row .

  1. Щелкните в любом месте таблицы.

  2. Щелкните вкладку Table Design > Параметры стиля > Итоговая строка .

    Всего строк вставлены внизу таблицы.

Установка функции агрегирования для ячейки строки итогов

Примечание. Это одна из нескольких бета-функций, которая в настоящее время доступна только для части участников программы предварительной оценки Office. Мы продолжим оптимизировать эти функции в течение следующих нескольких месяцев. Когда они будут готовы, мы выпустим их для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.

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

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

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

Нужна дополнительная помощь?

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

См. также

Обзор таблиц Excel

Видео: Создание таблицы Excel

Создать или удалить таблицу Excel

Отформатировать таблицу Excel

Изменение размера таблицы путем добавления или удаления строк и столбцов

Фильтрация данных в диапазоне или таблице

Преобразование таблицы в диапазон

Использование структурированных ссылок с таблицами Excel

Поля промежуточных и итоговых сумм в отчете сводной таблицы

Поля подытога и итога в сводной таблице

Проблемы с совместимостью таблиц Excel

Экспорт таблицы Excel в SharePoint

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

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

Что такое таблица Excel?

Преимущества таблиц Excel

Подготовка данных 

Создание таблицы Excel 

Сортировка и фильтрация данных

Отключить фильтры таблиц

Переименовать таблицу Excel

Изменить стиль таблицы Excel

Создать таблицу Excel с особым стилем в таблицу со строкой итогов

См. столбец таблицы в формуле

Проблема: копирование формул со ссылками на таблицы

Проблема: таблица не расширяется для новых данных

Проверка данных отсутствует в новых строках

Добавить поле счетчика

Печать только таблицы Excel

Макросы таблиц Excel

Получить файл примера

Связанные учебные пособия

Что такое таблица Excel?

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

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

Преимущества таблицы Excel

Список в виде именованной таблицы Excel просто отформатировать, и есть много преимуществ и всего несколько недостатков

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

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

Однако использование именованных таблиц Excel имеет несколько недостатков, поэтому могут возникнуть ситуации, когда вы предпочитаете их не использовать. Например:

  • Структурированные ссылки на ячейки таблицы не имеют «абсолютной» настройки, поэтому немного сложнее скопировать их по столбцу

    • .

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

Подготовка данных

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

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

Создание таблицы Excel

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

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

Сортировка и фильтрация данных

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

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

  • ПРИМЕЧАНИЕ . В Excel 2013 и более поздних версиях вы также можете использовать срезы таблиц Excel для быстрой фильтрации данных таблицы.

Отключить фильтры таблиц

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

Вот несколько причин, по которым вам может понадобиться скрыть фильтры:

  • Кнопки фильтра закрывают часть текста в заголовках столбцов
  • Не рекомендуется фильтровать таблицу и скрывать некоторые строки данных

Чтобы отключить фильтры:

  1. Выберите ячейку в таблице Excel
  2. На вкладке «Данные ленты» нажмите команду «Фильтр», чтобы отключить фильтр.

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

Переименование таблицы Excel

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

Чтобы изменить имя таблицы:
  1. Выберите любую ячейку в таблице
  2. На ленте на вкладке Работа с таблицами щелкните вкладку Дизайн.

  3. В крайнем левом углу ленты щелкните поле Имя таблицы, чтобы
    выберите существующее имя

  4. Затем введите новое имя, например Заказы, и нажмите клавишу Enter.

Изменить стиль таблицы Excel

После создания таблицы Excel ее внешний вид можно быстро изменить в любое время.

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

Чтобы выбрать другой стиль для существующей таблицы Excel, выполните следующие действия:

  • Выберите любую ячейку в таблице Excel
  • На ленте щелкните вкладку «Конструктор таблиц».
    • Или на вкладке Работа с таблицами щелкните вкладку Дизайн
  • В группе «Стили таблиц» нажмите кнопку «Дополнительно» , чтобы открыть палитру «Стили таблиц»

Затем выполните следующие действия, чтобы применить один из стилей:

  • В палитре «Стили таблиц» укажите любой из стилей таблиц
  • Таблица на рабочем листе изменится автоматически, чтобы показать предварительный просмотр выделенного стиля.
    • Необязательно. Укажите на другие стили, чтобы найти тот, который вам нравится
  • Когда вы выберете стиль таблицы, щелкните этот стиль в палитре
  • Палитра «Стили таблиц» закроется, и выбранный стиль будет применен к таблице Excel на рабочем листе.

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

Создать таблицу Excel с
Особый стиль

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

Посмотрите это видео, чтобы увидеть шаги и написанное
Инструкции приведены под видео

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

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

Если вы хотите применить определенный стиль таблицы при создании таблицы Excel
Таблица, выполните следующие действия:

  1. Выберите ячейку в подготовленном вами списке данных.
  2. На ленте щелкните вкладку Главная.
  3. В группе Стили щелкните Форматировать как таблицу
  4. Нажмите на стиль, который вы хотите использовать

    ИЛИ, чтобы применить стиль и удалить существующее форматирование, щелкните правой кнопкой мыши
    в стиле и нажмите «Применить и очистить форматирование»

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

Создана отформатированная таблица Excel с выбранным стилем таблицы.

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

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

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

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

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

Изменить пользовательский стиль таблицы

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

  • В верхней части диалогового окна «Изменить таблицу» введите имя для нового стиля таблицы.
    • Если вы создали новый стиль, диалоговое окно будет называться Новый экспресс-стиль сводной таблицы.)
  • Затем прокрутите список элементов таблицы, чтобы найти элемент, который вы хотите изменить.
    • Совет: к элементам, выделенным полужирным шрифтом, применено форматирование
  • Чтобы изменить форматирование элемента, щелкните его, чтобы выбрать.
    • Вы можете выбрать любой элемент в списке, независимо от того, выделен он жирным шрифтом или нет
  • Далее нажмите кнопку Формат
  • В диалоговом окне «Формат ячеек» выберите нужные параметры «Шрифт», «Граница» и «Заливка» для выбранного элемента.
  • Затем нажмите «ОК», чтобы вернуться в диалоговое окно «Изменить таблицу».
  • В правой части диалогового окна вы можете увидеть измененное форматирование в разделе предварительного просмотра
  • Необязательно — Повторите шаги форматирования для других элементов, если вы хотите их изменить
  • Необязательно: установите флажок «Установить стиль таблицы по умолчанию для этого документа».
  • Нажмите OK, чтобы закрыть диалоговое окно

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

Применить пользовательский стиль таблицы

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

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

На этом снимке экрана новый пользовательский стиль MY GREEN TABLE применяется к таблице Excel.

Показать итоги в таблице

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

Посмотрите это видео, чтобы увидеть шаги, а письменные инструкции находятся под видео.

  • На ленте на вкладке Работа с таблицами щелкните вкладку Дизайн
  • В группе «Параметры стиля таблицы» установите флажок «Строка итогов».

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

    • Вычисления строки итогов

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

    • Если все записи состоят из чисел, первый аргумент равен 109 – СУММ.
    • Если есть нечисловые записи, первый аргумент равен 103 – COUNTA
    Итоги для отфильтрованных данных

    Excel использует функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ в итоговой строке, так как эта функция вычисляет итог только для элементов, которые видны после применения фильтра.

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

    Например, добавьте формулу АГРЕГАТ в строку Итого — у нее больше функций и опций, чем ПРОМЕЖУТОЧНЫЙ ИТОГ.

    Изменить и добавить итоги

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

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

    А ПРОМЕЖУТОЧНЫЙ ИТОГ
    формула добавляется в ячейку, и она показывает расчет на основе
    на видимых ячейках в столбце таблицы.

    Отключение строки итогов

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

    • Выберите ячейку в таблице Excel
    • На ленте Excel в разделе Работа с таблицами щелкните вкладку Дизайн
    • В группе «Параметры стиля таблицы» установите или снимите флажок «Строка итогов»

    Добавить данные в таблицу со строкой итогов

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

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

    Добавить одну новую строку

    Чтобы добавить одна строка новых данных:

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

    В вставьте новые данные в конец таблицы

    • Скопируйте данные, которые вы хотите добавить ( Ctrl+C )
    • Выберите первую ячейку в строке Заголовок, даже если там есть текст или формула
    • Затем нажмите Enter или Ctrl+V , чтобы вставить скопированные данные

    См. столбец таблицы в формуле

    Когда формула ссылается на часть именованной таблицы Excel, структурированная ссылка
    создано. Структурированная ссылка покажет имя столбца таблицы,
    и может включать имя таблицы. Например, это ссылка на
    Столбец Product в таблице с именем OrderRef — OrdersRef[Product]

    Формула вне таблицы

    В этом примере формула будет создана вне таблицы.
    формула будет использовать СЧИТАТЬПУСТОТЫ
    Функция для подсчета пустых ячеек в столбце таблицы. Таблица
    с именем OrderRef, а столбец с именем Product.

    1. Чтобы запустить формулу, выберите пустую ячейку и введите: =СЧИТАТЬПУСТОТЫ(
    2. Затем щелкните в самом верху ячейки заголовка для столбца, который
      вы хотите проверить — указатель изменится на стрелку вниз.

    • Не нажимайте кнопку столбца, где стоит буква столбца.

    • И не нажимайте в середине ячейки заголовка

  • Структурированная ссылка должна отображать имя таблицы и имя столбца:

    • =СЧИТАТЬПУСТОТЫ(OrdersRef[Product]

  • Затем введите закрывающую скобку и нажмите Enter, чтобы завершить формулу.

    • Проблема: копирование формул со ссылками на таблицы

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

    Это происходит потому, что ссылки на таблицы НЕ заблокированы, поэтому ссылки на столбцы настраиваются автоматически при перетаскивании вправо.

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

    Хронология видео
    • 00:00 Введение
    • 00:46 ПРОМЕЖУТОЧНЫЙ ИТОГ Формула для проверки итогов
    • 01:19 СУММЕСЛИМН Формула со ссылками на таблицы
    • 01:42 Скопируйте формулу на одну строку вниз
    • 02:13 Копировать через
    • 03:09 Копировать без проблем
    • 03:45 Получить дополнительную информацию

    Исправление: Копирование формулы со ссылками на таблицы

    Во избежание проблем при копировании формул со ссылками на таблицы НЕ ПЕРЕТАЩИВАЙТЕ маркер заполнения для копирования.

    Вместо этого используйте один из следующих методов для копирования формул:

    • Заполните вправо с помощью сочетания клавиш: Ctrl + R
    • Скопируйте формулу (Ctrl + C) и вставьте ее в другие ячейки (Ctrl + V)

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

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

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

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

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

    Очистить строки ниже таблицы

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

    Чтобы устранить проблему, попробуйте удалить или очистить строки под таблицей Excel

    Вот комментарий Талли в моем блоге Contextures, объясняющий этот тип проблемы и то, как он ее исправил:

    • Под моей таблицей скрывались невидимые данные. (Ячейки с одним пробелом в них, которые были вставлены во время какого-то специального тестирования давным-давно.)
    • Я предполагаю, что Excel видит заполненные ячейки под таблицей и не добавляет их в новую строку списка в таблице.
    • Удалил эти значения и снова заработал как положено
    Скрытые строки под таблицей

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

    Чтобы отобразить все строки:

    • Нажмите кнопку «Выбрать все» в левом верхнем углу листа Excel
    • Щелкните правой кнопкой мыши любую из кнопок строки
    • Нажмите команду «Показать»

    Затем, при необходимости, очистите скрытые строки, чтобы удалить все данные. Или переместите данные в другое место в книге.

    Видео: изменение настроек автоформата таблицы Excel

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

    ПРИМЕЧАНИЕ : Изменение этих параметров повлияет на все книги
    которые вы открываете в Excel на своем компьютере.

    Включить настройки автоформата таблицы

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

    ПРИМЕЧАНИЕ : изменение этих параметров повлияет на все рабочие книги , которые вы открываете в Excel на своем компьютере.

    1. В левом конце ленты щелкните вкладку «Файл», затем щелкните
      Опции
    2. В окне параметров Excel слева щелкните Проверка правописания.

    3. В разделе «Параметры автозамены» щелкните «Параметры автозамены».

    4. Перейдите на вкладку «Автоформат при вводе»
    5. Добавить галочки для «Включить новые строки и столбцы в таблицу»
      и «Заполните формулы в таблицах для создания вычисляемых столбцов»

    6. Дважды нажмите OK, чтобы вернуться в Excel

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

    Включение или отключение этих параметров с помощью VBA

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

    Код макроса показан на этой странице, и вы также можете загрузить образец книги Excel, содержащий код макроса.

    Проверка данных отсутствует в новых строках

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

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

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

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

    • В таблице Excel выберите одну из ячеек с раскрывающимся списком
    • Нажмите Ctrl + C, чтобы скопировать ячейку
    • Выберите все ячейки данных в столбце, в который вы скопировали ячейку (не включая ячейку заголовка)
    • Щелкните правой кнопкой мыши одну из выделенных ячеек и выберите Специальная вставка
    • В диалоговом окне «Специальная вставка» в разделе «Вставка» щелкните «Проверка».
    • Нажмите OK, чтобы применить параметры проверки данных ко всем выбранным ячейкам

    Добавить поле счетчика

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

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

    1. На листе OrdersTable добавьте новый заголовок в первый пустой столбец
      — Продажи
    2. В ячейке под заголовком введите формулу: =1
    3. Нажмите Enter, чтобы завершить формулу

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

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

    Печать только таблицы Excel

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

    Читайте также: