Как сделать таблицу в экселе с подсчетом данных: Как сделать таблицу в Excel за 5 минут

Содержание

Как сделать таблицу в Excel за 5 минут

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

По-простому

По-простому, но красиво

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

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

Итак, нам понадобятся: компьютер, Excel и 5 минут времени. Для начала вам нужно включить компьютер и перейти в программу Эксель (90% дела будет сделано).

Если нет компьютера или программы, то сорри, миссия провалена.

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

Здесь есть строки и столбцы (в принципе как и у любой нормальной таблицы) — это номер один, что вам нужно знать про Эксель.

Работа в программе происходит на листах, например сейчас вы находитесь на первом листе. Листов можно создавать много — это номер два, что вам нужно знать про Эксель.

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

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

Чтобы сделать таблицу в Excel выполним несколько простых шагов:

Шаг 1. Введём названия столбцов и строк и заполним таблицу данными:

Шаг 2. Зададим границы таблицы:

Вот и всё, таблица готова, поздравляю вас с хорошей работой, теперь можно и отдохнуть! Или нет?

Внимание! Сейчас вылетит фишка!

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

  • Выделяем одну из ячеек внутри таблицы (в первом столбце — это важно).
  • Нажимаем комбинацию клавиш CTRL+A (таблица должна выделиться)
  • Нажимаем решетку (см. скрин выше) и выбираем «Все границы»

Кстати, я думаю вы же делаете табличку не просто так (для красоты), а для работы. Это значит вам необходимо хоть как-то научиться манипулировать данными внутри неё.

Поэтому покажу пару самых часто используемых приёмов.

Приём первый — фильтры в таблицах Эксель

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

Чтобы воспользоваться этим чудо-инструментом, нужно выделить всю таблицу (например используя CTRL+A), затем вверху справа выбрать «Сортировка и фильтр» и в выпадающем меню выбрать «Фильтр».

Получается вот такая красота, покликайте на эти штуки и узнаете на что они способны!

Приём второй — автосумма в Excel

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

Ба-бам, рядом появится еще одна ячейка с суммой.

Магия вне Хогвартса удалась!

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

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

Поэтому! Давайте сразу расскажу как добавлять/убавлять строки/столбцы в эту самую таблицу. Делается это элементарнейшим образом.

Выделяем строку/столбец, перед которыми нужно добавить новую строку или столбец и жмем кнопку «Вставить» (как показано на скрине), затем выбираем то что мы хотим вставить.

Этот же манёвр можно сделать более элегантно и после выделения, например, строки, нажмите «CTRL + ПЛЮС».

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

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

Хотите стать гуру, тогда читайте дальше.

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

Сделать это невероятно легко, таблица создаётся буквально в 4 шага!

Итак. Переходим в вкладку «Вставка» – выделяем область таблицы – нажимаем на «Таблицу» – нажимаем «Ок»

иииии всё! Готово! Должна получиться красивая табличка, да еще и сразу с фильтрами, попробуйте, вам понравится!

Видео о том как сделать таблицу в Excel

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

Итоги сего повествования

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

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

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

Автор:
Павел Истомин

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

  • WINDOWS
  • MAC OS
  • LINUX
  • IOS
  • ANDROID
  • игры
  • Новости
  • Статьи и обзоры

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

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

Таблица в Excel

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

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

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

  1. Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
  2. В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table]. 

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

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

 

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

  1.  ОК.

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

По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя. 

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.

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

Требования к именам таблиц аналогичны требованиям к именованным диапазонам.

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

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

  1. Выделить ячейку таблицы.
  2. На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

 

Вычисления в таблицах

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

Порядок действия следующий:

  1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

Добавление строки итогов

  1. В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию. 

Добавление строки итогов

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

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

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

Вставка формул в таблицу

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

  1. Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
  2. В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
  3. OK.

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

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

Для этого необходимо выполнить следующие шаги:

  1. На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
  2. Выбрать вкладку Преобразовать в диапазон [Convert to Range].

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

  1. Нажать на кнопку Да [Yes].

Как сделать таблицу в Эксель

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

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

Заполнение диапазона ячеек информацией

  1. Для начала давайте внесем в ячейки документа необходимые данные, из которых потом будет состоять наша таблица.Как создать таблицу в Excel
  2. После этого можно отметить границы данных. Для этого выделяем курсором нужный диапазон ячеек, затем переходим во вкладку «Главная». Здесь нам необходимо найти параметр «Границы». Щелкаем рядом с ним по стрелке вниз, которая раскроет список с вариантами границ и выбираем пункт “Все границы”.Как создать таблицу в Excel
  3. Итак, визуально выделенная область стала выглядеть как таблица.Как создать таблицу в Excel

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

Как преобразовать диапазон данных в полноценную таблицу

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

  1. Чтобы это сделать нам потребуется выбрать вкладку «Вставка». После этого выделяем курсором нужную область, и кликаем по пункту «Таблица».
    Как создать таблицу в Excel
    Примечание: в случае, если размеры окна, в котором открыт Excel невелики, возможно, во вкладке “Вставка” вместо пункта “Таблица” будет раздел “Таблицы”, раскрыв который стрелкой вниз, можно найти именно пункт “Таблица”, которым нам и нужен.
    Как сделать таблицу в Эксель
  2. В результате откроется окно, где будут обозначены координаты выделенной нами заранее области данных. В случае, если все было выделено верно, то ничего менять не нужно и просто щелкаем кнопку “OK”. Как вы могли заметить, также в этом окне имеется параметр «Таблица с заголовками». Галочку следует оставить, если ваша таблица, действительно, имеет заголовки, в противном случае галочку следует убрать.
    Как сделать таблицу в Эксель
  3. Вот, собственно говоря, и все. Таблица полностью готова.
    Как сделать таблицу в Эксель

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

Как сделать таблицу в Excel

Таблица в Microsoft Excel

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

Основы создания таблиц в Excel

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

Способ 1: Оформление границ

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

  1. Для начала создайте документ Excel, откройте его и введите в желаемые клетки данные.
  2. Введенные данные в произвольные диапазоны Ексель

  3. Произведите выделение ранее вписанной информации, зажав левой кнопкой мыши по всем клеткам диапазона.
  4. Выделенный блок введенных данных в Екселе

  5. На вкладке «Главная» в блоке «Шрифт» нажмите на указанную в примере иконку и выберите пункт «Все границы».
  6. Кнопка выделения всех границ выделенного диапазона в Ексель

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

    Соответственно, чтобы убрать оформление границ у таблицы, необходимо кликнуть на ту же иконку, но выбрать пункт «Нет границы».

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

  9. Для ручного оформления границ каждой из позиций можно воспользоваться специальным инструментом и нарисовать их самостоятельно. Перейдите уже в знакомое меню выбора оформления клеток и выберите пункт «Нарисовать границу».
  10. Кнопка нарисовать границу в Ексель

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

    Произвольно оформленные границы в Ексель

Обратите внимание! Оформление границ клеток работает как с пустыми ячейками, так и с заполненными. Заполнять их после обведения или до — это индивидуальное решение каждого, всё зависит от удобства использования потенциальной таблицей.

Способ 2: Вставка готовой таблицы

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

  1. Переходим во вкладку «Вставить».
  2. Меню вставки в Екселе

  3. Среди предложенных кнопок выбираем «Таблица».
  4. Меню вставки таблицы в Екселе

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

  7. Отпускаем кнопку мыши, подтверждаем выбор соответствующей кнопкой и любуемся совершенно новой таблице от Excel.
  8. Результат вставки базовой таблицы в Екселе

  9. Редактирование названий заголовков столбцов происходит путём нажатия на них, а после — изменения значения в указанной строке.
  10. Изменение данных заголовка таблицы в Екселе

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

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

Способ 3: Готовые шаблоны

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

  1. Открыв Excel, перейдите в главное меню нажатием кнопки «Файл».
  2. Меню файла в Екселе

  3. Нажмите вкладку «Создать».
  4. Кнопка создания файла в Екселе

  5. Выберите любой понравившийся из представленных шаблон.
  6. Выбранный готовый шаблон в Ексель

  7. Ознакомьтесь с вкладками готового примера. В зависимости от цели таблицы их может быть разное количество.
  8. Вкладки готового шаблона в Ексель

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

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

Пример готового шаблона бюджета в ЕксельМы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

ДА НЕТ

Как сделать таблицу в Эксель/Excel (Инструкция)

Вы хотите узнать, как сделать таблицу в программе Microsoft Office Excel? На самом деле, на этот вопрос нельзя ответить однозначно. Более того, на него есть десятки, если не сотни ответов – потому что в табличный редактор Эксель заложен невероятно обширный функционал. По статистике, неопытный пользователь, коих большая часть, не использует примерно 90-95% возможностей программы.

Универсальный способ по созданию таблицы в Эксель

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

  1. Выделите все элементы будущей или уже созданной таблицы, используя мышь. Для этого зажмите левую кнопку мыши и ведите курсор до крайнего элемента по диагонали; Выделяем
  2. Теперь кликните по любому из выделенных элементов правой кнопкой мыши для открытия контекстного меню; Меню
  3. Здесь Вы должны выбрать пункт «Формат ячеек…». Кликнув по нему, Вы через мгновение увидите открывшееся окошко с множеством новой для Вас информации;Выбираем формат
  4. По умолчанию там будет открыта вкладка «Число», но в данном случае она нам совсем не требуется. Вы должны перейти на вкладку «Граница», находящуюся неподалёку, где и будет произведён основной массив настроек; Выбираем "Граница"
  5. Как видите, теперь перед Вами находится окно, отвечающее за границы Вашей таблицы. Здесь Вы можете указать, какие именно Вы хотите проставить границы – внутренние или внешние. К слову, можно выбирать и те, и другие. Также Вы можете задать их цвет и тип линии. Если Вам нужна стандартная таблица, то стоит задать настройки как на скриншоте и кликнуть по кнопке «ОК»; Выбираем нужный формат
  6. Как видите, теперь наша таблица приобрела очертания и готова к печати. Итог

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

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

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

  1. Вам также первым делом стоит выделить таблицу или то место, где она будет располагаться;
  2. Далее следует вернуться ко вкладке «Главная», если Вы успели её переключить;
  3. Здесь нужно найти кнопку «Форматировать как таблицу» — её расположение в зависимости от версии табличного редактора может меняться. Возможно, придётся потратить несколько секунд на поиски; Форматировать как таблицу
  4. Кликните по ней и в выпадающем списке выберите тот вариант, который приглянулся Вам больше остальных или соответствует стилю будущего документа; Выбираем понравившиеся стиль
  5. Если у Вашей таблицы должны быть отличающиеся от остальных её элементов заголовки, в диалоговом окне поставьте галочку напротив соответствующего пункта; Выбираем нужное значение
  6. Готово! Теперь посмотрите, как сильно преобразился вид вывода данных! Итог

Что ж, теперь Вы знаете, как делать таблицы в Экселе, как задавать им определённое форматирование и стили. Хотелось бы добавить ещё вот что: не забывайте экспериментировать, у программы много интересных функций!

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

Начало работы в Excel

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

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

КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL ДЛЯ ЧАЙНИКОВ. Пошагово

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

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

Excel

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

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

Видео на тему: Excel для начинающих

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

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

Excel

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

Excel

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

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

Видео на тему: ТОП 15 лучших трюков Excel

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

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

  • Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.

Excel

  • Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.

Excel

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

Excel

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

Excel

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

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

Excel

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

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

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

Excel

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

Excel

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

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

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

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

  • Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.

Excel

  • Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.

Excel

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

ExcelExcel

  • Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Excel

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

Excel

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

Excel

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

КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL: ПОШАГОВАЯ ИНСТРУКЦИЯ

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

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

  • Переходим на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).

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

  • В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

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

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

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

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

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

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

КАК РАБОТАТЬ С ТАБЛИЦЕЙ В EXCEL

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

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

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

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

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

  • Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  • При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.

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

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

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

  • Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).

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

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

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

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

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

Вот ещё статьи про работу в Exel:

Как закрепить строку и столбец в Excel при прокрутке.

Как в Excel создаются формулы и таблицы. Пошагово.

Как в Эксель посчитать сумму столбца.

Как считать в функциях Excel

Contextures

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

Обзор: 7 способов
Подсчитайте в Excel

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

Загрузите 7 способов
to Count образец рабочей книги, чтобы вы могли следить за видео.

Счетчик чисел, всех данных или пустых ячеек

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

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

Подсчет ячеек с числами — COUNT

Функция COUNT будет подсчитывать ячейки, содержащие числа. Его синтаксис
это:
= СЧЁТ (значение1 , значение2, … значение30).
Аргументы (например, значение1) могут быть ссылками на ячейки или введенными значениями.
в формулу COUNT.

В следующем примере функции COUNT используется один аргумент — ссылка
в ячейки A1: A5.

  1. Введите данные образца в свой рабочий лист
  2. В ячейке A7 введите формулу COUNT, чтобы подсчитать числа в столбце.
    A: = СЧЁТ (A1: A5)
  3. Excel Count Function

  4. Нажмите клавишу Enter, чтобы заполнить формулу.
  5. Результатом будет 3 — количество ячеек, содержащих числа.

    Ячейка A1 не учитывается, поскольку она содержит текст. top of page

Примечание : Поскольку даты хранятся в виде чисел, функция СЧЁТ
будет включать любые ячейки, содержащие даты.

Подсчет ячеек с данными — COUNTA

Функция СЧЕТЧИК подсчитывает непустые ячейки. (См. Примечание к пустым ячейкам ниже)

Его синтаксис
это:
= СЧЁТ (значение1 , значение2, … значение30).
Аргументы (например, значение1) могут быть ссылками на ячейки или введенными значениями.
в формулу. В следующем примере используется один аргумент — ссылка
в ячейки A1: A5.

  1. Введите данные образца в свой рабочий лист
  2. В ячейке A7 введите формулу COUNTA, чтобы подсчитать числа в столбце.
    A: = СЧЁТ (A1: A5)
  3. Excel Count Text

  4. Нажмите клавишу Enter, чтобы заполнить формулу.
  5. Результатом будет 4, количество ячеек, содержащих данные.

Подсчет пустых ячеек — СЧИТАТЬПУСТОТЫ

Функция СЧИТАТЬПУСТОТЫ подсчитывает пустые ячейки. (См. Примечание к пустым ячейкам ниже)

Его синтаксис
составляет:
= СЧИТАТЬПУСТОТЫ (диапазон ).
В следующем примере используется ссылка на ячейки A1: A5.

  1. Введите данные образца в свой рабочий лист
  2. В ячейке A7 введите СЧЕТЧИК ПУСТОЙ формулы, чтобы подсчитать числа в
    столбец A: = СЧИТАТЬПУСТОТЫ (A1: A5)
  3. Excel Count blanks

  4. Нажмите клавишу Enter, чтобы заполнить формулу.
  5. Результатом будет 1, количество пустых ячеек.

Ячейки, которые выглядят пустыми

И COUNTA, и COUNTBLANK будут подсчитывать ячейки с формулами, которые выглядят пустыми, если результатом формулы является пустая строка {«»). Пустая строка рассматривается как текст и считается.

Например, = ЕСЛИ (B2 = «», «», B2).

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

  • ПРИМЕЧАНИЕ. Вы можете видеть апострофы в этих «непустых» преобразованных ячейках,
    если вы добавите галочку в «Параметры Excel», «Дополнительно», «Совместимость с Lotus», «Переход
    клавиши навигации «

Чтобы исправить ячейки, которые выглядят пустыми, но подсчитываются, см. Мой блог Contextures: Исправить пустые ячейки Excel, скопированные из базы данных ▲ TOP

Подсчитайте ячейки,
соответствует одному критерию

В этом видео показано, как использовать функцию СЧЁТЕСЛИ для подсчета
ячейки, содержащие определенную строку текста, например «Pen».Вы также можете найти текст, который является частью ячейки — сколько заказов было
размещены для любого типа ручки, такой как «Гелевая ручка», «Ручка» или даже «Карандаш»?

Письменные инструкции под видео: Матч
критерий точно и критерий соответствия в
строка

Также будьте осторожны, потому что вы можете получить False
Дубликаты С СЧЁТЕСЛИ и СЧЁТЕСЛИ не подсчитывают определенные числа в других числах. И считать конкретный текст, не считая
аналогичный текст, см.
пример здесь.

Точно соответствует одному критерию — COUNTIF

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

  1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
    этот пример)
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СЧЁТЕСЛИ (
  4. Выберите ячейки, содержащие значения для проверки критерия.В этом примере будут проверяться ячейки A1: A10
  5. Введите запятую для разделения аргументов
  6. Введите критерий. В этом примере вы проверяете текст, поэтому
    введите слово в двойных кавычках: «Pen»
    Примечание: верхний и нижний регистр обрабатываются одинаково
  7. Введите закрывающую скобку
    Заполненная формула: = СЧЁТЕСЛИ (A1: A10, «Перо»)
  8. Count Excel criteria

  9. Нажмите клавишу Enter для завершения ввода
  10. Результатом будет 4, количество ячеек, содержащих «Pen» ▲ TOP

Критерий соответствия в строке

В Excel подсчитайте ячейки, содержащие критерий, как часть
содержание.В этом примере все заказы Pen, Gel Pen и Pencil будут
считаться, потому что они содержат строку «ручка».

  1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
    этот пример)
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СЧЁТЕСЛИ (
  4. Выберите ячейки, содержащие значения для проверки критерия.
    В этом примере будут проверяться ячейки A1: A10
  5. Введите запятую для разделения аргументов
  6. Введите критерий.В этом примере вы проверяете текст, поэтому
    введите слово в двойные кавычки с одной или несколькими подстановочными знаками звездочки (*)
    символы: «* Pen *»
    Примечание: верхний и нижний регистр обрабатываются одинаково
  7. Введите закрывающую скобку
    Заполненная формула: = СЧЁТЕСЛИ (A1: A10, «* Pen *»)
  8. Excel Count criteria string

  9. Нажмите клавишу Enter для завершения ввода
  10. Результатом будет 6, количество ячеек, содержащих строку,
    «Ручка» ▲ ТОП

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете
относятся к ячейке.Например, формула на шаге 7 выше может быть
изменено на:
= СЧЁТЕСЛИ (A1: A10, «*» & B12 &
«*»)
, если в ячейке B12 был текст — pen .

Ложные дубликаты с COUNTIF

Будьте осторожны при использовании СЧЁТЕСЛИ, потому что он соответствует числам с текстом
выглядит как числа и может привести к ложным подсчетам.

Например, если у вас есть текстовая запись — «00123» — это будет
засчитываться как дубликат числа — 123.

= СЧЁТЕСЛИ (2 $ B $: 10 B $; B2))>

Если ваши данные могут содержать подобные записи, используйте SUMPRODUCT вместо
из COUNTIF.

= СУММПРОИЗВ (- ($ B $ 2: $ B $ 10 = B2))> 1

false duplicates with COUNTIF

Подсчитайте числа в числах

Функция СЧЁТЕСЛИ может находить определенные буквы или числа в текстовой строке. Однако СЧЁТЕСЛИ не может найти конкретное число в действительном числе.

На снимке экрана ниже в списке есть 4 элемента, которые содержат цифру «1». Формула СЧЁТЕСЛИ в ячейке A9 дает неверный результат 3. Она не учитывает «1» в ячейке A2, потому что эта ячейка содержит действительное число, а не текстовую строку.

false duplicates with COUNTIF

Используйте НАЙТИ или ПОИСК

Хотя функция СЧЁТЕСЛИ не может подсчитывать конкретные числа в пределах действительных чисел, функция НАЙТИ или ПОИСК найдет их.

На снимке экрана ниже в столбцах C, D и E используются следующие формулы для поиска «1».

Функция ISNUMBER возвращает True или False, а два знака минус (двойной унарный) преобразуют True в 1 и False в 0 (ноль).

  • C2: = СЧЁТЕСЛИ (A2; «* 1 *»)
  • D2: = — ISNUMBER (НАЙТИ (1; A2))
  • E2: = — ISNUMBER (ПОИСК (1; A2))

Формулы НАЙТИ и ПОИСК нашли все 4 элемента, содержащих «1». Формула СЧЁТЕСЛИ нашла только 3 элемента.

false duplicates with COUNTIF

Используйте SUMPRODUCT с FIND или SEARCH

Вместо того, чтобы проверять каждую строку по отдельности, используйте функцию СУММПРОИЗВ с НАЙТИ или ПОИСК, чтобы получить общее количество для списка.

  • Используйте FIND для подсчета чисел или с учетом регистра букв
  • Используйте SEARCH для подсчета чисел или прописных и строчных букв ( без учета регистра )

Пример 1: Числа

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

  • A9: = СУММПРОИЗВ (- ISNUMBER (НАЙТИ (1, $ A $ 2: $ A $ 7)))
  • A10: = СУММПРОИЗВ (- ISNUMBER (ПОИСК (1, $ A $ 2: $ A $ 7)))

false duplicates with COUNTIF

Пример 2: Буквы

В следующем примере формулы дают количество ячеек, содержащих «a».FIND считает только строчные буквы «a» (1), а SEARCH считает как прописные буквы «A», так и строчные буквы «a» (2).

  • A9: = СУММПРОИЗВ (- ISNUMBER (НАЙТИ («a», $ A $ 2: $ A $ 7)))
  • A10: = СУММПРОИЗВ (- ISNUMBER (ПОИСК («a», $ A $ 2: $ A $ 7)))

false duplicates with COUNTIF

Подсчет ячеек «Меньше» или «Больше»
Чем «

Из этого видео вы узнаете, как использовать
функция СЧЁТЕСЛИ для подсчета количества элементов в списке, которые
больше или меньше определенной суммы.Письменная инструкция под видео:

Подсчет ячеек больше или равных

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

  1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
    этот пример)
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СЧЁТЕСЛИ (
  4. Выберите ячейки, содержащие значения для проверки критерия.В этом примере будут проверяться ячейки B1: B10
  5. Введите запятую для разделения аргументов
  6. Введите критерий. В этом примере вы проверяете строки, в которых
    количество больше или равно 10. Оператор> =
    используется перед числом, а весь критерий заключен в двойную
    цитаты: «> = 10»
    Примечание. Несмотря на то, что это числовой критерий, он должен быть заключен в
    в двойных кавычках.
  7. Тип закрывающей скобки
  8. Заполненная формула:
    = СЧЁТЕСЛИ (B1: B10, «> = 10»)
  9. Нажмите клавишу Enter, чтобы завершить ввод

    Count Excel criteria operator

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете
относятся к ячейке. Например, формула на шаге 8 выше может быть
изменено на:
= СЧЁТЕСЛИ (B1: B10, «> =» & B12)
, если в ячейке B12 содержится номер — 10 .

Или вы можете использовать функцию как часть критерия. Например:
= СЧЁТЕСЛИ (A1: A10, «<" & СЕГОДНЯ ()) ▲ TOP

Подсчет ячеек от 5 до 10

Вы можете комбинировать формулы СЧЁТЕСЛИ для подсчета строк, находящихся в
диапазон значений. В этом примере формула будет подсчитывать строки, в которых
количество от 5 до 10 (включительно).

  1. Выберите ячейку, в которой вы хотите увидеть счетчик (ячейка A12 в
    этот пример)
  2. Введите формулу для подсчета строк, больше или равных 5:
    = СЧЁТЕСЛИ (B1: B10, «> = 5»)
  3. Введите знак минуса
  4. Введите формулу для подсчета строк больше 10:
    СЧЁТЕСЛИ (B1: B10, «> 10»)
  5. Заполненная формула:
    = СЧЁТЕСЛИ (B1: B10, «> = 5») СЧЁТЕСЛИ (B1: B10, «> 10»)
  6. Нажмите клавишу Enter, чтобы завершить ввод

    Excel Count criteria match

Примечание : вместо того, чтобы вводить критерий в формуле, вы можете
относятся к ячейке.Например, формула на шаге 8 выше может быть
изменено на:
= СЧЁТЕСЛИ (B1: B10, «> =» & B12)

СЧЁТЕСЛИ (B1: B10, «>» & C12)
, если ячейка B12 содержит число — 5 и ячейка C12
содержал номер — 10 . ▲ ТОП

Изменить оператора для функции СЧЁТЕСЛИ

Вместо того, чтобы вводить оператор в формулу СЧЁТЕСЛИ, как показано выше,
вы можете создать список всех возможных операторов и выбрать один из
выпадающий список.Затем обратитесь к этому оператору в формуле.

В этом видео показаны шаги по настройке формулы, а письменные инструкции приведены под видео.

Создание раскрывающегося списка операторов

Для создания раскрывающегося списка операторов:

  1. На другом листе книги введите список операторов в
    столбец.
  2. Выберите ячейки в списке и назовите
    этот список как OpList.

    countif operator list

  3. На основном листе выберите ячейку, в которой вы хотите раскрыть
    список — ячейка E6 в этом примере
  4. Используйте команду проверки данных для создания раскрывающегося списка на основе
    на названном диапазоне — OpList

countif operator list

Используйте раскрывающийся список

Измените формулу СЧЁТЕСЛИ, чтобы заменить типизированный оператор ссылкой
в ячейку с выпадающим списком.

= СЧЁТЕСЛИ (B2: B11, E6 и F6)

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

countif operator list

Подсчет ячеек, соответствующих нескольким критериям

В этом видео показано, как использовать функцию СЧЁТЕСЛИМН для подсчета
ячейки на основе нескольких критериев.

Письменная инструкция под видео:

—Считать несколько критериев с помощью СЧЁТЕСЛИМН

—Подсчитайте несколько критериев с помощью SUMPRODUCT

—Считать все даты в конкретном месяце и году

Подсчет ячеек, соответствующих нескольким критериям

В Excel 2007 и более поздних версиях вы можете использовать функцию СЧЁТЕСЛИМН.
для подсчета строк, соответствующих двум или более критериям.В этом примере только
строки, где элемент — «Ручка» и количество больше, чем
или равный десяти будут засчитаны.

  1. Выберите ячейку, в которой вы хотите увидеть итог
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СЧЁТЕСЛИМН (
  4. Выберите ячейки, содержащие значения, которые нужно проверить на
    критерий. В этом примере будут проверяться ячейки A2: A10
  5. Введите запятую и первый критерий: «Pen»
    Примечание. Поскольку это текстовый критерий, он заключен в двойные
    кавычки.
  6. Чтобы начать следующий набор критериев, введите запятую
  7. Выберите ячейки, содержащие значения для проверки второго
    критерий. В этом примере будут проверяться ячейки B2: B10
  8. Введите запятую и второй критерий: «> = 10 »
    Примечание. Поскольку этот критерий включает операторы, он заключен
    в двойных кавычках. Для подсчета строк, в которых количество равно
    10, потребуется только цифра 10.
  9. Отделка закрывающей скобкой 🙂
  10. Заполненная формула показана ниже.
  11. Нажмите клавишу Enter для завершения ввода

Excel Count multiple criteria

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

Используйте типизированные критерии:

= СЧЁТЕСЛИМН (A2: A10, «Pen», B2: B10, «> = 10»)

= СЧЁТЕСЛИМН (A2: A10, D3, B2: B10, «> =» & E3)

Подсчет ячеек, соответствующих нескольким критериям
— СУММПРОИЗВ

В этом примере функция СУММПРОИЗВ используется для подсчета строк, в которых элемент — «Ручка», а количество —
больше или равно десяти.Это решение будет работать в более старых версиях Excel, где функция СЧЁТЕСЛИМН недоступна.

  1. Выберите ячейку, в которой вы хотите увидеть итог
  2. Введите знак равенства (=), чтобы начать формулу
  3. Тип: СУММПРОИЗВ (- (
  4. Выберите ячейки, содержащие значения, которые нужно проверить на
    критерий. В этом примере будут проверяться ячейки A2: A10
  5. Введите первый критерий: = «Pen»
    Примечание. Поскольку это текстовый критерий, он заключен в двойные
    кавычки.
  6. Тип), — (
  7. Выберите ячейки, содержащие значения для проверки второго
    критерий. В этом примере будут проверяться ячейки B2: B10
  8. Введите второй критерий:> = 10
    Примечание. Поскольку это числовой критерий, он не заключен.
    в двойных кавычках.
  9. Отделка закрывающими скобками :))
  10. Заполненная формула показана ниже.
  11. Нажмите клавишу Enter, чтобы завершить ввод

Excel Count multiple criteria

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

Используйте типизированные критерии:

= СУММПРОИЗВ (- (A2: A10 = «Pen»), — (B2: B10> = 10))

или ссылки на ячейки:

= СУММПРОИЗВ (- (A2: A10 = D2), — (B2: B10> = E2))

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

В этом примере в ячейке A2 есть дата, а в списке заказов даты в ячейке A5: A26.Следующая функция СУММПРОИЗВ находится в ячейке D2 и подсчитывает все даты, которые имеют тот же месяц и год, что и дата в ячейке A2.

= СУММПРОИЗВ ((МЕСЯЦ (A5: A26) = МЕСЯЦ (A2)) * (ГОД (A5: A26) = ГОД (A2)))

  • Функция МЕСЯЦ возвращает номер месяца для каждой ячейки и сравнивает его с номером месяца для даты в ячейке A2.
  • Функция ГОД возвращает номер года для каждой ячейки и сравнивает его с номером года для даты в ячейке A2.
  • Учитываются только строки, в которых месяц и год совпадают с ячейкой A2.

Excel Count multiple criteria

Подсчитать повторяющиеся элементы в списке

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

В ячейках A6: C12 находится именованная таблица ( tblIDs ). В столбце ID каждый номер должен быть уникальным, но 2 вводится дважды, а 3 вводится дважды.

duplicate items in column

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

  • В этом примере результат формулы должен быть 2 — есть 2 уникальных значения, которые были дублированы — 2 и 3.
  • Формула НЕ сообщит нам, что 4 строки содержат повторяющиеся идентификационные номера.

Формула для подсчета повторяющихся элементов

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

= СУММПРОИЗВ ((tblIDs [ID] <> «») /
COUNTIF (tblIDs [ID], tblIDs [ID] & «»)
(COUNTIF (tblIDs [ID], tblIDs [ID]) = 1))

formula to count duplicate items in column

ПРИМЕЧАНИЕ : Чтобы просто выделить повторяющиеся значения в столбце, используйте условное форматирование.

Как это работает

Формула СУММПРОИЗВ содержит 3 формулы:

  • A: (tblIDs [ID] <> «») — Проверить, НЕ пуста ли ячейка — ИСТИНА (1) или ЛОЖЬ (0)
  • B: COUNTIF (tblIDs [ID], tblIDs [ID] & «») — Сколько раз идентификатор появляется в столбце
  • C: (COUNTIF (tblIDs [ID], tblIDs [ID]) = 1) — Число (B) равно единице — ИСТИНА (1) или ЛОЖЬ (0)

Затем A делится на B, а C вычитается

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

Вычислить в строке формул

В строке формул вы можете выбрать каждую формулу (A, B и C) и нажать F9 для вычисления этой формулы. На этом снимке экрана показаны результаты.

calculate in the formula bar

Затем вычислите часть формулы A / B, чтобы увидеть эти результаты. ИСТИНА равно 1, поэтому 1/1 равно 1, а 1/2 равно 0,5.

calculate in the formula bar

Затем рассчитайте A / B — C, чтобы увидеть эти результаты. ИСТИНА равно 1, а ЛОЖЬ равно нулю, поэтому 1-1 равно нулю и 0.5-0 равно 0,5:

calculate in the formula bar

Наконец, функция СУММПРОИЗВ дает сумму этих чисел с результатом 2.

Рабочий лист формул

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

На этом снимке экрана вы можете увидеть результаты A и B, затем A / B. Затем посмотрите расчеты C и A / B-C. Внизу последнего столбца отображается сумма.

worksheet formulas for each row

Используйте результат формулы

В других частях книги вы можете ссылаться на ячейку A4 (DupIds), чтобы создавать предупреждающие сообщения или отображать ноль вместо ожидаемых результатов. Например, покажите сообщение с такой формулой:

  • = IF (DupIDs> 0, «Прежде чем продолжить, удалите повторяющиеся идентификаторы.», «»)

Или умножьте на 1 (ИСТИНА) или на ноль (ЛОЖЬ) в других формулах в зависимости от числа в ячейке DupIDs.

  • = COUNT (tblIDs [ID]) * — (DupIDs = 0)

refer to the formula cell in other parts of the workbook

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

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

Подсчет строк в отфильтрованном списке с ПРОМЕЖУТОЧНЫМ ИТОГОМ

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

  1. Примените к столу автофильтр.Здесь есть инструкции —
    Основы работы с автофильтром
  2. Отфильтруйте хотя бы один из столбцов в таблице. В этом примере
    первый столбец был отфильтрован для связующих.
  3. Выберите ячейку непосредственно под столбцом, для которого требуется суммировать.
  4. Щелкните кнопку Автосумма на стандартной панели инструментов Excel.
    • Если вы хотите, чтобы функция ПРОМЕЖУТОЧНЫЙ ИТОГ находилась в другой ячейке
      непосредственно под отфильтрованным списком вы можете ввести формулу вместо
      использования кнопки Автосумма.
  5. Будет автоматически вставлена ​​формула ПРОМЕЖУТОЧНЫЙ ИТОГ,
    видимые ячейки в столбце
  • Первый аргумент функции ПРОМЕЖУТОЧНЫЙ ИТОГ — это номер функции,
    который указывает, как следует рассчитывать числа. По умолчанию
    равно 9, что говорит Excel о суммировании чисел.
  • Можно использовать другие номера функций, например 1 для СРЕДНЕГО и
    3 для COUNTA.Посмотрите полный список в справке Excel.
  • Чтобы подсчитать все непустые ячейки в столбце D, используйте 3 в качестве первого
    аргумент:
    = ПРОМЕЖУТОЧНЫЙ ИТОГ (3, D2: D10)
  • Нажмите клавишу Enter, чтобы завершить ввод формулы.

    Excel Count subtotal

  • Примечание : Чтобы подытог по строкам, которые были скрыты или отфильтрованы вручную, используйте 103 в качестве номера функции вместо 3 :
    = ПРОМЕЖУТОЧНЫЙ ИТОГ (103, D2: D10)
    ▲ TOP

    Подсчет строк в отфильтрованном списке с помощью AGGREGATE

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

    1. Примените к столу автофильтр. Здесь есть инструкции —
      Основы работы с автофильтром
    2. Отфильтруйте хотя бы один из столбцов в таблице. В этом примере
      первый столбец был отфильтрован для связующих.
    3. Выберите ячейку, в которой вы хотите увидеть итог — ячейка B1 в этом примере
    4. Чтобы начать формулу, введите: = AGGREGATE (
    5. В списке номеров функций дважды щелкните 3-COUNTA , затем введите запятую
    6. В списке номеров опций дважды щелкните 3 — Игнорировать скрытые строки, значения ошибок, вложенные функции ПРОМЕЖУТОЧНЫЙ ИТОГ и ОБЩИЙ ИТОГ , затем введите запятую
    7. Выберите ячейки, содержащие значения, которые нужно проверить на
      критерий.В этом примере выбран столбец «Итого» в таблице.
    8. Введите закрывающую скобку, затем нажмите клавишу Enter, чтобы завершить ввод формулы.
      = АГРЕГАТ (3,3, Таблица1 [Итого])

    Подсчитываются два видимых числа, а ошибка в ячейке D9 игнорируется.

    count filtered list with aggregate function to ignore errors

    Подсчет конкретных элементов в отфильтрованном списке

    Лоран Лонгре создал формулу, которая позволяет работать с видимыми строками.
    после фильтра.Для получения информации см. Технику формул мощности в этом
    статья на веб-сайте Джона Уокенбаха (через сайт WayBack Machine):
    https://web.archive.org/web/20100110043824/ https://j-walk.com/ss/excel/eee/eee001.txt

    Включая эту технику, СУММПРОИЗВ может использоваться для подсчета видимых
    элементы в отфильтрованной таблице. В следующем примере столбец D был
    отфильтрован для сумм больше 100. Следующая формула будет считать
    количество видимых строк, содержащих «Pen» в столбце A.

    Count Excel filtered

    1. В раскрывающемся списке в ячейке D1 выберите Пользовательский.
    2. Фильтр для строк больше 100.
    3. В ячейке A12 введите: Pen
    4. В ячейке B12 введите следующую формулу:

    = СУММПРОИЗВ (ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (A1: A10; СТРОКА (A1: A10))
    -МИН (СТРОКА (A1: A10)) ,, 1)), — (A1: A10 = A12))

    1. Нажмите клавишу Enter, чтобы завершить ввод формулы. ▲ ТОП

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

    В бюллетене Excel Expert (выпуск 20, 8 июля 2001 г. — больше не доступен) есть формула для подсчета уникальных элементов в отфильтрованном списке. В этом примере список фильтруется для Центрального региона, и подсчитываются уникальные элементы в столбце D.

    1. Столбец LineVal представляет собой именованный диапазон — Rge
    2. Имя unRge определяется по формуле: = ЕСЛИ (ПРОМЕЖУТОЧНЫЙ ИТОГ (3, СМЕЩЕНИЕ (РЯД, СТРОКА (Rge) -МИН (СТРОКА (RGE)) ,, 1)), Rge, «»)
    3. В ячейке C2 введите следующую формулу:

    = СУММ (N (ЕСЛИ (ISNA (MATCH («» «», unRge, 0)), MATCH (Rge, Rge, 0),
    IF (MATCH (unRge, unRge, 0) = MATCH («» «», unRge, 0), 0,
    MATCH (unRge, unRge, 0))) = ROW (Rge) -MIN (ROW (Rge)) + 1))

    1. Это формула массива, поэтому нажмите Ctrl + Shift + Enter , чтобы завершить формулу. ▲ ТОП

    count filtered list with aggregate function to ignore errors

    Загрузить файлы примеров (COUNT)

    1. Загрузите заархивированный образец учебного пособия по функциям подсчета.
    Заархивированный файл имеет формат xlsx и не содержит макросов.

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

    3.Загрузите книгу «Подсчет уникальных элементов в отфильтрованном списке». Заархивированный файл имеет формат xlsx и не содержит макросов.

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

    Дополнительные учебные пособия по функциям

    Список функций

    Критерии подсчета в другом столбце

    Количество отдельных элементов

    подсчитывать
    Ячейки с определенным текстом

    СРЕДНЕЕ

    СУММ / СУММЕСЛИ

    Промежуточный итог

    ,

    Как использовать функцию СЧЁТЕСЛИ в Excel

    Функция СЧЁТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне, который соответствует одному предоставленному условию. Критерии могут включать логические операторы (> ,, =) и символы подстановки (*,?) Для частичного соответствия. Критерии также могут быть основаны на значении из другой ячейки, как описано ниже.

    СЧЁТЕСЛИ находится в группе из восьми функций Excel, которые разделяют логические критерии на две части (диапазон + критерий). В результате синтаксис, используемый для построения критериев, отличается, а для COUNTIF требуется диапазона ячеек, вы не можете использовать массив.

    СЧЁТЕСЛИ поддерживает только одиночное условие . Если вам нужно применить несколько критериев, используйте функцию СЧЁТЕСЛИМН. Если вам нужно манипулировать значениями в диапазоне аргумент как часть логической проверки, см. Функции СУММПРОИЗВ и / или ФИЛЬТР.

    Базовый пример

    На листе, показанном выше, в ячейках G5, G6 и G7 используются следующие формулы:

     
     = СЧЁТЕСЛИ (D5: D12, "> 100") // подсчитываем продажи более 100
    = СЧЁТЕСЛИ (B5: B12, "jim") // count name = "jim"
    = СЧЁТЕСЛИ (C5: C12, "ca") // количество состояний = "ca" 

    Примечание СЧЁТЕСЛИ без учета регистра , «CA» и «ca» обрабатываются одинаково.

    Двойные кавычки («») в критерии

    Как правило, текстовые значения необходимо заключать в двойные кавычки («»), а числа — нет. Однако, когда логический оператор включен в число, число и оператор должны быть заключены в кавычки, как показано во втором примере ниже:

     
     = СЧЁТЕСЛИ (A1: A10,100) // считать ячейки равными 100
    = COUNTIF (A1: A10, "> 32") // подсчет ячеек больше 32
    = COUNTIF (A1: A10, "jim") // подсчет ячеек, равных "jim" 

    Значение из другой ячейки

    Значение из другой ячейки может быть включено в критерии с помощью конкатенации.В приведенном ниже примере СЧЁТЕСЛИ вернет количество значений в ячейке A1: A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текст) заключен в кавычки.

     
     = COUNTIF (A1: A10, "<" & B1) // подсчитать ячейки меньше B1 

    Не равно

    Чтобы создать критерий «не равно», используйте оператор «», заключенный в двойные кавычки («»). Например, приведенная ниже формула будет считать ячейки , не равные , до «красных» в диапазоне A1: A10:

    .

     
     = СЧЁТЕСЛИ (A1: A10, «<> красный») // не «красный» 

    Пустые ячейки

    СЧЁТЕСЛИ может подсчитывать пустые или непустые ячейки.В приведенных ниже формулах подсчитываются пустые, а не пустые ячейки в диапазоне A1: A10:

    .

     

    Даты

    Самый простой способ использовать СЧЁТЕСЛИ с датами - это указать действительную дату в другой ячейке со ссылкой на ячейку. Например, для подсчета ячеек в A1: A10, которые содержат дату, превышающую дату в B1, вы можете использовать следующую формулу:

     
     = СЧЁТЕСЛИ (A1: A10, ">" & B1) // считать даты больше, чем A1 

    Обратите внимание, что мы должны связать оператор с датой в B1.Чтобы использовать более сложные критерии даты (т.е. все даты в данном месяце или все даты между двумя датами), вам нужно переключиться на функцию СЧЁТЕСЛИМН, которая может обрабатывать несколько критериев.

    Самый безопасный способ жестко ввести дату в СЧЕТЕСЛИ - использовать функцию ДАТА. Это гарантирует, что Excel распознает дату. Для подсчета ячеек в A1: A10, которые содержат дату менее 1 апреля 2020 г., вы можете использовать формулу, подобную этой

     
     = СЧЁТЕСЛИ (A1: A10, "<" & DATE (2020,4,1)) // даты менее 1 апреля 2020 г. 

    Подстановочные знаки

    В критериях можно использовать подстановочные знаки вопросительного знака (?), Звездочки (*) или тильды (~).Знак вопроса (?) Соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, для подсчета ячеек в ячейках A1: A5, содержащих текст «яблоко» в любом месте, вы можете использовать следующую формулу:

     
     = СЧЁТЕСЛИ (A1: A5, «* яблоко *») // ячейки, содержащие «яблоко» 

    Для подсчета ячеек в A1: A5, содержащих любые 3 текстовых символа, вы можете использовать:

     
     = СЧЁТЕСЛИ (A1: A5, "???") // ячейки, содержащие любые 3 символа 

    Тильда (~) - это escape-символ, соответствующий литеральным подстановочным знакам.Например, чтобы подсчитать буквальный вопросительный знак (?), Звездочку (*) или тильду (~), добавьте тильду перед подстановочным знаком (например, ~ ?, ~ *, ~~).

    Банкноты

    • СЧЕТЕСЛИ без учета регистра. Используйте функцию EXACT для подсчета с учетом регистра.
    • СЧЁТЕСЛИ поддерживает только одно условие. Используйте функцию СЧЁТЕСЛИМН для нескольких критериев.
    • Текстовые строки в критериях должны быть заключены в двойные кавычки («»), т.е. «яблоко», «> 32», «ja *»
    • Ссылки на ячейки в критериях: , а не , заключенные в кавычки, т.е.е. "
    • Подстановочные знаки? и * можно использовать в критериях. Знак вопроса соответствует любому одному символу, а звездочка соответствует любой последовательности символов (от нуля или более).
    • Чтобы сопоставить буквальный знак вопроса или звездочку, используйте тильду (~) перед вопросительным знаком или звездочкой (т.е. ~ ?, ~ *).
    • COUNTIF требует диапазона, вы не можете заменить массив.
    • СЧЁТЕСЛИ возвращает неверные результаты, если используется для сопоставления строк длиной более 255 символов.
    • COUNTIF вернет ошибку #VALUE при обращении к другой закрытой книге.

    .

    Отправить ответ

    avatar
      Подписаться  
    Уведомление о