[решено] Excel: создать одномерный график/таблицу посещений по заданным данным

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

Excel: создать одномерный график/таблицу посещений по заданным данным

Есть генерируемый текстовый файл вида
Иванов 01.09 02.09 03.09
Петров 03.09 04.09 05.09
Сидоров 02.09 03.09 06.09

Чем в Экселе из него сделать график с засечками на единой временнОй оси
Или табличку:

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

Цитата:

Цитата afj

текстовый файл вида
Иванов 01.09 »

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

Цитата:

Цитата afj

Или табличку: »

Если без использования VBA, то примерно как в приложенном файле:
Содержимое файла можно скопировать "ручками" и просто вставить в первый столбец таблицы.
чтобы каждый раз не менять месяц - номер месяца меняется в А1.
Все остальное - формулами. Если необходимо получить более автоматизированное решение - приложите пример реальных файлов.

Так понимаю, простого решения нет, встроенными графиками визуализировать не получится, только ручками:(

Имена (точнее идентификаторы на их основе) на более раннем этапе используются как имя файла, так что дубликатов имён нет.
А вот дубликаты дат (более одной отметки в день на человека) быть могут.

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

Кусок реального файла (там ещё и время, но это настраиваемо, для данной визуализации время не нужно, достаточно даты):

Цитата:

Цитата afj

Так понимаю, простого решения нет, встроенными графиками визуализировать не получится »

Нет ни решения, а постановки задачи: как должен выглядеть результат с графиком. У вас 5.09.2021 например посещение 39 людей. Как их отобразить на графике? 39 кривых - так это для восприятия слишком много? Или сумма плюсиков за день нужна? Как вы себе этот график представляете?

Цитата:

Цитата afj

А вот дубликаты дат (более одной отметки в день на человека) быть могут. »

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

Цитата:

Цитата afj

Про помесячность сказано ничего не было, диапазон дат может быть любым »

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

Цитата:

Цитата afj

лимиты единой временнОй оси хорошо бы рассчитывались динамически от входных данных. »

Тут возможно VBA и не понадобится, но остается вопрос - какой график должен получится на выходе.

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

ЗЫ Изначально формулировка была краткой и общей в расчёте на ответ вида "Это типовая задача, в экселе давным-давно есть начиная с версии ххх, волшебная кнопка вооот здесь, жамкай и она всё сама сделает". (Уж в построении графиков Эксель дока)
А также в расчёте на то, что это _нашим_ данным придётся подстраиваться под существующую встроенную функцию.
Если же пилить вручную, понятно что ТЗ необходимо уточнять.

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

Графический график возможно воспринимался бы иначе, но псевдографикой - много визуального шума, иксами/плюсами воспринималось лучше.

Авторасчёт лимитов шкалы времени возможен?

Powershell

Код:

$folder = "D:\temp\visit"

$sourceTxt = "$folder\visit-data.txt"

$mark = "+" # метка визита в файлах



$csv = (gc "$sourceTxt") -match "\.bmp" -replace "\.bmp= ",";" -replace ",\d+:\d+:\d+" |ConvertFrom-Csv -Header name,dates -Delimiter ';'

$dates = $csv.dates -split ' ' |sort {$_ -as [datetime]} -Unique

$startDate = $dates[0] -as [datetime]

$day = $startDate

$finishDate = $dates[-1] -as [datetime]

$datesRange = do {$day; $day = $day.adddays(1)} until ($day -gt $finishDate)

$datesRangeString = "$($startDate.ToShortDateString())-$($finishDate.ToShortDateString())"

$reportCsv = "$folder\report $datesRangeString.csv"



"Name;" + ($datesRange.toshortdatestring() -join ';') > "$reportCsv"

$csv |% {

$_.name + ';' + ((diff $datesRange ($_.dates -split ' ' |sort -Unique |% {$_ -as [datetime]}) -IncludeEqual |sort Inputobject).sideindicator -replace '<=',';' -replace '==',"$mark;" -join '') >> "$reportCsv"

}



# Если установлен модуль ImportExcel (https://www.powershellgallery.com/packages/ImportExcel)

if (Get-Module ImportExcel -ListAvailable) {

$reportXlsx = $reportCsv -replace 'csv$','xlsx'

    if (Test-Path "$reportXlsx") {rm "$reportXlsx"}

$condText = New-ConditionalText $mark green lightgreen

Import-Csv "$reportCsv" -Delimiter ';' |export-Excel -Path "$reportXlsx" -WorksheetName $datesRangeString -ConditionalText $condText -AutoSize -FreezeTopRow -AutoFilter -BoldTopRow

}

Генерирует CSV, а если установлен модуль ImportExcel, то добавляет и файл .xlsx (см. вложение).