Въпрос към урок: Трикове при Copy/Paste при Excel. Използване на Paste Special
от курс: Тайните на Excel
Как да използвам едновременно комбинацията от transpose и paste link?
Здравейте, искам да копирам няколко клетки (от един ред) с формули от един sheet и да ги paste - на в друг, но под формата на колана, като запазя и линковете. В момента, в който отбележа transpose, опцията за paste link става неактивна. Благодаря за информацията.
Здравей,
има вариант на Transpose, който най - вероятно би ти свършило работа точно за този случай.
Идеята ми е да ползваш формулата Transponse в рамките на Excel.
Първо диапазона, който имаш ( снимка 1 позиция 1 ) го маркирай и му задай някакво име за да стане масив под дадено име ( снимка 1 позиция 2 - в моя случай за по - бързо е написано krasitest ) изпиши името при селектиран диапазон и дай Enter. Вече този диапазон е с име krasitest.
Като е много важно да се отбележи и запомни че масива който съм създал се състой от 5 колони и 7 реда.
Сега отивам в друг sheet или пък в друг файл ако искаш. И там маркирам с курсора на мишката 7 реда и 5 колони - снимка 2 позиция 1, отивам директно горе в реда ( снимка 2 позиция 2 ) и изписвам формулата = TRANSPONSE ( krasitest) и тук много важно е като изпиша формулата да натисна Ctrl + Shift + Enter за да може да се отрази във всичките маркирани клетки.
На снимка 3 е резултата от действията ни, а на снимка 4 показва какво е станало с формулата TRANSPONSE след като след въвеждането ѝ натиснахме клавишната комбинация - Ctrl + Shift + Enter - оградена е в { } и за целия маркиран от нас диапазон, като е трансформирала диапазона ( krasitest ) по колони и редове.
Това е един алтернативен метод на TRANSPONSE който не минава през използването на PASTE, така че ако имаш link - нати стойности в клетките не би следвало да се променят ами да са си линкове.
Най - вероятно има и други бързи методи ползвайки VBA или пък някоя друга техника в Excel, от мене е тази :)
Надявам се още някой да сподели и друга техника, т.к. казуса е интересен.
Надявам се да съм бил полезен отговора.
Всъщност основната особеност при работа с масиви е използването на комбинацията Shift+Ctrl+Enter.
Повече обяснения има на този линк:
https://support.office.com/bg-bg/article/%D0%A3%D0%BA%D0%B0%D0%B7%D0%B0%D0%BD%D0%B8%D1%8F-%D0%B8-%D0%BF%D1%80%D0%B8%D0%BC%D0%B5%D1%80%D0%B8-%D0%B7%D0%B0-%D1%84%D0%BE%D1%80%D0%BC%D1%83%D0%BB%D0%B8-%D0%B7%D0%B0-%D0%BC%D0%B0%D1%81%D0%B8%D0%B2%D0%B8-7d94a64e-3ff3-4686-9372-ecfd5caa57c7
Здравейте, ето от мен вариант с използване на INDEX, COLUMN и ROW, където не се използва array формула, т.е. не се натиска Ctrl+Shift+Enter, а само Enter:
INDEX(диапазон;COLUMN(A1);ROW(A1))
INDEX може да се използва за връщане на елемент от диапазон, избран чрез индексите на номерата на реда и на колоната. Синтаксиса е:
INDEX(диапазон, ред_ном,[колона_ном])
1. Задава се желания диапазон. Тук задължително трябва да се използват абсолютни адреси, за да не се промени областта при копирането на формулата.
2. За номер на ред от диапазона се използва функция Column. Тя връща номера на колоната на зададена препратка. За клетка А1 винаги е 1. Тук задължително трябва да се използва относителен адрес, за да се промени референцията при копирането на формулата.
3. За номер на колона от диапазона се използва функция Row. Тя връща номера на реда на зададена препратка. За А1 е 1. Тук също задължително се използва относителен адрес.
Функцията ще върне грешка #REF!, когато е копирана в по-голяма от зададената област, което ще ни подскаже кога да спрем с копирането.
Аргументите на Column и Row в първата клетка винаги трябва да са А1. Това е малко объркващо, но ние там търсим стойността на клетката от първата колона и първия ред на зададения диапазон.
С VBA ще е възможно да се вижда истински линк към клетка. Ако успея да направя, ще го споделя.
Много благодаря за информацията!
Здравейте, успях да направя макрос.
Как работи:
1. Маркира се областта за копиране
2. Стартира се макроса
3. Когато се появи диалоговия прозорец за въвеждане на първата клетка от областта за пействане се маркира клетка (може и да се напише нейния адрес).
Клетката може да е в същия или друг лист, също така и в друга работна книга.
За пействане в друга работна книга с Excel 2010 особеното е, че тя трябва да се активира през меню View -> Window -> Switch Windows. В Excel 2013 този проблем липсва.
4. Натиска се бутон ОК или клавиш Enter и готово.
Екстри:
- Ако има данни в клетки от областта за пействане се показва съобщение с опция за продължаване или спиране. Избира се желаната опция. Натискане на Enter означава продължаване.
- Ако се маркира повече от една клетка за начало на пействането - съобщение с опция за нов опит. Натискане на Enter означава нов опит.
- При грешка по време на процедурата: съобщение с изброена една вероятна причина - маркирана диаграма.
Ето го и кода:
Sub PasteTransposeLink()
'Копира маркирана предварително област и я пейства
'в избрана от потребителя област
'едновременно като transpose и paste link.
'Excel 2010 - За да се пейстне в работна книга, различна от
'източника, тя трябва да се активира от меню
'View -> Switch Windows (когато се появи прозореца за избор на клетка).
On Error GoTo errorHandling
Dim myRange As Range, myCell As Range
Dim ExtSt As String, Msg As String
Dim NRows As Long, NCol As Long, i As Long, j As Long
Dim Ans As Variant
Dim Counter As Long
'Запомня маркираната преди стартирането на макроса област
Set myRange = Selection
NRows = Selection.Rows.Count
NCol = Selection.Columns.Count
'Ще се върне тук ако е избрана повече от една клетка
'за започване на пействането и е натиснат бутон Yes
'Показва се прозорец, в който да се маркира или напише
'първата клетка от областта, в която ще се пейства
TryAgain:
On Error Resume Next
Set myCell = Application.InputBox _
(Prompt:=vbNewLine & "Въведете адреса или маркирайте първата клетка" _
& " от областта за поставяне." & vbNewLine & vbNewLine, _
Type:=8)
On Error GoTo errorHandling
'Ако се натисне бутон Cancel - изход от процедурата
If myCell Is Nothing Then Exit Sub
'Ако се маркира повече от една клетка - съобщение с опция за нов опит
If myCell.Cells.Count > 1 Then GoTo MultipleEntry
'Активира маркираната клетка
With myCell
.Parent.Parent.Activate
.Parent.Activate
.Activate
End With
'Маркира областта за пействане
Range(myCell, myCell.Offset(NCol - 1, NRows - 1)).Select
'Ако има дори една клетка с данни в областта за пействане
'се показва съобщение с опция за отказ от пействането
Counter = WorksheetFunction.CountA(Selection.Cells)
If Counter > 0 Then
Msg = "В областта за пействане има клетки, " & vbNewLine
Msg = Msg & "които не са празни!" & vbNewLine & vbNewLine
Msg = Msg & "Данните ще бъдат заместени." & vbNewLine & vbNewLine & vbNewLine
Msg = Msg & "Желаете ли да продължите?"
Ans = MsgBox(Msg, vbYesNo + vbExclamation)
If Ans = vbNo Then Exit Sub
End If
'Проверява дали данните ще се пействат в лист с различно име.
'Това ще определи дали в линковете ще се включват имената на листа и на файла
If myCell.Parent.Name = myRange.Parent.Name Then
ExtSt = "False"
Else: ExtSt = "True"
End If
'Зададено е линковете да са с относителни адреси. За абсолютни
'или смесени False се променя на True
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
myCell.Offset(j - 1, i - 1).Formula = "=" & myRange(i, j). _
Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=ExtSt)
Next j
Next i
Exit Sub
MultipleEntry:
Msg = "Трябва да маркирате само една клетка!"
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Желаете ли да опитате отново?"
Ans = MsgBox(Msg & vbNewLine, vbYesNo + vbExclamation)
If Ans = vbYes Then GoTo TryAgain
Exit Sub
errorHandling:
Msg = "Възникна грешка!" & vbNewLine & vbNewLine
Msg = Msg & "Причините могат да бъдат различни." & vbNewLine
Msg = Msg & "Една от тях е маркирана диаграма."
MsgBox Msg, vbCritical
End Sub
Надявам се макроса да е от полза.
Пропуснала съм да тествам пействане в друга работна книга в лист с име като листа източник. Резултата не е желания.
Когато се замени този блок от команди:
'Проверява дали данните ще се пействат в лист с различно име.
'Това ще определи дали в линковете ще се включват имената на листа и на файла
If myCell.Parent.Name = myRange.Parent.Name Then
ExtSt = "False"
Else: ExtSt = "True"
End If
с този:
'Проверява дали данните ще се пействат в лист с различно име.
'Това ще определи дали в линковете ще се включват имената на листа и на файла
If myCell.Parent.Name = myRange.Parent.Name And _
myCell.Parent.Parent.Name = myRange.Parent.Parent.Name Then
ExtSt = "False"
Else: ExtSt = "True"
End If
макроса работи коректно и в този случай.
Защо се хаби излишно време и пространство, което не е в урока, за да видим как някой се опитва да се изпъкне. /защото я няма "циганката да хвали"/.
Много полезен за мен урок. Благодаря! Досега предпочитах ползването на десен бутон на мишката за copy/paste. „Триковете“ са особено полезни и в други случаи, не само в Excel.
Вашият отговор
Научи компютърните програми онлайн от топ експертите на България
Регистрацията в АУЛА ви дава:
20 безплатни урока
Трикове и тънкости за софтуера
Отговори на вашите въпроси
