Write table in Excel with percentages and decimals.
7 次查看(过去 30 天)
显示 更早的评论
Good morning everyone,
I'm writing my first "question" in "Matlab Answers".
I'm using Matlab and the problem is the following:
I have few tables that I need to write on a spreadsheet in Excel, but they are supposed to have different kind of datas: some of them are supposed to be "transformed in percentages", while others just need to be cut of unnecessary decimals (say setting a precision of 4 decimals after the comma).
This is the data I have to print on a table:
0.0022835 -0.0034148 -0.00056562
-0.033729 -0.023028 -0.010711
0.017093 0.015746 0.011102
0.071375 -0.10197 -0.017716
0.07079 -0.10586 -0.017534
0.010817 0.0099986 0.0047263
1.1754 -1.9015 -0.66633
1.3995 -2.6692 -0.93321
I need to transform the first six rows into percentages and just print the other ones, e.g. needs to be displayed as , and so on. I know that in Excel it is sufficient to select a cell and convert it into a percentage: Excel multiplies the number inside the cell and attach it to the percentage symbol. However I don't know how to make Matlab tell Excel to do it automatically, e.g. with a command like this:
writetable(myTable, filePath, 'Format', 'Percentage') % --> I'm just guessing how would a command like this look like
So I worked it around and transformed it in a cell array and worked on the single rows:
% (1) Translate cells in strings, stick the percentage symbol to them
statsCell_tmp(1:6,:) = cellfun(@(x) num2str(100*x, '% .2f%%'), statsCell_tmp(1:6, :), 'UniformOutput', 0);
% (2) expose just few decimals
statsCell_tmp(7, :) = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(7, :), 'UniformOutput', 0);
statsCell_tmp(8, :) = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(8, :), 'UniformOutput', 0);
and the result that I get in my workspace is this, after using cell2table(statsCell_tmp):
'0.23%' '-0.34%' '-0.06%'
'-3.37%' '-2.30%' '-1.07%'
'1.71%' '1.57%' '1.11%'
'7.14%' '-10.20%' '-1.77%'
'7.08%' '-10.59%' '-1.75%'
'1.08%' '1.00%' '0.47%'
'1.1754' '-1.9015' '-0.6663'
'1.3995' '-2.6692' '-0.9332'
However, when I use writetable I get a very disappointing result on Excel:
As you can see, the last two lines have the exact same digits of their Matlab-display version, but the "." has been moved.
In a nutshell, "writetable" is not preserving the shape of the datas inside the table. Also, if I avoid to transform the last two rows in string, they're printed with the correct decimals:
... however, I am required to use the same notations for decimals (and to do it automatically), so even this solution is not working; besides, it has already been pointed outit has already been pointed out that it's not possible to use the comma as a decimal separator in Matlab.
In a nutshell: how am I supposed to come up with a unique (and correct) notation for these tables?
Thank you for your time and patience.
0 个评论
回答(1 个)
Rohan Patidar
2019-3-22
Hi Eri,
I understand you are getting discrepancy when you load matlab saved file into excel. Specially the last 2 rows where the position of decimal is not as expected.
I tried running the above code with your data:
statsCell_tmp = {
0.0022835, -0.0034148, -0.00056562;
-0.033729, -0.023028, -0.010711 ;
0.017093, 0.015746, 0.011102 ;
0.071375, -0.10197, -0.017716 ;
0.07079, -0.10586, -0.017534 ;
0.010817, 0.0099986, 0.0047263 ;
1.1754, -1.9015, -0.66633 ;
1.3995, -2.6692, -0.93321 };
statsCell_tmp(1:6,:) = cellfun(@(x) num2str(100*x, '% .2f%%'), statsCell_tmp(1:6, :), 'UniformOutput', 0);
statsCell_tmp(7, :) = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(7, :), 'UniformOutput', 0);
statsCell_tmp(8, :) = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(8, :), 'UniformOutput', 0);
% This saves data to Test.csv
writetable(cell2table(statsCell_tmp), 'Test.csv');
On opening the Test.csv file, I am able to see the expected result that is
1.1754
1.3995 ..
Is this what you were looking for?
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!