Публикувана: 6 септ. 2015, 23:06 ч.
Последно мнение: 13 май 2021, 02:27 ч.

Да се научим да работим с Excel - тема 3

88 063
743
1

Отговори

Q&A

Обобщени въпроси и отговори от темата *

Как мога да импортирам текстов файл (.txt), съдържащ код и име в Excel?
Как мога да се справя с условно форматиране в Excel, когато променям локацията на датата?
Как мога да използвам условно форматиране за дати в Excel?
* Предложените въпроси и отговори се генерират машинно от автоматизиран езиков модел на база потребителските мнения в темата. Генерираното съдържание може да е непълно, неактуално, подвеждащо или неподходящо. Вашите оценки спомагат за подобряване на модела и неговото усъвършенстване.
- Как мога да импортирам текстов файл (.txt), съдържащ код и име в Excel?
  
  Можете да импортирате текстов файл в Excel, като използвате функцията "Text to Columns". Ако информацията е разделена със запетая или интервал, тя ще бъде разделена в отделни колони. Като алтернатива можете да използвате Data -> XML -> Import за импортиране на текстовия файл.
- Как мога да се справя с условно форматиране в Excel, когато променям локацията на датата?
  
  Когато променяте локацията на датата в Excel, условното форматиране може да не работи правилно. Това е така, защото формули като TODAY се третират като текст. За да разрешите този проблем, можете да въведете формулата за текущата дата в отделна клетка извън таблицата и след това да редактирате правилото за условно форматиране, като използвате абсолютни адреси и стойности вместо формули като TODAY.
- Как мога да използвам условно форматиране за дати в Excel?
  
  За да използвате условно форматиране за дати в Excel, можете да дефинирате правила за формат, като използвате функции като TODAY или конкретни дати. Например, можете да форматирате клетки, които съдържат дата след текущата дата с помощта на условието „Формат клетки, които съдържат...“.
- Как мога да копирам част от информацията от една клетка в Excel?
- Как мога да активирам хипервръзките в Excel, които са оцветени, без да ги обработвам ръчно?

# 450 29 ян. 2020, 15:54 ч.

Мнения: 2 391

За pivot трябва всички редове да са ти попълнени във всички колони, за може да ти направи обощени таблици.

# 451 29 ян. 2020, 15:58 ч.

Мнения: 35 920

Ако е само по трета колона за някой продукт, ползвай филтър, задавайки да ти чете цялата таблица. Празните не пречат.

# 452 29 ян. 2020, 17:22 ч.

Мнения: 10 878

Цитат на: shrekie в ср, 29 яну 2020, 15:54

За pivot трябва всички редове да са ти попълнени във всички колони, за може да ти направи обощени таблици.

добре, но как най-бързо да ги попълвам и да махам празните 2 реда, и те ще пречат май за пивот.

Цитат на: Soul Free в ср, 29 яну 2020, 15:58

какво имаш предвид - "да ми чете цялата таблица" -аз искам като направя филтър по "продукт" вляво да ми пише кой е клиента и от коя фирма е. Трябва ли да попълвам данните на всеки клиент в първите две колони? Ако може по-подробно да ми обясниш идеята ти?

# 453 29 ян. 2020, 17:51 ч.

Варна
Мнения: 3 308

Pepina_n3 За решение на твоя казус има три варианта:
1. С формули - може би първото, което идва наум. Трудоемко е, но ще го напиша по-долу.
2. С макроси - още по-сложно и по-трудоемко от първия, затова няма да го обясня, само го споменавам като вариант.
3. С Power Query - това ми е фаворит, лесно и удобно, но много хора не са работили с него. Обяснявам го, защото за хора, които се занимават с данни от други източници и трябва преобразуване, си е много полезно и направо задължително.

Първи вариант
Първо трябва да се отървеш от празните редове. Маркираш всички редове и колони с данни и активираш Filter. След това селектираш Blanks и ги изтриваш.
След това добавяш колона след ProductName, нека да е в колона D и първата клетка с формула да е D2. Формулата в нея трябва да е: =IF(ISBLANK(A2);D1;A2) и копираш надолу. Аналогично се действа с името. Ако не ти трябват първите две колони (които имат празните клетки), преди да ги изтриеш, копирай само стойностите в новите колони. (Copy + Paste Special - Values).

Това е доста трудоемко и трябва да се прави при всяко ново добавяне на данни. Ако може по някакъв начин да се промени вида, в който идват данните, ще е по-добре.

Трети вариант
1. Маркираш всички данни и ги форматираш като таблица - Format as Table.
2. След това избираш произволна клетка от таблицата и Data - Get & Transform Data - From Table/Range . Това отваря Power Query Editor, в който много лесно се извършва преобразуването на данните в приличен вид. Следващите стъпки се изпълняват в него.
3. За премахване на празните редове - Home - Remove Rows - Remove Blank Rows.
4. За попълване на празните клетки - Transform - Fill Down за двете колони с кодовете и имената.
5. Накрая за да върнеш данните в Excel избираш File - Close & Load.

# 454 29 ян. 2020, 19:40 ч.

тук-там
Мнения: 4 364

Да не ви изтърва. Последното мнение ми е супер полезно и на мен.
Благодаря Ясмина.
Моите таблици са горе долу същите. В първите две колони имам имена и дати - надолу празно докато в третата колона не свършат съответните "неща". Като филтрирам по трета колона и в първите е празно, а ми е важно.

# 455 29 ян. 2020, 20:10 ч.

Мнения: X

Маркирайте клетката която ви интересува. После двоен клик върху маркера долу в дясно на клетката ще запълни празните полета под нея
Филтър, ауто филтър и избирате каквото ви трябва. За многоредова таблица пак върши работа но е малко скучно.

За примера - избирате клетката с името на фирмата.

# 456 30 ян. 2020, 22:43 ч.

Мнения: 10 878

Цитат на: Mr. Smith в ср, 29 яну 2020, 20:10

Двойното кликване го зная, и в случая празните редове вършат идеална работа, но таблицата е дълга и не ми се ще ръчно да минавам всеки клиент.

Цитат на: Ясмина в ср, 29 яну 2020, 17:51

Pepina_n3 За решение на твоя казус има три варианта:

Първият вариант става, но е трудоемко. Нямам това време.
Третия вариант също е готин, получава се, за съжаление и нов Sheet прави. Но пак е трудоемко, за да го прявя всеки път, склонна съм за макрос. Но броя на редовете в таблицата е променлив, да допълня. Първоначално данните са в Ексел, трябва ли да се прави "Format as table"? Опитах с макрос, но има проблем с името на таблицата, при всяко пускане става различно (Table2, Table 3)
Аз все си мислех, че не случайно ми дават данните в този вид, мислех, че са идеални за Пивот. Но се оказа, че не съвсем.
Благодаря за подкрепата, ще продължавам да търся и аз, ако намеря ще публикувам.

# 457 31 ян. 2020, 08:43 ч.

Мнения: X

@Pepina_n3
Човешката мисъл е безгранична. Grinning

Ако целта на задачката е просто "да направя справка кои клиенти са си купили дадено нещо" и се касае за огромна таблица Ясмина с третия вариант ви даде едно елегантно решение което отхвърлихте защото прави нов Sheet и е трудоемко. Може би аз не разбирам добре но ето ви и четвърти вариант. Допускам, че данните в суров вид пристигат във файл който често е с еднакво име. Затова...Стартирайте Ексел с нов празен workbook и оставете само един Sheet.
Data - Get Data - From File - From Workbook изберете файла със суровите данни - Import - изберете шийта с данните - Transform Data
В Power Query Remove Rows - Remove Blank Rows
Селектирайте колоните Customer ID и Company name - Transform - Fill - Down. Вече имате нормализирана база за анализ.
Home - Close & Load to.. и в прозорчето изберете Table или PivotTable Report (изберете това което според вас ще ви върши повече работа). Запишете новата таблица под име което ви допада - например Pepina_n3 Analysis.xlsx.
Когато получите новият файл от приложението презапишете стария и когато отворите този който сте създали той ще съдържа нормализирани данни без да се налага да правите каквото и да било.

Ако бях на ваше място в import data щях да избера Table. С помощта на филтъра ще може да избирате един или няколко продукта и резултата ще е в прегледан табличен вид. Пивотките са за друг вид задачи. Stuck Out Tongue Winking Eye

# 458 31 ян. 2020, 09:43 ч.

Мнения: 35 920

Ясмина и Мр Смит, моля за насоки. Реших да опитам да "поправя" таблица, в която умишлено изтрих няколко клетки и вкарах няколко празни реда.
В Data не виждам къде е Get Data…..

Get External data ме прехвърля да избера външен файл и само го зарежда като информация без да ми отваря подпрозорец, където да мога да правя нещо....

# 459 31 ян. 2020, 11:55 ч.

Варна
Мнения: 3 308

Soul Free Вашата версия на Excel e по-ниска, затова трябва да търсите Power Query Editor в Data - Get External Data - From Other Sources.

# 460 31 ян. 2020, 11:56 ч.

Мнения: 35 920

Аааа, благодаря. Сега ще видя....

Това ми е подменюто.

Последна редакция: пт, 31 яну 2020, 12:01 от Soul Free

# 461 31 ян. 2020, 12:31 ч.

Мнения: X

Мисля че можете да изтеглите power query за Ексел 2010 и 2013. От сайта на Майкрософт

# 462 31 ян. 2020, 13:41 ч.

Мнения: 35 920

Благодаря. Явно в момента нямам тази функционалност.
Не, че ми трябва в момента. Просто исках да опитам нещо, което не бях пробвала до сега.

# 463 31 ян. 2020, 20:31 ч.

Варна
Мнения: 3 308

Сега имам време да напиша отговор. Pepina_n3 Както и Mr Smith каза, Power Query е вашето спасение. Тези данни, които идват в неприемлив вид, не могат да ви служат нито за филтри, нито за pivot таблици. Мислете за тях като за източник, от който да се получат данните, които ще анализирате. С тях директно не може да работите.

Вие не казахте как пристигат данните - дали са с натрупване или не. Но да предположим, че не са с натрупване, а идват на определен период, защото това е по-сложния случай. Единственото усилие, което трябва да направите, е да копирате новите данни под старите. След това избирате Resize Table от Table Tools - Design за да включите и новокопираните данни в таблицата. Хубавото на Power Query e, че помни какви стъпки сте му задали предния път и не е нужно да ги правите наново. Та, след като се включили и новите данни в таблицата, отваряте панела Queries and Connections (ако не се вижда, го избирате от меню Data) и единственото, което трябва да направите е да рефрешнете заявката, натискайки бутона Refresh до самата заявка. След това данните от "новия шийт" ще се допълнят с новите в правилната структура. При всяко изпълнение на Refresh няма да се създава всеки път нов лист, а ще се допълва старата таблица с правилните данни.

Макросите бих ви съветвала да ги забравите, защото наистина е доста трудоемко и вероятността за грешки е голяма, както може би сте се убедили вече. Може да се опитате с Macro Recorder да запишете тези действия, които описах по-горе и да ги изпълнявате. Но имайте предвид, че трябва да използвате подходящ метод, който да разпознава докъде трябва да оразмери таблицата.

Soul Free Power Query е вградена функционалност в Excel 2016, а за по-старите версии е безплатен Add ins, който може да свалите от сайта на Microsoft, както писа и Mr Smith.

# 464 31 ян. 2020, 22:21 ч.

Мнения: 10 878

Данните пристигат винаги по един и същи начин, в Ексел, когато ми трябва справка си генерирам от апликацията. Отразяват положението в текущия момент. Не е нещо с натрупване. Дължината не може да се прогнозира, да кажем, че са максимум 800 реда. Опитах да направя макрос, записващ действията. Но ми излезе грешка.

ActiveWorkbook.Queries.Add Name:="Table1 (3)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""CustomerId"", Int64.Type}, {""CompanyName"", type text}, {""ProductName"", type text}})," & Chr(13) & "" & Chr(10) & " #""Removed Blank Rows"" = Table.SelectRows(#""Changed Type"", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"""", n" & _
"ull})))," & Chr(13) & "" & Chr(10) & " #""Filled Down"" = Table.FillDown(#""Removed Blank Rows"",{""CustomerId"", ""CompanyName""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Filled Down"""

Power Query е много хубаво нещо! Безспорно! Идеално е, но не искам да се затормозявам с тези кликове всеки ден. Имам да мисля за други неща. И тази справка ми трябва по най-бързия начин, да я генерирам с натискане бутона на макроса и да направя филтър, да намеря каквото ме интересува. Макрос би било идеално - влагам усилия в написването и после макросът да търси къде да кликне.
С Първия вариант е по-лесно да се направи макрос, защото там е с формула, оставаш в същия Sheet.
С общи усилия ще измислим нещо. Това е реален проблем, породен от практиката.

Веднъж направих на един мой познат една формулка и той цял живот ми е благодарен, че бизнесът му се крепи на нея и я използва ежедневно години наред.

Препоръчани теми

Всякакви въпроси относно пералнята - тема 7

Азиатска козметика и грим - 21

Имате ли хоби??

Най-доброто отопление според нашия дом

647
Нашият дом

Причина:

Причина: