Как в excel из одной таблицы перенести данные в другую по условию: Сборка данных со всех листов книги в одну таблицу

Сборка данных со всех листов книги в одну таблицу

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

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

Давайте будем исходить из следующих соображений:

• Структура и столбцов на всех листах одинаковая.
  
• Количество строк на всех листах разное.
  
• Листы могут в будущем добавляться или удаляться.

Наша задача — собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, Power Query и макросы.

Способ 1. Сборка данных с листов с помощью Power Query

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

Шаг 1. Подключаемся к файлу

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

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data). Нажмите кнопку Получить данные / Создать запрос — Из файла — Книга Excel (Get Data / New Query — From file — From Excel) и укажите наш файл с исходными листами:

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

Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit):

Должно появиться окно редактора запросов Power Query, где отобразятся данные с выбранного листа.  Поскольку нам нужен, на самом деле, не один лист, а все, то удалим в правой панели все шаги, кроме первого шага Источник (Source) используя крестик слева от названия шага:

То, что останется после удаления шагов — это список всех объектов, которые Power Query «видит» во внешних файлах, а это:

• листы (Sheet)
• «умные таблицы» (Table)
• именованные диапазоны (Defined Name)
• области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

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

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind. Например, если вам нужны только листы:

Во-вторых, если нам нужны только видимые листы, то дополнительно можно отфильтровать ещё по столбцу Hidden.

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

Добавление столбца — Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):

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

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

=[Data][Column1]{0}

Здесь:

• [Data] — имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
• [Column1] — имя столбца на листе, из которого мы хотим извлечь данные
• {0} — номер строки (считая с нуля), откуда мы хотим взять данные

После фильтрации «мусора» все добавленные вспомогательные столбцы можно, конечно же, спокойно удалить, оставив только колонки Name и Data.

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix):

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Останется лишь «навести блеск», а именно:

1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home).
2. Переименовать первый столбец в Город двойным щелчком на заголовку.
3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home — Close & Load):

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

или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl+Alt+F5.

Плюсы такого подхода:

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

Минусы этого способа:

• Собираются только значения, т.е. формулы с исходных листов не сохраняются.
  
• Названия столбцов должны на всех листах совпадать с точностью до регистра.
• Нельзя выбрать какой именно диапазон берётся с каждого листа — это определяется автоматически (берётся всё, что есть).
  
• Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более «классического» подохода — макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub CollectDataFromAllSheets()
    Dim ws As Worksheet
    
    Set wbCurrent = ActiveWorkbook
    Workbooks. Add
    Set wbReport = ActiveWorkbook
    
    'копируем на итоговый лист шапку таблицы из первого листа
    wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
    
    'проходим в цикле по всем листам исходного файла
    For Each ws In wbCurrent.Worksheets
    
        'определяем номер последней строки на текущем листе и на листе сборки
        n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        
        'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
        Set rngData = ws.Range("A1:D5")            'фиксированный диапазон или
        Set rngData = ws.UsedRange                 'всё, что есть на листе или
        Set rngData = ws.Range("F5").CurrentRegion    'область, начиная от ячейки F5 или
        Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell))    'от А2 и до конца листа
        
        'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
        rngData.  Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)
        
    Next ws
End Sub

Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или с помощью сочетания клавиш Alt+F8. Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.

Плюсы такого подхода:

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

Минусы этого способа:

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

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления — использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор — это всегда хорошо.

Ссылки по теме

 

Перемещение и копирование ячеек и их содержимого

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

Ячейки в Excel перемещаются и копируются вместе с формулами, итоговыми значениями, форматом и примечаниями.

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

Перемещение ячеек путем перетаскивания

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

2. Наведите указатель мыши на границу выделенной области.

3. Когда указатель примет вид указателя перемещения , перетащите ячейку или диапазон ячеек в другое место.

Перемещение ячеек с помощью команд «Вырезать» и «Вставить»

1. Выделите ячейку или диапазон ячеек.

2. Нажмите кнопку главная > вырежьте или нажмите клавиши CTRL + X.

3. Выделите ячейку, в которую вы хотите переместить данные.

4. Выберите главная > вставьте или нажмите клавиши CTRL + V.

Копируйте ячейки на листе с помощью команд Копировать и Вставить.

1. Выделите ячейку или диапазон ячеек.

2. Нажмите кнопку Копировать или клавиши CTRL+C.

3. Нажмите кнопку Вставить или клавиши CTRL+V.

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

   Выделение ячеек и диапазонов

2. На вкладке Главная в группе Буфер обмена выполните одно из

Транспортировка данных в Ехсеl

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

Извлекаем полезную информацию В бухгалтерских программах обычно присутствует функция экспорта данных в обычный текстовый файл. А уже оттуда, из файла экспорта, данные можно импортировать в Excel, после чего с ними можно работать в этой программе. Для того чтобы произвести экспорт данных из бухгалтерской программы в текстовый файл, нужно нажать соответствующую кнопку или выбрать нужный пункт в меню. Как эта функция работает конкретно в вашей версии программы, можно узнать из руководства пользователя, которое поставляется изготовителем, или из справочной системы самого программного продукта. В большинстве современных бухгалтерских программ схема действий при выполнении экспорта данных выглядит примерно одинаково. Первым делом нужно отобрать в бухгалтерской программе документы, которые мы хотим перенести в другой файл. После того как сформируем их список, жмем на соответствующую кнопку или выбираем подходящий пункт меню для осуществления экспорта данных. Обычно программа перед началом процедуры переноса данных требует задать имя файла, в который будут сохранены выбранные документы, и указать папку для его сохранения. Результатом экспорта обычно бывает файл с расширением *.txt, содержащий в себе данные из бухгалтерской программы. Встречаются и другие расширения, но значительно реже. Переносим данные в Excel Если у вас нет необходимости переносить данные в Excel постоянно, можно использовать способ непосредственного открытия нужных файлов в среде Excel. Для этого сперва открываем программу Excel и жмем на кнопку открытия файла. В появившемся окошке в строке для типа файла выбираем параметр «Все файлы». А после этого ищем папку с файлом, который содержит данные из бухгалтерской программы, выделяем его курсором и нажимаем кнопку «Открыть». Если вдруг вы не видите нужного документа в папке, где он хранится, то скорее всего вы забыли выбрать пункт «Все файлы», и Excel его просто «не видит». Далее появится окно мастера по импорту данных из файла на лист Excel. В «Мастере текстов» имеются необходимые настройки и параметры, которые позволяют легко добиться хорошего результата. Нужно лишь следовать подсказкам мастера. Обычно импорт файлов производится в три шага. Для удобства пользователя на каждом из них есть окно для предварительного просмотра результатов изменений. Шаг первый. Здесь мы должны задать документу первоначальное форматирование, то есть определить, каким именно образом информация будет размещена по столбцам. Сперва в окне мастера в разделе «Формат файла» нужно выбрать тип используемой кодировки, чтобы текст нормально читался. Если в окне просмотра появляются какие-то непонятные слова или символы, попробуем выбрать для формата файла другой параметр. Потом указываем, с какой строки требуется производить импорт данных. Например, это необходимо, если в первой строке содержится техническое описание столбцов, которое нам не нужно. Когда все готово, нажимаем кнопку «Далее» для перехода к следующему шагу. Шаг второй. На этом этапе нам нужно определить, правильно ли был сделан выбор типа данных. Если в окне, где отображен результат, информация неправильно разбита по столбцам, возвращаемся назад и выбираем другой тип данных. При первом шаге у нас была возможность выбрать для формата данных значение «фиксированной ширины». Если мы установили галочку напротив этого пункта, то на втором шаге потребуется задать границы каждому столбцу. Сделать это можно на линейке в окне «Образец разбора данных». Символ разделителя столбцов пользователь выбирает самостоятельно. Какой именно знак установлен в данный момент, можно увидеть в табличке «Символом-разделителем является:». Если символ выбран неправильно, разбивка по столбцам будет происходить некорректно. Тогда следует подобрать или ввести другой символ-разделитель. Когда все станет нормально, жмем на кнопку «Далее» и переходим к последнему шагу. Шаг третий. В этот момент мы можем сразу исключить из таблицы ненужные столбцы, которые содержат лишнюю информацию. Для каждого из оставшихся столбцов укажем формат данных, который будет учитываться при создании листа в Excel. Будьте внимательны: при изменении формата происходит переформатирование данных, и это может привести к их искажению. Определившись со всеми столбцами, нажимаем на кнопку «Готово». После импорта полученную информацию нужно сразу сохранить. При этом обязательно выберите тип файла «Книга Microsoft Excel». А уже после полученные данные можно отформатировать на свой вкус. Если перенос данных прошел не совсем удачно, расстраиваться не стоит – процедуру импорта можно повторить еще раз. Но в мастере текстов попробуйте подобрать другие настройки и параметры. Регулярно обновляем файл Бывают случаи, когда нужно с некоторой периодичностью импортировать новые данные из специализированной бухгалтерской программы в файл Microsoft Excel, то есть регулярно его обновлять. Процедура такого перемещения очень похожа на вышеописанную. Сперва мы также производим экспорт данных из бухгалтерской программы в файл. А потом в меню программы Excel выбираем пункт «Данные», затем подпункт «Внешние данные», и далее – «Импорт текстового файла…». В результате на экране появится окно импорта текстового файла, где вместо кнопки «Открыть» будет «Импорт». Выбираем документ, из которого будем брать информацию, и после этого повторяем три вышеописанных шага. Завершаем перенос данных выбором листа, куда следует поместить данные. В начале таблицы, в которую мы импортировали данные из бухгалтерской программы, можно создать новую строчку с описанием столбцов. А слева добавить столбец для нумерации строк. И в последующем они останутся неизменными, а все обновляемые данные будут размещаться за ними. Этот способ имеет свои преимущества. Если данные из бухгалтерской программы нужно постоянно экспортировать в один и тот же файл, то его обновление займет значительно меньше времени. Это возможно благодаря тому, что все шаги мастера не придется каждый раз повторять вручную, программа выполнит их самостоятельно. Обновить данные из бухгалтерской программы в уже имеющемся файле Excel еще проще. Для этого открываем книгу Microsoft Excel, в которой была сохранена информация из текстового файла, и ставим курсор в любую ячейку с перенесенными данными. Потом в меню Excel выбираем пункт «Данные», а затем – «Обновить данные». После этого появится окно для открытия файла, в котором уже указано имя документа, из которого вы брали информацию в прошлый раз. Остается лишь нажать кнопку «Импорт» – и можно приступать к обработке обновленных данных. Когда вы работаете с постоянно обновляемым файлом, не меняйте местами и не добавляйте новые столбцы между уже имеющимися. Это связано с тем, что при последующем импорте данных информация будет размещена в обычном порядке, и в результате данные в таблице будут смещены.

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

Александр ПОПОВ

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

Вы когда-нибудь использовали ВПР для переноса столбца из одной таблицы в другую? Теперь, когда Excel имеет встроенную модель данных, функция VLOOKUP устарела. Вы можете создать связь между двумя таблицами данных на основе совпадающих данных в каждой таблице. Затем вы можете создавать листы Power View и строить сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете импортировать и связать данные анализа времени для анализа моделей продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

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

org/ItemList»>

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

2. Выполните одно из следующих действий: Отформатируйте данные как таблицу или Импортируйте внешние данные как таблицу на новом листе.

3. Дайте каждой таблице осмысленное имя: В Инструменты для таблиц щелкните Дизайн > Имя таблицы > введите имя.

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

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

5. Щелкните Данные > Взаимосвязи .

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

1. org/ListItem»>

   В поле Управление взаимосвязями щелкните Новый .

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

3. Для столбца (внешний) выберите столбец, содержащий данные, связанные с Связанный столбец (основной) . Например, если у вас есть столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.

4. org/ListItem»>

   Для Связанная таблица выберите таблицу, в которой есть хотя бы один столбец данных, связанный с таблицей, которую вы только что выбрали для Таблица .

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

6. Щелкните ОК .

Подробнее о связях между таблицами в Excel

Заметки об отношениях

Пример: связь данных времени с данными о рейсах авиакомпаний

«Могут потребоваться связи между таблицами»

Шаг 1. Определите, какие таблицы указать в отношении

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

Заметки об отношениях

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

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

Таблицы данных в Excel — Easy Excel Tutorial

Одна таблица переменных данных | Таблица двух переменных

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

Предположим, у вас есть книжный магазин и у вас есть 100 книг на складе. Вы продаете определенный процент по максимальной цене 50 долларов и определенный процент по более низкой цене 20 долларов. Если вы продаете 60% по самой высокой цене, в ячейке D10 ниже рассчитывается общая прибыль 60 * 50 + 40 * 20 долларов = 3800 долларов.

Таблица с одной переменной

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

1. Выберите ячейку B12 и введите = D10 (см. Ячейку общей прибыли).

2. Введите различные проценты в столбец A.

3. Выберите диапазон A12: B17.

Мы рассчитаем общую прибыль, если вы продадите 60% по самой высокой цене, 70% по самой высокой цене и т. Д.

4. На вкладке «Данные» в группе «Прогноз» щелкните «Анализ возможных вариантов».

5.Щелкните Таблица данных.

6. Щелкните поле «Ячейка ввода столбца» (проценты указаны в столбце) и выберите ячейку C4.

Мы выбираем ячейку C4, потому что проценты относятся к ячейке C4 (% продано по самой высокой цене). Вместе с формулой в ячейке B12 Excel теперь знает, что он должен заменить ячейку C4 на 60% для расчета общей прибыли, заменить ячейку C4 на 70% для расчета общей прибыли и т. Д.

Примечание: это таблица данных с одной переменной, поэтому мы оставляем поле ввода Row пустой.

7. Щелкните ОК.

Результат.

Вывод: если вы продаете 60% по самой высокой цене, вы получаете общую прибыль в размере 3800 долларов США, если вы продаете 70% по максимальной цене, вы получаете общую прибыль в размере 4100 долларов США и т. Д.

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

Таблица двух переменных данных

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

1. Выберите ячейку A12 и введите = D10 (см. Ячейку общей прибыли).

2. Введите различную прибыль (наивысшую цену) в строке 12.

3. Введите различные проценты в столбец A.

4. Выберите диапазон A12: D17.

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

5. На вкладке «Данные» в группе «Прогноз» щелкните «Анализ возможных вариантов».

6. Щелкните Таблица данных.

7. Щелкните поле «Ячейка ввода строки» (единицы прибыли указаны в строке) и выберите ячейку D7.

8. Щелкните поле «Ячейка ввода столбца» (проценты указаны в столбце) и выберите ячейку C4.

Мы выбираем ячейку D7, потому что единичная прибыль относится к ячейке D7.Мы выбираем ячейку C4, потому что проценты относятся к ячейке C4. Вместе с формулой в ячейке A12 Excel теперь знает, что он должен заменить ячейку D7 на 50 долларов и ячейку C4 на 60% для расчета общей прибыли, заменить ячейку D7 на 50 долларов и ячейку C4 на 70% для расчета общей прибыли и т. Д.

9. Щелкните OK.

Результат.

Вывод: если вы продаете 60% по наивысшей цене, с прибылью на единицу в 50 долларов, вы получаете общую прибыль в 3800 долларов, если вы продаете 80% по самой высокой цене, с прибылью на единицу в 60 долларов, вы получаете общую прибыль 5200 долларов США и т. д.

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

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

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

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

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

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

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

 
 = СЧЁТЕСЛИ (D5: D12, "> 100") // подсчитываем продажи более 100
= СЧЁТЕСЛИ (B5: B12, "jim") // count name = "jim"
= COUNTIF (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, вы можете использовать следующую формулу:

 
 = COUNTIF (A1: A10, ">" & B1) // считать даты больше, чем A1 

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

Самый безопасный способ жестко запрограммировать дату в СЧЁТЕСЛИ - использовать функцию ДАТА. Это гарантирует, что 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 символов.
• СЧЁТЕСЛИ вернет ошибку # ЗНАЧ при обращении к другой закрытой книге.

Условное форматирование на основе близости даты (Microsoft Excel)

Обратите внимание: Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003. Если вы используете более позднюю версию (Excel 2007 или новее), этот совет может не подойти вам .Чтобы посмотреть версию этого совета, написанную специально для более поздних версий Excel, щелкните здесь: Условное форматирование на основе близости даты.

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

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

1. Выберите ячейку A1.
2. Выберите «Условное форматирование» в меню «Формат». Excel отображает диалоговое окно «Условное форматирование».(См. Рисунок 1.)

Рисунок 1. Диалоговое окно «Условное форматирование».

3. Задайте условие 1, чтобы значение ячейки было равно = СЕГОДНЯ (). Обратите внимание, что в крайнем правом поле необходимо ввести знак равенства, за которым следует функция СЕГОДНЯ ().
4. Нажмите кнопку «Форматировать». Excel отображает диалоговое окно «Формат ячеек».
5. Убедитесь, что выбрана вкладка «Узоры». (См. Рисунок 2.)

Рисунок 2. Вкладка "Шаблоны" диалогового окна "Формат ячеек".

6. Выберите красный цвет, который вы хотите использовать, и закройте диалоговое окно «Формат ячеек».
7. Щелкните кнопку Добавить.
8. Задайте условие 2 так, чтобы значение ячейки находилось между = СЕГОДНЯ () - 7 и = СЕГОДНЯ () + 7. Обратите внимание, что вы должны использовать знаки равенства в двух крайних правых полях, иначе Excel не сможет определить, что вы вводите формулу.
9. Нажмите кнопку «Форматировать». Excel отображает диалоговое окно «Формат ячеек».
10. Убедитесь, что выбрана вкладка «Узоры».
11. Выберите желтый цвет, который вы хотите использовать, и закройте диалоговое окно «Формат ячеек».
12. Щелкните кнопку Добавить.
13. Задайте условие 3 так, чтобы значение ячейки было между = СЕГОДНЯ () - 14 и = СЕГОДНЯ () + 14. Опять же, вы должны включить знаки равенства, чтобы указать, что вы вводите формулы.
14. Нажмите кнопку «Форматировать». Excel отображает диалоговое окно «Формат ячеек».
15. Убедитесь, что выбрана вкладка «Узоры».
16. Выберите зеленый цвет, который вы хотите использовать, и закройте диалоговое окно «Формат ячеек».
17. Щелкните ОК.

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

ExcelTips - ваш источник экономичного обучения Microsoft Excel. Этот совет (2664) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Условное форматирование на основе приближения даты .

Автор Биография

Аллен Вятт

Аллен Вятт - всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen ...

Контроль за нанесением пустых ячеек

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

Узнать больше

Поиск текста без определенного стиля

Word очень гибок в том, что позволяет искать. Однако одна вещь, которую он не может сделать, - это позволить вам искать ...

Узнать больше

Соответствие форматирования при объединении

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

Узнать больше

Сортировка или фильтрация результатов условного формата

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

Узнать больше

Условные форматы, различающие пробелы и нули

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

Узнать больше

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

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

Узнать больше .