Print side | Luk vindue

Hjælp til opslag

Udskrevet fra: Dansk Regneark Forum
Kategori: Hjælp til regneark.
Forum navn: Formler
Forum beskrivelse: Hjælp til formler i regneark og/eller brugen af disse.
Web-adresse: https://forum.excel-regneark.dk/forum_posts.asp?TID=455
Udskrevet den: 25.Maj.2024 kl. 00:39


Emne: Hjælp til opslag
Besked fra: peppe
Emne: Hjælp til opslag
Posteringsdato: 08.Sep.2011 kl. 19:43
Hej igen herinde
Jeg får brug for hjælp til at slå nogle værdier op.
Det kan virke kompliceret at forklare hvad jeg ønsker, men vedhæfter gerne et eksempel, hvis
det kan gøre det mere forståeligt   :-) Big smile

I en projektmappe har jeg et antal faner hver med en tabel som jeg skal hente værdier fra.

Jeg har også en fane, hvor jeg skal bruge den opslåede værdi (lad os kalde det dataark).
 
Tabellerne i de enkelte faner er navngivet efter dato på følgende måde:
"list0911" (for september 2011)
"list0811" (for august 2011)
"list0711" (for juli 2011)
"list0611" (for juni 2011
Og så videre...
 
Alle tabeller er sorteret efter første kolonne i forhold til et entydigt ID-nr fx 3000, 3001, 3002, 3003 osv.
På dataarket ønsker jeg at slå værdien op, der står ud for det entydige ID-nr.
 
Udfordringen er at det enkelte ID-nr måske kun findes i list0711 og så list0411 og list0111 osv.
På dataarket skal jeg have værdien for det enkelte ID-nr de sidste 5 gange det er tastet ind -
som nævnt fra fx list0711, list0411, list0111 osv. men ved ikke hvilken tabel det er tastet ind.
 
Hvad ønsker jeg Excel gør for mig:
- Jeg taster ID-nr ind på dataarket
- Excel kigger på datoen som står på datoarket (=I DAG()) fx 08-09-2011
- Excel kigger i seneste liste fx list0911 og returnerer værdien, hvis den findes
- Excel kigger næste liste (list0811) og returnerer værdien, hvis den findes
- Dette fortsætter indtil Excel har fundet de sidste 5 værdier der er indtastet.
 
Jeg har stor tiltro til at eksperterne herinde kan løse denne lille opgave...Clap
 
Peppe


Svar:
Besked fra: rassten
Posteringsdato: 10.Sep.2011 kl. 13:34
Hej
Se vedhæftet eksempel, som vist nok tager udgangspunkt i et tidligere spørgsmål.

uploads/107/Opslag_i_liste-2.xlsx - uploads/107/Opslag_i_liste-2.xlsx

Kolonnerne M,N og O er nødvendige mellem regninger, disse kan selvfølgelig gemmes. De fejl meldinger som er i kolonne M og O skal du ikke prøve at rette, da de er nødvendige for lopslag. I kolonne N skal du selv skrive hvis der kommer nye navngivne områder.




-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 10.Sep.2011 kl. 17:58
Hej Rassten
 
Ja du har fuldstændigt ret. Jeg har lavet et indlæg tidligere, hvor du ligeledes har hjulpet mig en del videre.
Sidste gang skulle jeg bruge værdier fra alle lister  12 mdr. tilbage.
Denne gang skal jeg kun bruge de lister, hvor der er tal for det enkelte ID-nr. Derfor lavede jeg et nyt indlæg.
 
Igen har det vist sig at det med opslag er noget du har helt styr på. Tak for hjælpen Rassten.
Der er dog en lille detalje du sikkert også kan løse:
 
Det skyldes faktisk en mangel i beskrivelsen, så beklager...
 
Egentlig skal jeg ikke bruge selve opslaget men differensen mellem de enkelte opslag...
Eksempel:
ID-nr findes i list0711, list0411, list1110, list0610 mv.
Jeg  skal bruge de enkelte lister trukket fra hinanden fx list0711-list0411 og herefter list0411-list1110 og
herefter list1110-list0610 og så videre indtil jeg har de sidste 5 differencer.
 
Samtidig skal datoen (altså måneden) angives i to kolonner i forhold til de to tal der er trukket fra hinanden.
I eksemplet ovenfor vil det så være fra April 2011 til Juli 2011 og herefter fra november 2010 til April 2011 og
herefter fra Juni 2010 til November 2010 og så videre...
 
Kan det fixes ???
 
På forhånd tak
 
Jeg bukker og skraber og siger tak  Smile
 
Peder

Besked fra: rassten
Posteringsdato: 11.Sep.2011 kl. 02:09
Hej
Nu hvor du skriver det, kan jeg godt huske at det var et eksempel fra dig.

uploads/107/Opslag_i_liste-3.xlsx - uploads/107/Opslag_i_liste-3.xlsx

Prøv at se vedhæftet, jeg håber at det noget i den stil du har brug for.

Det er vigtigt at listen med navngivne områder i kolonne N, er sorteret med ældste øverst




-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 11.Sep.2011 kl. 04:22
Tak tak og tak igen  Wink
 
Så blev det vidst sat på plads.
Det virker bare helt perfekt - der er ikke et øje tørt.
 
Ja jeg ser det må være nødvendigt at sortere listen således, så det har jeg naturligvis fortsat ifølge dit eksempel.
 
Herfra med stor taknemmelighed får du lige en klapsalve ClapClapClap
 
Peder

Besked fra: peppe
Posteringsdato: 12.Sep.2011 kl. 18:40
Hej igen
 
Lidt i forlængelse af det tidligere spørgsmål, så vil jeg høre om følgende:
 
Hvordan søger man i en tabel, hvor tre kriterier skal være opfyldt før Excel returnerer et ID-nr?
Eksempel:
Hvis man angiver adresse, nr. og Litra, hvordan får man Excel til at søge på de tre kriterier tilsammen og
returnere ID-nr?
 
PS. tabellen er sorteret efter ID-nr og dette skal helst ikke ændres.
Det betyder samtidigt at adresse mv. ikke er sorteret.
 
Venligst
Peder

Besked fra: rassten
Posteringsdato: 12.Sep.2011 kl. 18:58
Umiddelbart kan jeg komme i tanke om 2 forskellige metoder.
1 bruge "Avanceret" filter, findes på fanen "Data"
2. Lave en ny kolonne i data, sammen træk adresse , nr og litre for alle med ID-nr. Et andet sted på samme ark, sammentræk adresse, nr og litre, på den som ikke du mangler ID-nr. på. Så brug en indeks - sammenlign formel.
Begge virker dog kun hvis data er sat op på et ark, og ikke spredt ud over flere forskellige ark


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: rassten
Posteringsdato: 12.Sep.2011 kl. 19:35
Eksempel
uploads/107/Find_id.xlsx - uploads/107/Find_id.xlsx
 
Formel på arket "indeks-sammenlign" forklarer næsten sig selv.
Arket "Avanceret-filter", markere en celle i data området, vælg avanceret på fanen "Data",markere listeområdet, markere kriterieområdet (bemærk overskrifterne skal være med i områderne, samt ID også de rækker er tomme under kriterie området, da det er disse du vil finde), bestem om resultatet skal være lokalt eller et andet sted på arket.
Avanceret filter er et godt redskab at kende til. Det kan blandt andet give en unik liste, og man kan bruge kriterie området som OG eller "ELLER" filter.
 


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 12.Sep.2011 kl. 20:05
Tak
 
Avanceret filter er jeg ikke sikker på helt forstår.
 
I begge tilfælde: hvordan sikrer man at Excel er "ligeglad" med om det er store eller små bogstaver?
 
Peder

Besked fra: rassten
Posteringsdato: 12.Sep.2011 kl. 20:24
Øh-ikke sikker på jeg helt forstår
indeks-sammenlign er i mit eksempel ligeglad med sToRe elle LilLe bogstaver.
Det samme er avanceret filter.
uploads/107/Find_id-2.xlsx - uploads/107/Find_id-2.xlsx
 
Avanceret filter har endvidre den fordel at listeområdet og kriterieområdet kolonnerne ikke behøver at stå i samme rækkefølge.
Ved godt at avanceret filter kan være lidt meget svært at forstå, men en google søgning på"avanceret filter excel" vil give en del forskellige gode resultater. 


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 12.Sep.2011 kl. 20:26
Avanceret filter er efter lidt testforsøg egentlig meget smart, men...
Hvis man nu som i dit eksempel angiver tre kriterier, kan man så lave en knap ved siden af til at trykke på der
automatisk udfører den avancerede filtrering og skriver resultatet (ID-nr) ?
 
Det kunne være for smart...
 
Blot for at gøre det så brugervenligt som muligt?
Peder

Besked fra: rassten
Posteringsdato: 12.Sep.2011 kl. 20:37
Selvfølgelig kan man det, prøv at se:
 
http://www.youtube.com/watch?v=tw6x2Ljarc4 - http://www.youtube.com/watch?v=tw6x2Ljarc4
 
hvis linket ikke virker, gå til youtube søg efter: dueling excel+1420


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 12.Sep.2011 kl. 20:37
Ja det kan jeg se, hvis jeg ellers kunne åbne øjnene  Ouch
 
Begge metoder er simpelthen så logiske som kan være - super.
To spørgsmål til eksperten:
 
1. kan man lave en knap, der automatisk laver filtreringen, som jeg ønsker
(ingen brugerformular behøver komme op).
 
2. hvad menes der med "kun unikke poster" under avanceret filter?
 
 
Venligst
Peder

Besked fra: peppe
Posteringsdato: 12.Sep.2011 kl. 20:50
HOV HOV - hvad skete der lige der - de to gutter duelerer rigtig nok - DAMN de er for vilde.
 
Tror lige jeg lader en ekspert med begge ben på jorden (dig) lader forklare det...
 
Tror du jeg kan få dig til det (duelmester)  Smile
 
 
 
 

Besked fra: rassten
Posteringsdato: 12.Sep.2011 kl. 20:54
@2:
Lille opgave:
uploads/107/Find_id-3.xlsx - uploads/107/Find_id-3.xlsx
 
Opgave i kolonne B har du samtlige køber, problem jeg vil gerne have en liste over alle de som køber(altså bruger navn).
Men der er over 3000 rækker, som du så bør gå igennem for at være sikker på at du har fundet alle de forskellige brugerer som kan optræde.
 
Løsning: markere celle B1, vælg avanceret filter, under handling, vælg kopiere til andet sted, listeområdet vælg 'Unikke-brugere'!$B:$B , eller blot vælg hele Kolonne B, vælg kopier til 'Unikke-brugere'!$R$1 eller vælg blot celle R1, markere Kun unikke poster. Tryk OK
 


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: rassten
Posteringsdato: 12.Sep.2011 kl. 21:03
@1 prøver jeg gerne, men skal desværre først have noget at spise.
Vender tilbage

-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: rassten
Posteringsdato: 13.Sep.2011 kl. 00:02
Prøv at se dette eksempel
uploads/107/Find_id-41.xlsm - uploads/107/Find_id-41.xlsm
 
Da jeg ikke ved noget om størrelsen på dit data sæt, har jeg lavet nogle variabler og nogle steder som du selv må lave om hvis resultatet skal stå andet sted på arket
 
Sub a_test()
Range("U1").CurrentRegion.ClearContents
fr1 = Cells(Rows.Count, 1).End(xlUp).Row 'hvor mange rækker er der i data
fr2 = Cells(Rows.Count, 16).End(xlUp).Row 'hvor mange rækker er i kriterieområdet
lr3 = Range("a1", Range("a1").End(xlToRight)).Count 'hvor mange kolonner er i data området
    
Range("a1", Cells(fr1, lr3)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "O1:R" & fr2), CopyToRange:=Range("U1"), Unique:=False

End Sub
 
kriterieområdet er sat til O1 til R & fr2 , hvis det skal være andet sted, lav O om, og Cells(Rows.Count, 16), skal så laves om til antal kolonner til kriterieområde start+1
Resultatet af filter bliver kopieret til start celle U1, hvis du vil have det andet sted, lav U1 om, men husk at det skal begge stede i kode.
 
Jeg håber du kan finde hoved og hale i ovenstående ellers skriver du bare igen


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 13.Sep.2011 kl. 23:44
Super sejt Clap fedt (du er jo Excel haj for vildt...Star)
 
Lidt forklaring følger:
 
Jeg har et Excelark med kontaktoplysninger (ca.3500 rækker)
Ud for hver række er der angivet følgende i de enkelte kolonner:
Kolonne A: ID-nr (entydigt nr)
Kolonne B: en anden oplysning
Kolonne C: en tredje oplysning
Kolonne D: en fjerde oplysning
Kolonne E: adresse
Kolonne F: nr (altså hus nr)
Kolonne G: litra (oftest et bogstav, men kan være et tal)
Kolonne H til AJ: andre oplysninger
 
Hvis man ikke lige husker eller kender det entydige ID-nr, så
vil jeg gerne have muligheden for at slår ID-nr op på adressen (altså adresse, nr og litra).
 
Det specielle ved denne opgave:
1. Kontaktoplysningerne ligger på et andet ark (i samme projektmappe) end der hvor man slår op
2. Samme adresse (og nr. og litra) kan godt forekomme flere gange - op til 12 gange tror jeg (med hver sit ID-nr)
3. Der skal ikke ændres i excelarket med kontaktoplysninger
4. Adresse og nr. skal være nøjagtig opslag, men Litra kan nøjes med at være tilnærmet
 
Det jeg ønsker:
Man angiver hhv.
- Adresse
- Nr
- Litra
 
Herefter slår Excel adressen op og returnerer de entydige ID-nr der er ud for adressen
(der er oftest kun et ID-nr, men der kan være op til ca. 12 ID-nr.
 
 
Da der er personfølsomme oplysninger kan jeg ikke oploade selve arket Cry
 
Tror dog godt vi kan bruge et af de eksempler du tidligere har lagt herind.
VBA kode er for sejt, men det kunne være fedt, hvis det kunne klares med Excels indbyggede funktioner.
 
DAMN I OWE YOU BIG TIME
 
Peder
 

Besked fra: peppe
Posteringsdato: 14.Sep.2011 kl. 01:53
... En "lille" detalje jeg netop har fundet ud af :
 
Tilsyneladende så er der i adresser og nr angivet "blanktegn" - altså mellemrum før og efter adresse og ligeledes før og efter nr.
Jeg ved ikke hvor udbredt problemet er, men tror det er generelt. FØØØØJ
 
Hmm Nå, men det er en importeret fil fra et andet program. Fra dette program har jeg eksporteret alle oplysninger som en .csv fil (semikolonseparerat fil). Jeg går ud fra det er her den er gået galt.
Ligeledes er der sandsynlighed for at disse data ved indtastning (i det andet program) ved en fejl er indtastet forkert (fx ved at der er sat et mellemrum før litra), så det ikke nødvendigvis er importen/eksporten der er gået galt.
Nogle steder er der flere mellemrum før/efter selve teksten.
Det gør selvfølgelig at uanset hvad jeg så søger på, så finder Excel ikke noget pga. de dumme mellemrum.
 
Hvordan fjerner jeg disse mellemrum før og efter teksten (ikke inde i teksten fordi det kan fx godt hedde "Hanne Ejlers Vej" men det skal ikke hedde "  Hanne Ejlers Vej         ".
 
Håber også du kan hjælpe med det ?
 
Venligst
Peder

Besked fra: rassten
Posteringsdato: 15.Sep.2011 kl. 00:50
Beklager men jeg har ikke fået nogen mail om at du havde lavet et nyt indlæg.

Men jeg har lavet dette eksempel
uploads/107/Find_id_51.xlsm - uploads/107/Find_id_51.xlsm

Dette bruger stadig  en makro, men denne gang ikke avanceret filter, men almindelige loop.

Hvis koden virker som du ønsker, og derfor vil flytte kode over i din egen excel fil. Så skal du huske at rette i koden så ark navnene bliver som dine ark navne.




-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: rassten
Posteringsdato: 15.Sep.2011 kl. 01:03
Dit andet problem med for mange mellemrum før og efter en tekst, er desværre ikke ukendt ved import fra et andet program.
I excel er der en funktion som =FJERN.OVERFLØDIGE.BLANKE(a1), som normalt kan løse problemet.

Men nu har du jo et meget stort data område og derfor vil jeg anbefale at bruge en makro
vælg alle celler og kør makroen:

Sub a_test()
For Each cell In Selection
    cell.Value = Trim(cell.Value)
Next cell
End Sub


Trim er næsten som FJERN.OVERFLØDIGE.BLANKE. Men FJERN.OVERFLØDIGE.BLANKE fjerne også overflødige mellemrum i teksten, altså "   aa       bbb    " bliver til "aa bbb". Men det kan Trim ikke "   aa       bbb    " bliver til "aa       bbb".
Så hvis du også har for mange mellemrum i teksten, skal du have en anden makro, men det forventer jeg ikke skulle være nødvendigt.


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 15.Sep.2011 kl. 20:13
Hej igen
 
Kan se denne tråd er ved at blive lang (2 sider nu he he).
 
Nå men først og fremmest så tror jeg godt tør prøve lidt makro.....
koden skal jeg lige have implementeret i mit ark.
Da jeg ikke er superskarp til VBA kode, så tænker jeg at måske kunne få dig til at indlægge nogle kommentarer,
i linierne, så jeg har mulighed for at begribe, hvad der sker ?
 
Herefter, så er jeg pludselig blevet klogere på "Fjern.overflødige..." og "Trim" tak for det :-) , men
kan TRIM kun bruges i makro (kan ikke finde den under funktioner) ?
 
Peder

Besked fra: rassten
Posteringsdato: 15.Sep.2011 kl. 21:33
Hej
jeg vil selvfølgelig gerne skrive nogle kommentar linier i koden. Men er ikke ved en computer lige nu.
Trim er fjern.overflødige på engelsk. Men som jeg startede på at skrive i går. Fjern (Trim) i excel og i vba er ikke præcis det samme.
Det betyder desværre at næsten alt i vba er på engelsk, og at de samme   funktioner i vba og excel ikke er ens selv de kan have samme betegnelse.
Derfor hvis du har problemer med for mange mellemrum mellem ord og vil bruge vba skal der en lidt anden kode end den du fik som nr 2.

-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: Allan
Posteringsdato: 16.Sep.2011 kl. 08:55
Hej med jer,
 
Som en tilføjelse til rasstens kommentar.
Citat: rassten rassten skrev:

Fjern (Trim) i excel og i vba er ikke præcis det samme.
 
Hermed redigeret udgave af rasstens kode, blot baseret på Excels 'Fjern.overflødige.blanke'.
Sub a_test_2()
For Each cell In Selection
cell.Value = Application.WorksheetFunction.Trim(cell.Value)
Next cell
End Sub
 
 
Så kan du bruge den kode som passer bedst ind til netop dit behov.
 
//Allan

Besked fra: rassten
Posteringsdato: 17.Sep.2011 kl. 00:11
Hej igen
Både Peder og Allan

Allan har selvfølgelig helt ret i hans makro en god måde at få vba til bruge excel Fjern.over... og ikke vbas egen Fjern..(Trim).

Peder, jeg har skrevet de en del kommentar linier i min tidligere makro, for vise hvad og hvordan den virker.
Hvis der er noget som du er i tvivl om eller har kommentar, skriver du bare igen.
uploads/107/Find_id_5_2.xlsm" rel="nofollow - uploads/107/Find_id_5_2.xlsm




-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: peppe
Posteringsdato: 18.Sep.2011 kl. 19:48
Hej til Jer begge
 
Først og fremmest vil jeg sige mange tak for de mange gode input.
Det er superlækkert at mulighederne er så mangfoldige og man kan få det næsten helt efter eget ønske.
Det er bare fedt.
 
Det viser sig dog at ved at at benytte "arkbeskyttelse" (under fanen "gennemse"), så er der et par problemer...
Excel brokker sig over:
 
- flettede celler
Det har jeg så rådet bod på ved at ændre dette
 
- indsættelse at værdier betyder at cellerne ændrer format (bl.a. kant linier forsvinder)
Da jeg har opbygget søgeområdet med kantlinier mv. så skal dette helst ikke ændres.
Lidt primitivt har jeg lavet en makrooptagelse, hvor jeg ændrer bl.a. kantlinie og indsat det i VBA koden.
 
- kantlinier
Jeg har ændret celleområdet det handler om til at være "åbne" og er derved ikke "låst", men
ønsker egentlig disse skal være låst
 
Mine spørgsmål:
 
1. Kan jeg låse celler (naturligvis undtaget de celler man skal indtaste adresse i mv.) uden VBA-koden
skaber problemer med låste ark ?
Alternativt om man i VBA koden kan slå arkbeskyttelsen fra medens koden kører?
 
2. Den makrokode jeg har indsat i VBA-koden for at indsætte kantlinier virker meget voldsom
Kan det gøres nemmere?
 
Jeg indsætter Et excel-ark indeholdende VBA-koden herunder:
 
/uploads/106/Eksempel_på_kode_VBA.xlsm" rel="nofollow">uploads/106/Eksempel_på_kode_VBA.xlsm
 
Herundeer et word dokument, hvor søgeområdet ses:
 
uploads/106/Word_med_udseende.docx" rel="nofollow - uploads/106/Word_med_udseende.docx
 
Hvis der er spørgsmål for at gøre det forståeligt, så vend endelig tilbage...  Smile
 
 
 
De bedste tanker
Peder
 
 
 
 

Besked fra: rassten
Posteringsdato: 19.Sep.2011 kl. 00:59
Til 2.

Fra under "Range("AR14:BN50").Select" og ned til "Range("AR14").Select"
 burde kunne slettes og erstattes med:

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).Weight = xlThin
        Selection.Borders(xlEdgeTop).Weight = xlThin
        Selection.Borders(xlEdgeBottom).Weight = xlThin
        Selection.Borders(xlEdgeRight).Weight = xlThin
        Selection.Borders(xlInsideVertical).Weight = xlThin
        Selection.Borders(xlInsideHorizontal).Weight = xlThin
       




-------------
VH rassten

Arbejde excel 2010
Privat excel 2010

Besked fra: rassten
Posteringsdato: 19.Sep.2011 kl. 01:10
Til 1.

Som taget fra Allans svar: https://forum.excel-regneark.dk/kre-en-makro_topic264_post1434.html?KW=Password#1434

Øverst i din kode:
ActiveSheet.Unprotect "123"

og til sidst i din kode:
 ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True

Du skal selvfølgelig lave 123 om til hvad ønsker koden skal være


-------------
VH rassten

Arbejde excel 2010
Privat excel 2010


Print side | Luk vindue