60620_zh-TW

liliconcom

顏色單元格中每個行中出現次數最多的值

大家好，

 

我一直在努力拼湊一些適用於此的東西。

 

我想迭代每一行並為其值最不明顯的單元格著色。例如，使用下表：

 

	RowNames	商店1	商店2	Store3	Store4	Store5
1	動物	貓	狗	貓	貓	鳥
2	成本	10	12	8	10	6

 

第1行：突出狗和鳥

第2行：突出顯示12,8和6

 

謝謝！

 

Re：為每行中出現次數最多的值著色單元格謝謝！這很棒！Re：為每行中出現次數最多的值著色單元格

好方法！

 

我確實看到了一個問題，如果不止一個選擇怎麼辦......讓我們說，貓和狗都有相同數量的出現......

 

這就是為什麼我做了分裂和組合列......

Re：為每行中出現次數最多的值著色單元格

有趣的問題。  我嘗試了一種使用轉置的不同方法，它允許我使用列操作。

dt = New Table( "example",
 Add Rows( 2 ),
 New Column( "RowNames", Character( 16 ), "Nominal", Set Values( {"Animal", "Cost"} ) ),
 New Column( "Store1", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
 New Column( "Store2", Character( 16 ), "Nominal", Set Values( {"Dog", "12"} ) ),
 New Column( "Store3", Character( 16 ), "Nominal", Set Values( {"Cat", "8"} ) ),
 New Column( "Store4", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
 New Column( "Store5", Character( 16 ), "Nominal", Set Values( {"Bird", "6"} ) )
);

dtt = dt << Transpose(
 columns( :Store1, :Store2, :Store3, :Store4, :Store5 ),
 Label( :RowNames ),
 Output Table( "Transpose of example" ),
 invisible
);

for (i = 1, i <= nrows(dt), i++,
 one_col = column(dt, "RowNames")[i];
 tab = dtt << Tabulate(
  Show Control Panel( 0 ),
  Add Table( Row Table( Grouping Columns( column(dtt, one_col) ) ) ),
  invisible
 ); 
 dtb = tab << Make Into Data Table;
 dtb << sort(by(:N), replace table(1));
 nr = nrows(dtb);
// Get the maximum counted value
 max_value = column(dtb, one_col)[nr];
// Now back to the original table - highlight everything but the max value
 for (k = 2, k <= ncols(dt), k++,
  one_value = column(dt, k)[i];
  if (one_value != max_value,
   column(dt, k) << Color Cells( "Light Yellow", i );
  );
 );
 close(dtb, nosave);
);
close(dtt, nosave);

Re：為每行中出現次數最多的值著色單元格

這是一個適用於您的示例數據表的腳本，只需要做一些工作，就可以使用您需要它處理的任何表

Names Default To Here( 1 );
dt = New Table( "example",
 Add Rows( 2 ),
 New Column( "RowNames", Character( 16 ), "Nominal", Set Values( {"Animal", "Cost"} ) ),
 New Column( "Store1", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
 New Column( "Store2", Character( 16 ), "Nominal", Set Values( {"Dog", "12"} ) ),
 New Column( "Store3", Character( 16 ), "Nominal", Set Values( {"Cat", "8"} ) ),
 New Column( "Store4", Character( 16 ), "Nominal", Set Values( {"Cat", "10"} ) ),
 New Column( "Store5", Character( 16 ), "Nominal", Set Values( {"Bird", "6"} ) )
);

// Add a new column to use as a pointer to the row 
dt << New Column( "TheRow", formula( Row() ) );
// Convert to static values for TheRow
dt:TheRow << delete formula;

// Stack the columns in question, so the processing can be done all at once
dtStack = dt << Stack(
 columns( :Store1, :Store2, :Store3, :Store4, :Store5 ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" ),
 Name( "Non-stacked columns" )(Keep( :TheRow ))
);

// Summarize the data to find the max count for each row values
dtSum = dtStack << Summary(
 Group( :TheRow, :Data ),
 Freq( "None" ),
 Weight( "None" ),
 Link to Original Data Table( 0 )
);

Close( dtStack, nosave );

// Add a new column to represent the maximums
dtSum << New Column( "TheMax",
 formula( If( :N Rows == Col Maximum( :N Rows, :TheRow ), 1, 0 ) )
);

// Delete the non max rows in the table
dtSum << select where( :TheMax == 0 );
dtSum << delete rows;

// Create a new table that has only one row for each row in the original data table
// and has a column for each maximum value found
dtSplit = dtSum << Split(
 Split By( :Data ),
 Split( :Data ),
 Group( :TheRow ),
 Remaining Columns( Drop All ),
 Sort by Column Property
);

Close( dtSum, nosave );

// Add a column that concatenates all values into one
theColumns = dtSplit << get column names;
theColumns = Remove( theColumns, 1, 1 );
dtSplit << Combine Columns(
 delimiter( "," ),
 Columns( Eval( theColumns ) ),
 Column Name( "The Maximums" )
);

// Delete the nonessential columns
dtSplit << delete columns( theColumns );

// attach the found maximumns to the original data table
dt = dt << Update( With( dtSplit ), Match Columns( :TheRow = :TheRow ) );

Close( dtSplit, nosave );

// Color the cells
For( i = 1, i <= N Rows( dt ), i++,
 theList = As List( Words( dt:The Maximums[i], "," ) );
 If( N Rows( Loc( theList, dt:Store1[i] ) ) == 0,
  dt:Store1 << Color Cells( "Red", i )
 );
 If( N Rows( Loc( theList, dt:Store2[i] ) ) == 0,
  dt:Store2 << Color Cells( "Red", i )
 );
 If( N Rows( Loc( theList, dt:Store3[i] ) ) == 0,
  dt:Store3 << Color Cells( "Red", i )
 );
 If( N Rows( Loc( theList, dt:Store4[i] ) ) == 0,
  dt:Store4 << Color Cells( "Red", i )
 );
 If( N Rows( Loc( theList, dt:Store5[i] ) ) == 0,
  dt:Store5 << Color Cells( "Red", i )
 );
);

// Cleanup
dt << delete columns( {"TheRow", "The Maximums"} );