PDA

Vollständige Version anzeigen : Brauche Hilfe für ein Makro, Zellen transponieren, Matrix in Spalten


Michael Schmohl
07.10.2017, 14:08
Hallo zusammen,
ich brauche Hilfe für ein VBA-Makro in Excel. Vielleicht könnt ihr mir helfen, das wäre super nett.
Fall:
Ich habe eine große Tabelle als Matrix. Also für eine Zeile sind mehrere Infos in Spalten vorhanden. Diese muss aber als Spalte dargestellt werden, da sie in ein anderes System übernommen werden muss. Es müssen eigentlich die Daten transponiert werden. Aber ich brauche es sehr häufig und für große Tabellen, da ist Handarbeit sehr mühsam. Deshalb brauche ich ein Makro.

Ich habe mal eine Testdatei, die Original ist viel größer und breiter, hochgeladen.
Dort sind also links in den Zeilen mehrere Werte (30mm-60mm) und dafür existieren nebeneinander in den Spalten Maßangaben mit Preise. Diese müssen jetzt untereinander dargestellt werden.

Kann mir dabei jemand helfen?
Danke
Grüße
Michael

markusxy
07.10.2017, 14:26
Excel Fragen gehören in Excel Forum. Hier bist du im Outlook Forum.

Hajo_Zi
07.10.2017, 16:19
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11px; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:37px;" /><col style="width:37px;" /><col style="width:37px;" /></colgroup><tr style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">9</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">30</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 1/2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">5</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">10</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">30</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 3/4</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">7,5</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">11</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">30</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">8</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">12</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">30</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">10</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">13</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">30</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">15</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">14</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">40</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 1/2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">6,66666666666667</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">15</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">40</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 3/4</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">10</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">16</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">40</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">10,6666666666667</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">17</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">40</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">13,3333333333333</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">18</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">40</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">20</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">19</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">50</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 1/2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">8,33333333333333</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">20</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">50</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 3/4</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">12,5</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">21</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">50</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">13,3333333333333</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">22</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">50</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">16,6666666666667</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">23</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">50</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">25</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">24</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">60</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 1/2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">10</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">25</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">60</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; "> 3/4</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">15</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">26</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">60</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">16</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">27</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">60</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">20</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">28</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:right; ">60</td><td style="border-color:#000000; color:#FFFFFF; font-size:11px; background-color:#808080 ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; font-size:11px; background-color:#D9D9D9 ;; text-align:right; ">30</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11px; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td>Englisch</td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td><td>N/A</td></tr><tr><td>A9:A28</td><td>=INDEX($A$2:$A$5,ROUNDDOWN((ROW()-4)/5,0))</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>B9:B28</td><td>=INDEX($B$1:$F$1,,MOD((ROW()-4),5)+1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>C9:C28</td><td>=INDEX($B$2:$F$5,ROUNDDOWN((ROW()-4)/5,0),MOD((ROW()-4),5)+1)</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11px;width:800px; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td colspan="2" > Zahlenformate </td></tr><tr valign="top" style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td> Zelle </td><td> Format </td></tr><tr><td>A9:A28 </td> <td style="text-align:center; ">'0 "mm"</td></tr><tr><td>B9:B28 </td> <td style="text-align:center; ">'# ?/?</td></tr><tr><td>C9:C28 </td> <td style="text-align:center; ">'_-* #.##0,00 [$€-407]_-;-* #.##0,00 [$€-407]_-;_-* "-"?? [$€-407]_-;_-@_-</td></tr></table><b>Zellen mit Format Standard werden nicht dargestellt</b><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 25.13 einschl. 64 Bit</td></tr></table><br/>

Mit dem Hinweis, kann ich mich nur anschließen.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

Michael Schmohl
07.10.2017, 18:23
Danke für den Hinweis.
War nicht meine Absicht, weiß auch nicht wieso ich hier gelandet bin.
Sorry

Michael Schmohl
07.10.2017, 18:31
Hallo,
vielen Dank schon mal für die Antwort. Super. Jetzt noch als VBA Makro und es klappt. Ich habe die Frage nach Excel verschoben. Sorry, weiß nicht, wieso es hier gelandet ist.
Danke
Michael