Kopiowanie wiersza w exelu

Oprogramowanie komputerowe Problemy z oprogramowaniem
#1

Witam. Mam mały problem. Na początku wydawał mi się prosty do rozwiązania, ale przeglądałem fora i nie znalazłem odpowiedzi.

W każdej komórce jednej z kolumn mam taką formułę:

=JEŻELI(A2164=‘C:\Documents and Settings\Lenovo\Moje dokumenty\gra-com\cennik do porównania[cennik_AB.csv]cennik_AB’!A2164;"";“BŁĄD”)

Problem polega na tym, że czasem muszę 2 sąsiadujące wiersze zamienić miejscami, więc wycinam cały wiersz i wstawiam w innym miejscu. Problem polega na tym, że po wstawieniu wszystko się aktualizuje tzn. np zamiast A2164 jest A2163 ale już przy tym linku do drugiego cennika z którym porównujemy ([cennik_AB.csv]cennik_AB’!A2164) już się to nie zmienia. Doszedłem do wniosku, że A2164 nie jest jakby aktywne w tym przypadku bo jest to traktowane jak zwykły tekst - czy da się zrobić aby excel też to zmieniał, bo czasem kilkadziesiąt komórek będę tak przenosił i nie mam ochoty za każdym razem poprawiać.

A może macie jakiś inny pomysł? Liczę na podpowiedź i pozdrawiam

#2

Hej,

A czy mógłbyś jakoś lepiej wytłumaczyć, o co chodzi?? Rozumiem, że formuła sprawdza, czy ceny się zgadzają z plikiem porównawczym, a gdy jest to nieprawdą, wyświetlają tekst “BŁĄD”. Pytanie tylko po co zmieniasz wiersze?? I dlaczego przeklejasz cały wiersz?? Nie do końca też rozumiem, co Ci się nie aktualizuje… A może masz możliwość zawieszenia choć próbki pliku na jakimś serwerze, by łatwiej to zrozumieć??

Pozdrawiam,

Dimatheus

#3

Problem polega na tym, że cennik z którym będę porównywał każdego dnia sortuje produkty według nazwy a nie według indeksu, i zdarzają się takie co mają taką samą nazwę i losowo wybiera który idzie pierwszy. Ot problem. Jak źle wybierze to pojawia się błąd. Więc dlatego chcę zamieniać te wiersze miejscami a nie aktualizuje po wycięciu wiersza i wklejeniu tego miejsca [cennik_AB.csv]cennik_AB’!A2164- ono pozostaje cały czas takie samo choć to pierwsze A2164 zmienia się na - np jak przeniosłem do wiersza A2000 to jest tak:

=JEŻELI(A2000=‘C:\Documents and Settings\Lenovo\Moje dokumenty\gra-com\cennik do porównania[cennik_AB.csv]cennik_AB’!A2164;"";“BŁĄD”).

To przykład- o tyle nie będę przenosił tylko ewentualnie o 1 w górę lub dół, ale tak żeby lepiej zrozumieć. Pozdrawiam

#4

Hej,

Spoko, teraz już rozumiem w czym rzecz - najlepiej zrobić sobie coś podobnego - i znaleźć dokładnie taki sam problem… :wink:

Na dobrą sprawę aktualizuje, ale tylko w zakresie danego dokumentu. Nie aktualizuje pół, które są odnośnikami do innych plików. I niestety nie ważne, czy będzie to inny CSV czy XLS - zawsze odwołania w formułach do innych plików pozostaną bez zmian przy takim kopiowaniu, nawet gdy nie będą zablokowane kotwicami ($). Dopiero przeciągnięcie lub skopiowanie formuł przywraca poprawne odniesienia. Może więc warto posłużyć się makrem. Dokonujesz odpowiednich zmian (mowa o przeniesieniach wierszy), a później uruchamiasz makro, które kopiuje przekopiuje odpowiednie formuły, zgodnie ze wzorem

Sub Makro2()

'

' Makro2 Makro

' Makro zarejestrowane 2009-12-17, autor Dimatheus

'


'

    Range("C2").Select

    Selection.Copy

    Range("C2:C27").Select

    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

End Sub

gdzie C2 to pierwsza formuła, a C2:27 to zakres komórek, w które chcemy przekopiować daną formułę. I tyle… :slight_smile:

Oczywiście są możliwe też inne rozwiązania - z użyciem funkcji wyszukiwania pionowego lub z wykorzystaniem tabeli przestawnej. Pytanie tylko, w jaki sposób powstaje plik, z którego pobierane są dane i czy możliwe jest w nim przemieszczanie kolumn…

Pozdrawiam,

Dimatheus

#5

Dzięki za pomoc. przydało się. mam jeszcze pytanko jak zsumować miejsca w całej kolumnie gdzie wystąpił błąd bo jak nie ma takiego produktu to nie wyświetla słowa błąd tylko #N/D!. Jak zsumować takie błędy - to pierwsza sprawa. A druga jak zrobić aby to makro było nie do konkretnego wiersza tylko do ostatniego aktywnego wiersza czyli do ostatniego z jakimiś danymi - bo nie będę pamiętał aby za każdym razem poprawiać to makro jak dodam kolejne produkty. pozdrawiam i jeszcze raz dziękuję.

#6

Hej,

Nie ma sprawy. :slight_smile:

To zależy o co pytasz. Czy chcesz takie błędy przerobić (#N/D) przerobić na słowo błąd, czy po prostu zliczać ilość takich błędów. Podejrzewam, że to pierwsze. :slight_smile: Jeśli tak, to najlepiej posłużyć się formułą JEŻELI oraz CZY.BRAK. Dla uproszczenia załóżmy, że formułą, którą otrzymujesz wyniki w pliku będzie słowo FORMUŁA. By zamienić błąd #N/D na słowo błąd należy posłużyć się taką kombinacją:

=JEŻELI(CZY.BRAK(FORMUŁA)=PRAWDA;"BŁĄD";FORMUŁA)

Najłatwiej można powiedzieć, że formuła sprawdza, czy wartość z danej formuły nie jest błędem. Jeśli tak, zwraca słowo “BŁĄD”, a jeśli nie, wrzuca formułę pierwotną. Jedyne o czym trzeba pamiętać to to, by słowo PRAWDA nie było w cudzysłowie, bo to nie jest tekst, a wartość formuły. W drugim przypadku posługujemy się też wspomnianą formułą, tyle że potrzebujemy dodatkowej kolumny - wprowadzamy do niej formułę CZY.BŁĄD odwołującą się do wyniku sprawdzania danych, a następnie zliczamy wartości PRAWDA za pomocą funkcji LICZ.JEŻELI.

Tutaj będzie nieco trudniej, bo chyba musiałbym poszperać w Biblii Excel’a… :slight_smile: Ale może zrobić to za pomocą formuł. Końcową formułę, którą będzie kopiować makro nazwijmy znów FORMUŁĄ, a wiesz, w których wpisujemy dane, gdy dodajemy rekord, to na przykład A2. I uzależniamy wyświetlanie formuły od tego, czy w danym pole są dane następującą formułą:

=JEŻELI(A2<>"";FORMUŁA;"")

.

Po tej zmianie edytujemy makro na przykład do kopiowania formuły do wiersza o numerze 20 000 i już nic nie robimy. Formuła się przekopiuje, a gdy nie będzie dla niej wartości w poprzednich kolumnach, komórka będzie po prostu pusta.

Mam nadzieję, że to już rozwiąże problemu… :slight_smile: A jeśli nie, to czekam na dodatkowe pytania. :slight_smile:

Pozdrawiam,

Dimatheus

#7

blokujesz symbolem $

co do wycinania to nie lepiej po prostu przenisc wiersz nizej poprzez przeciągnięcie go?

Pozdrawiam

#8

Hej,

A co w tej całej sytuacji da kotwiczenie komórek?? Tu akurat należy się ich pozbywać, a nie wstawiać.

To niestety nic nie da, bo będą pojawiać się częściowo zdublowane komórki - w których dwie komórki w tabeli właściwej, będą odnosić się do tej samej komórki w pliku źródłowym - trzeba by więc i tak ręcznie zmieniać formuły. :frowning:

Pozdrawiam,

Dimatheus

#9

Pomogło chociaż trochę musiałem pozmieniać i teraz ta formuła wygląda tak

=JEŻELI(JEŻELI.BŁĄD(LUB(PORÓWNAJ(cennik_AB.csv!A4;WYSZUKAJ.PIONOWO(cennik_AB.csv!A4;A:A;1;FAŁSZ));FAŁSZ);(A4=0));"";“BŁĄD”)

Chodzi mi o tą drugą ewentualność bo mam inne kolumny i chciałbym łącznie wszystkie błędy obojętnie jakie będę zliczać.

Pozdrawiam

#10

Hej,

I też wszystko zależy od tego, jakie wartości się pojawiają - jeśli tylko wynikające z formuł wielkości “BŁĄD” oraz #N/D!, to formuła sumująca występowanie będzie wyglądać tak jak poniżej. Dla ułatwiania przyjmiemy, że badany zakres od A1 do B5.

=LICZ.JEŻELI(A1:B5;BRAK())+LICZ.JEŻELI(A1:B5;"BŁĄD")

.

Funkcja BRAK wrzuca do komórki - a w tym przypadku do formuły - wartość #N/D!, która wpisana ręcznie jest traktowana jako tekst. Warto o tym pamiętać.

Pozdrawiam,

Dimatheus

#11

Witam. Mam jeszcze jeden problem. Jak zrobić makro, takie aby jeśli w kolumnie A natrafi na BŁĄD (#N/D!) usunął cały wiersz. Jest to możliwe do wykonania. Pozdrawiam

#12

Hej,

Tak, jest to możliwe. Co prawda w makrach siedzę na tyle, na ile potrzebuję, ale trochę się pobawiłem… :slight_smile: Rezultatem jest poniższe makro.

Sub Usun_Wiersze_Z_ND()

  Dim i As Long

  With ActiveWorkbook.Worksheets("Usuwanie_Wierszy_Z_ND!")

    For i = .Range("A65536").End(xlUp).Row To 1 Step -1

      If IsError(.Cells(i, 1).Value) = True Then

        .Rows(i).Delete (xlShiftUp)

      End If

    Next

  End With

End Sub

W miejsce nazwy zakładki Usuwanie_Wierszy_Z_ND! wpisujesz odpowiednią nazwę zakładki - na przykład Arkusz1. Pamiętaj tylko o cudzysłowach. Makro te usuwa w danym arkuszu wszystkie wiersze, w których w kolumnie A występuję błąd braku wartości (#N/D!). Jeśli natomiast w formułach wyszukujących uwzględniłeś formułę CZY.BŁĄD i zamiast #N/D! otrzymujesz na przykład zwrot tekstowy BŁĄD, wtedy należy użyć poniższego makra.

Sub Usun_Wiersze_Z_BLAD()

  Dim i As Long

  With ActiveWorkbook.Worksheets("Usuwanie_Wierszy_Z_BLAD")

    For i = .Range("A65536").End(xlUp).Row To 1 Step -1

        If .Cells(i, 1) = "BŁĄD" Then

        .Rows(i).Delete (xlShiftUp)

      End If

    Next

  End With

End Sub

Oczywiście tutaj także należy zmienić nazwę zakładki z Usuwanie_Wierszy_Z_BLAD na taką, jaka naprawdę występuje w pliku.

Pozdrawiam,

Dimatheus

#13

super, bardzo dziękuję za pomoc

dopiero teraz odpisuję bo trochę chorowałem. Potrzebuję jeszcze raz pomocy

ponieważ okazało się że będę kopiował przez makro więcej kolumn tak jak opisałeś

więc jak można zrobić aby występowała zmienna np. x która w tym wypadku ma wartość 27

Chodzi o to, że chciałbym aby jak najmniej obciążało mi kompa dlatego nie ma sensu zrobić do 10000 ale zrobiłem do 6000 a jak przybędzie produktów to będę tylko w tej zmiennej zmieniał wartość.

A druga rzecz to jak zrobić by już po wypełnieniu komórek posortowało mi wiersze od z do a - pytam o to bo w innym arkuszu bym potrzebował i postanowiłem skorzystać z makra które podesłałeś. U mnie wygląda ono tak, a chciałbym jeszcze posortować

Jeszcze raz dziękuję za dotąd otrzymaną pomoc i pozdrawiam

Dawid

#14

Hej,

Nie ma sprawy… :slight_smile:

Dodajemy warunek, że makro ma pobierać wartość z danej komórki. W tym przypadku przyjmujemy, że jest nią komórka D1, w której wpisujemy wartość C27. Makro będzie wyglądać następująco:

Sub Kopiuj()


Range("C2").Select

Selection.Copy

i = Range("D1").Value

Range("C2", i).Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False


End Sub

W sumie w przypadku dzisiejszych komputerów takie makro, to żadne obciążenie. Ale już masz podane, jak wpisać w makro warunek z zakresem kopiowanych komórek. Co do drugiego makra to najpierw jedna uwaga. Nie ma sensu powtarzać kopiowana poprzez makra pojedynczych kolumn x-razy… Jeśli możliwe scalamy zakresy, w których działają makra. Rezultatem tego będzie zdecydowanie krótsze makro.

Range("C4").Select

Selection.Copy

Range("C4:C6000").Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False


Range("H4:N4").Select

Selection.Copy

Range("H4:N6000").Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Rozumiem, że chodzi o posortowanie całej tabeli malejąco. Jeśli tak, to na końcu makra - tego mojego powyżej - należy dodać wpis

Range("C10").Activate

    Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _

        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

        DataOption1:=xlSortNormal

Pole C10 to dowolna komórka tabeli, która w okół siebie ma wartości. Działa to podobnie jak skrót klawiszowy Ctrl+A, który zaznacza całą tabelę. Natomiast B2, oznacza według której kolumny sortujesz dane. Jeśli miałaby to być kolumna pierwsza wpisujesz wartość A2.

Pozdrawiam,

Dimatheus

#15

Witam. Czy istnieje możliwość zapisania w excelu pliku csv ale w innym kodowaniu - chodzi mi o UTF-8 bo na razie robię to tak że zapisuję normalnie (jak się zorientowałem to jest Windows-1250) a dopiero potem w OpenOffice zamieniam z 1250 na UTF-8. Chciałem sobie skrócić i zapisywać w Excelu innym kodowaniem. Z góry dziękuję

#16

Hej,

Szczerze mówiąc wydaje mi się, że to nie jest możliwe. Otwieranie plików csv - o kodowaniu innym niż domyślne - musi odbywać się poprzez import danych. Inaczej pojawiają się krzaczki i inne nieokreślone znaczki… :slight_smile: Stąd też ciężko wyszukiwać opcji eksportu do csv o innym kodowaniu…

Pozdrawiam,

Dimatheus