Summera teckenpoäng

[Ruatha2]

Hej, ett problem:

 

I blad1 har jag en textmassa utspridd över ett flertal celler, säg A1:N50

i blad2 vill jag få fram ett värde i en cell, detta värde är beräknat på texten i blad1 enligt följande.

Alla A:n är värda 1 poäng, alla B:n 2 poäng etc, (versaler/gemener ska inte skiljas åt).

Jag kan få fram antal celler som innehåller A:n, eller antal A:n i en cell (Tack Xcel Dennis sida), men att få ihop det så att det fungerar totalt lyckas jag inte med.

 

Är ett VBA makro där man räknar ut poöng utifrån asciikod enklast, eller finns det någon bra formel jag kan avnända i kalkylarket?

 

Hur skulle ett makro se ut? jag är erkänt dålig på VBA!

 

[Monshi]

Två vägar att gå. Steg ett är lika; skapa en funktion som räknar antalet av givet tecken, eller snarare sträng, i en given cell:

[color="#0000ff"]Function[/color] CountChar(values As Variant, toCount As [color="#0000ff"]String[/color]) As Long
    [color="#0000ff"]Dim[/color] myValue As Variant
    [color="#0000ff"]Dim[/color] index As Integer
    [color="#0000ff"]Dim[/color] cn As Long
    [color="#0000ff"]For[/color] [color="#0000ff"]Each[/color] myValue [color="#0000ff"]In[/color] values
        index = [color="#0000ff"]InStr[/color](1, myValue, toCount, vbTextCompare)
        [color="#0000ff"]While[/color] index <> 0
            cn = cn + 1
            index = [color="#0000ff"]InStr[/color](index + [color="#0000ff"]Len[/color](toCount), myValue, toCount, vbTextCompare)
        [color="#0000ff"]Wend[/color]
    [color="#0000ff"]Next[/color] myValue
    CountChar = cn

[color="#0000ff"]End[/color] [color="#0000ff"]Function[/color]

 

Denna kan man sedan antingen nyttja som en funktion på arbetsbladet vilket nog kan den enklaste vägen.

Dvs - sätt values till området med dina celler i och toCount som den bokstav/sträng du vill söka. toCount kan vara referensen till en cell eller en sträng direkt i formeln.

 

Alternativt drar man nytta av Excels sökfunktion Find för att hitta cellerna med bokstaven i och sedan låta funktionen räkna förekomsten.

 

Rent praktiskt är nog funktionen ovan att föredra. Om du har en funktion som kan ta fram alla celler med bokstaven i först, dvs en OM-sats som sorterar bort all som ej. Kan gå lite snabbare om det är stora mängder text som ska sökas igenom.

 

Två exempel:

Med text i A1:B300

med det du vill söka efter i C1.

Enkel formel:

=CountChar(A1:B300;C1)

Kanske snabbare:

{ =CountChar(OM(N(ÄRTAL(SÖK(C1;A1:B300)));A1:B300;"");C1) }

 

Den andra är en matrisformel, måsvingarna fyller Excel i när du matar in det som en matrisformel. För att göra det avsluta inmatningen med CTRL+SHIFT+ENTER.

 

Koden - kopiera denna och klistra in den i en modul i VBA-editorn.

ALt+F11 öppnar editorn, högerklicka på din bok och välj infoga modul.

Klistra in koden.

 

klart.

 

Edit: Den ovan gör skillnad på versaler/gemener. Återkommer snart med en fix.

Edit2:Fixat, lade till vbTextCompare till InStr().

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

 

[inlägget ändrat 2006-11-14 10:28:34 av Monshi]

 

[inlägget ändrat 2006-11-14 10:30:34 av Monshi]

 

[inlägget ändrat 2006-11-14 10:31:05 av Monshi]

[MH2]

Blev bara tvungen att kolla om det går med KalkylbladsFormel.

 

Klarar bara korrekt svar för A-z. Men det gick nästan i alla fall:-)

 

=PRODUKTSUMMA((KOD(VERSALER(EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1)))*1-64))

 

[Monshi]

Att man inte testar först med formler! Jag hade för mig att jag försökt förut och misslyckats men se - det går! Och enkelt! Men MH2 - vad gör din formel? Summerar teckenvärdena?

 

Denna enkla formel:

=PRODUKTSUMMA(LÄNGD(A1:B300)-LÄNGD(BYT.UT(GEMENER(A1:B300);GEMENER(C1);"")))

gör precis det som min mer komplicerade kod ovan gör. Fast mycket snabbare kan jag nog lova.

 

Hade för mig, som sagt, att dessa text-funktioner ogillade matriser. Men så fel jag hade.

 

Tack MH för att du ledde mig in att försöka

 

 

Edit:

Testade att jämföra min funktion och den i detta inlägg.

Nu har Excel det jobbigt. Frågan är om inte Excel har hängt sig.. Nej se - nu vaknade Excel igen... Samma svar som kalkylbladformeln ovan men cirka 3 minuter senare... Formeln ovan gillar dock inte referenser över hela kolumner, dvs A:B

 

 

Detta bekräftar än en gång att om det går att använda Excels egna funktioner så ska man använda Excels egna funktione.

 

 

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

 

[inlägget ändrat 2006-11-14 12:31:53 av Monshi]

[Ruatha2]

Inte bara två alterantiv, utan snabba svar också!!

 

Tack ska ni ha!!

 

[Ruatha2]

Ok, har testat kalkylbladsformlerna, den från MH2 fungerar bara för en cell i taget, eller hur?

 

Den från Monshi, den summerar från A1 till B300, men vad är cell C1?

Oj, läs allt först, flåt Monshi! jag ser vad C1 ska göra

 

[inlägget ändrat 2006-11-14 15:45:54 av Ruatha 2]

[Monshi]

A1:B300 är området den söker i. Ange det område du vill.

 

C1 är cellen med det du vill söka efter, dvs en cell med ett a, ett b... i. Du kan ersätta C1 i formeln med den bokstav/sträng du vill söka efter.

 

Använd den formel jag gav, inte koden.

 

 

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[Ruatha2]

Hej igen.

nja det fungerar inte riktigt.

jag testade med detta:

=PRODUKTSUMMA(LÄNGD(A1:B5)-LÄNGD(BYT.UT(GEMENER(A1:B5);GEMENER(C1);"")))

och texten "Aba" i A1 och "ab" i A2 och "a" i C1 men får noll som svar (testade "A" i C1 också), ska testa enstaka celler nu och se

 

 

MH2:s kod fungerar bra men ännu bara för en cell i taget och som sagt a-z, men en bit på vägen!

[inlägget ändrat 2006-11-14 15:52:00 av Ruatha 2]

[Ruatha2]

MH2:s kod ändrad till:

=ÄRFEL(PRODUKTSUMMA((KOD(VERSALER(EXTEXT(Blad1!A1;RAD(INDIREKT("1:"&LÄNGD(Blad1!A1)));1)))*1-64)))

 

fungerar, men när jag sätter den i en OM sats får jag ett fel och den markerar LÄNGD, t ex i

OM(ÄRFEL(PRODUKTSUMMA((KOD(VERSALER(EXTEXT(Blad1!A1;RAD(INDIREKT("1:"&LÄNGD(Blad1!A1)));1)))*1-64)));1;0)

 

Jag tänkte lägga in denna formel i varje cell i blad 2:

=OM(ÄRFEL(PRODUKTSUMMA((KOD(VERSALER(EXTEXT(Blad1!A1;RAD(INDIREKT("1:"&LÄNGD(Blad1!A1)));1)))*1-64)));0;PRODUKTSUMMA((KOD(VERSALER(EXTEXT(Blad1!A1;RAD(INDIREKT("1:"&LÄNGD(Blad1!A1)));1)))*1-64)))

 

men får det inte att fungera riktigt....

 

[Monshi]

Den fungerar. Kopierar exakt det du testat och den fungerar...

 

Se bifogade bilden om du tvivlar.

 

Så undrar vad som går fel.

 

Vilken version av Excel har du?

 

edit: I cell E3 står formeln, exakt den formel du har i ditt inlägg ovan.

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[bild bifogad 2006-11-14 16:05:23 av Monshi]

 

[inlägget ändrat 2006-11-14 16:06:21 av Monshi]

[MH2]

Det borde väl gå att använda PASSA()?

 

=PRODUKTSUMMA((PASSA((EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1));{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};1)))

(skall sitta ihop på en rad)

 

Käns kanske lite hemmagjort, men jag vet inte hur man skall få ÅÄÖ att fungera annars.

Klarar en cell i taget så länge den bara inehåller "lagliga" tecken, men den får frispel av mellanslag och liknande. Får den inte att fungera med flera celler heller. Hmmm, borde gå att hantera felvärden, hmmmm.

Nej, nu måste jag läsa Monshis inlägg och se vad hans formel går ut på.

 

 

[Ruatha2]

Monshi, jag har Office XP, Excel 2003 (11.8105.8107) SP2.

Den markerar LÄNGD när den säger att det blir fel, det spelar ingen roll om jag har formeln i blad1 eller blad2.

 

MH2, det fungerar nästan perfekt! Jag lägger in din formel i varje cell i blad2, de checkar respektive cell i blad1 och sedan summerar jag alla cellerna i blad2!!!

Om det är ett annat tecken än de angivna (A-Ö) så blir värdet 0. Så jag måste rensa bort allt annat först, ska se om jag kan hitta på nåt enkelt sätt.

 

Tack!

 

Bild på felet på den tidigare formeln, varför det blir fel vet jag ej, det är den andra förekomsten av LÄNGD den markerar.

[bild bifogad 2006-11-14 21:24:53 av Ruatha 2]

[inlägget ändrat 2006-11-14 21:32:04 av Ruatha 2]

[inlägget ändrat 2006-11-14 21:38:18 av Ruatha 2]

[MH2]

Moahhaaaa, The matris-formel from Hell

Mata in det här som en Matrisformel (CTRL+SHIF+ENTER när du skrivit in formeln, det skall dyka upp "måsvingar" runt hela formeln)

 

=SUMMA(OM(ÄRFEL(PASSA((EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1));{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};1));0;(PASSA((EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1));{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};1))))

 

Går fortfarande inte att lägga ihop flera celler, men jag är äckligt nöjd med att det fungerar.

Nehe, just det, jag skulle ju försöka se vad Monshis formel gjorde..

 

[Ruatha2]

Jag funderade på att göra en trebladsserie.

Blad1 innehåller data i de olika cellerna

Blad2 innehåller rensad data (Blad1 minus alla tecken som inte är bokstäver)

Blad3 innehåller siffervärden (Passaformeln ovan).

 

Men hur bäst göra en formel för att ta bort alla tecken som inte är boksäver? Jag kan ta bort alla mellanslag t ex med =BYT.UT(A1;" ";"") men då blir alla punkter, komman, siffror etc kvar?

 

[Ruatha2]

Testar matrisformeln from hell nu

Missade den först!

 

[Ruatha2]

Ohhh!!!

Matrisformeln from Hell fungerar utan yttre påverkan, precis som den ska, utan att det krävs utrensningsaktioner eller annat!

Stort tack, jag är djup imponerad och förstår att du är nöjd!!!!

 

Nu räcker det med 2 blad, ett datablad och ett summeringsblad, där jag sedan summerar cellerna och får fram ett värde.

Suveränt!

 

[MH2]

Nu har jag läst Monhis formel och testat den (Lyckas inte fokusera på någonting mer än 3 sekuner i taget idag).

 

Det är deffinitivt den formel jag skulle välja. Passar dina behov MYCKET bättre. Den summerar alltså antalet av den bokstav som står i cell c1.

Skriv en lista med A,B,C,D... i cell C1-C28 och 1,2,3,4... i cell D1 till D28. Sen kan du enkelt kopiera ner formeln och få reda på antalet A'n, B's osv. Multiplicera ihop C och D- kolumnen så får du "poängen" för varje bokstav. Summera din kolumn och du har din poäng. Kom bara ihåg att "låsa" referensen till dina data (sätt in dollartecken så att det bara är C1 som ändras, inte $A$1:$B$300).

 

 

 

Ähhh, vill inte släppa "PASSA". Det här är en variant av Monshis Formel som direkt summerar poängen. Men den klarar inte mer än 1 kolumn och jag skulle nog inte vilja använda den på något större datamaterial. Men det går

 

=PRODUKTSUMMA((LÄNGD(A1:A300)-LÄNGD(BYT.UT(VERSALER(A1:A300);{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};"")))*(PASSA({"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};0)))

 

Ähhh, försök felsöka den formeln... som sagt var, jag skulle använda Monshis formel. Den är i alla fall överskådlig.

 

[MH2]

Bahhh, För arkivet. "Matrisformel from hell" har en bugg. Aldrig får man vara nöjd.

PASSA skall naturligtvis ha "typen" 0, inte 1. Annars kommer den att räknar en del skiljetecken (? och * i alla fall, säker några andra också).

 

Det är alltså två 1:or som skall bytas ut mot 0:or

 

=SUMMA(OM(ÄRFEL(PASSA((EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1));{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};0));0;(PASSA((EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1));{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};0))))

 

[Monshi]

Aj fan - nu ser jag att jag läst fel... Du vill räkna ihop en poäng för alla bokstäverna i ett område, inte räkna förekomsten av en viss bokstav och poängsätta denna... mmmh, då är min formel lite fel ute men ändå väldigt enkel att använda.

 

En cell per bokstav. Multiplicera med önskat värde, summera. Ingen matrisformel "from-hell"...

 

Nåja, godnatt.

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[Ruatha2]

MH2: Den fungerar bättre med 1:an än med 0:an.

Nu har jag ettan och då får skiljetecknen ingen poäng, och en mening som

"Är det ok?" ger 101 poäng:

Ä=28

r=18

d=4

e=5

t=20

o=15

k=11

======

101 poäng,

byter jag till noll blir det 102 poäng...

 

SÅ jag är rätt nöjd, ska kika på Monshis förslag igen.

 

[Ruatha2]

Hej. Monshis förslag fungerar också!!

Nu har jag två metoder, Jättebra!!

 

Jag väljer nog Monshis som du säger MH2 då den är enklare att justera om jag vill lägga till fler tecken eller öka området!

 

Tack båda två!

 

 

 

[inlägget ändrat 2006-11-15 08:54:51 av Ruatha 2]

[Ruatha2]

Jag sitter och studerar båda alternativen, som båda fungerar jättebra, men jag har svårt att förstå hur...

Jag ska fortsätta grunna på dem, jag har mycket att lära här!!

 

Tack igen

 

[Monshi]

Min formeln funktion är enkelt att beskriva.

1: Beräknar längden på strängen i given cell.

2: Ersätter givet tecken/sträng i cellen given i punkt 1.

3: Beräknar längden på resultatet i punkt 2

4: Beräknar skillnaden.

 

Sedan sker detta i en matrisformel. Att använda PRODUKTSUMMA är i praktiken likvärdigt med { SUMMA() }.

 

Och en matrisformeln innebär att beräkningen sker cellvis, varje cell "propagerar" genom formlerna för sig så att säga. Viktiga då är att de båda matriserna i detta fall är lika stora, de kan vara olika områden om så önskas.

 

Får man fråga - vad är det du gör egentligen? Vad är målet?

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[MH2]

Hmmm, Mystifistiskt. När jag använder Passa(x;x;0) på jobbet (excel2000) så tolkas ? och * som jokertecken och summan blir fel (som du skriver). Men när jag gjorde samma sak i går kväll (Excel XP) så var det Passa(x;x;1) som gav fel. Ok, jag var ganska trött...

 

Min formel är egentligen ganska enkel den också, den ser bara komplicerad ut på grund av den "Manuella listan" med bokstäver.

Bygger på:

 

PASSA(X;Y;Z) är excels "leta i matris" funktion. Den returnerar positionen i matrisen för "träffen"

 

Testa det här t.ex:

=PASSA(A1;{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z";"Å";"Ä";"Ö"};0)

Om du skriver in en bokstav i A1 så kommr den att returnera "poängen" för den bokstaven, beroende på vilken ordning bokstäverna står i.

 

Matrisen behöver inte vara manuell. Det skulle lika gärna kunna vara en adress till några celler (det är den vanligaste användningen)

 

För att slippa de oändliga formlerna kan du även använda någonting som kallas "Matriskonstanter". Gå till

Infoga, Namn, definiera

I Namn skriver du ABC

I "refererar till" skriver du {"A";"B";"C" .... "Å";"Ä";"Ö"} (hela listan)

Klicka på OK

Nu kan du skriva ovanstående formel som

=PASSA(A1;ABC;0)

Lite smidigare :-)

 

 

RAD(INDIREKT("1:"&LÄNGD(A1))) är ett jobbigt, men fungerande sätt att skriva "alla tal från 1 till xx" där xx=längden av cell A1

 

EXTEXT(Text;Startposition;Antal Tecken)

Den returnerar "Antal Tecken" från "Text" med början vid "Startposition"

I vårt fall är

"Antal Tecken" alltid =1

"Text" = Den cell du tittar i

"Startposition"= en matris, från 1 till längden av "Text"

EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1)

Är alltså bara ett komplicerat sätt att få Excel att returnera ett tecken i taget från cell A1:-)

 

Det här tecknet skickas in i PASSA() och jämförs med listan varpå tecknets sifferpoäng returneras.

SUMMA ligger "längst ut" och summerar teckenpoängen när EXTEXT går igenom varje bokstav. Ja, sen ligger det lite Felhantering och annat skräp också.

 

Så här blir MFFH om man använder en matriskonstant

 

 

=SUMMA(OM(ÄRFEL(PASSA((EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1));ABC;1));0;(PASSA((EXTEXT(A1;RAD(INDIREKT("1:"&LÄNGD(A1)));1));ABC;1))*1))

 

 

eller den modifierade "C1-lösa" Monshiformeln

=PRODUKTSUMMA((LÄNGD(A1:A300)-LÄNGD(BYT.UT(VERSALER(A1:A300);ABC;"")))*(PASSA(ABC;ABC;0)))

Skulle vara kul att se om den ger rätt svar