Подсчет портов захода с помощью сводной таблицы (Microsoft Excel)

У Датча есть рабочий лист, который он использует, чтобы отслеживать все круизы, которые он совершил.

Каждая строка — это один круиз. В левых столбцах указано название судна, дата отправления, номер бронирования и т. Д. Десять крайних правых столбцов помечены как «Порт 1» — «Порт 10» для записи посещенных портов по порядку. Нассау, например, может быть первой остановкой в ​​одном круизе и третьей остановкой в ​​другом. Датч не может придумать простой и понятный способ использования сводной таблицы (как он это делает, например, чтобы увидеть, сколько раз он курсировал на определенном корабле и т. Д.), Чтобы узнать, сколько раз он посетил каждый порт. Он задается вопросом, можно ли использовать сводную таблицу для подсчета количества раз, когда каждое уникальное значение появляется в диапазоне ячеек, а не в одной строке или столбце.

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

Это можно сделать, выбрав ячейки, содержащие порты, и присвоив этому диапазону имя, например «Порты». (О том, как настроить именованный диапазон, рассказывалось в других советах ExcelTips.) Затем вы можете настроить список всех портов. Это может быть, например, помещено на тот же рабочий лист, который использует голландский, или это может быть на другом рабочем листе в той же книге.

Скажем, например, что имена портов находятся в столбце A, начиная с подробных данных в строке 22. Справа от портов в столбце B вы можете добавить простую формулу COUNTIFS, которая ссылается на созданный вами именованный диапазон:

Скопируйте это количество ячеек, сколько необходимо, и у вас будет подсчет количества посещений каждого порта. (См. Рис. 1.)

Рисунок 1. Получение количества портов

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

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

Рис. 2. Получение количества портов

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13625) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.