Мазмұны
Кеше марафонда 30 күнде 30 Excel функциясы функцияның көмегімен диапазондағы бағандардың санын санадық COLUMNS (NUMBERCOLUMN), енді сұранысқа ие нәрсенің уақыты келді.
Марафонның 13-ші күні біз функцияны зерттеуге арнаймыз ТРАНСПОЗ (TRANSP). Бұл функцияның көмегімен тік аймақтарды көлденеңге және керісінше айналдыру арқылы деректеріңізді айналдыра аласыз. Сізге мұндай қажеттілік бар ма? Мұны арнайы кірістіру арқылы жасай аласыз ба? Басқа функциялар мұны істей ала ма?
Сонымен, функция туралы ақпарат пен мысалдарға жүгінейік ТРАНСПОЗ (TRANSP). Егер сізде қосымша ақпарат немесе мысалдар болса, оларды түсініктемелерде бөлісіңіз.
13-функция: TRANSPOSE
функция ТРАНСПОЗ (TRANSPOSE) ұяшықтардың көлденең ауқымын тік ауқым ретінде қайтарады немесе керісінше.
TRANSPOSE функциясын қалай пайдалануға болады?
функция ТРАНСПОЗ (TRANSP) деректердің бағытын өзгерте алады, сонымен қатар басқа функциялармен бірге жұмыс істей алады:
- Деректердің көлденең орналасуын тікке өзгертіңіз.
- Соңғы жылдардағы ең жақсы жалпы жалақыны көрсетіңіз.
Бастапқы деректерге сілтеме жасамай, деректер бағытын өзгерту үшін:
- пайдалану Арнайы қойыңыз (Арнайы паста) > Ауыстыру (Транспозиция).
Синтаксис TRANSPOSE (TRANSP)
функция ТРАНСПОЗ (TRANSPOSE) келесі синтаксиске ие:
TRANSPOSE(array)
ТРАНСП(массив)
- массив (массив) – транспозицияланатын ұяшықтардың массиві немесе ауқымы.
TRANSPOSE (TRANSPOSE) тұзақтары
- функция ТРАНСПОЗ (TRANSPOSE) басу арқылы массив формуласы ретінде енгізілуі керек Ctrl + Shift + Enter пернелер тіркесімі.
- Функция арқылы түрлендіру нәтижесінде болатын ауқым ТРАНСПОЗ (TRANSPOSE) бастапқы ауқымда сәйкесінше бағандар мен жолдар болатындай жолдар мен бағандар саны бірдей болуы керек.
1-мысал: Көлденең деректерді тік деректерге айналдыру
Егер деректер Excel парағында көлденең болса, функцияны қолдануға болады ТРАНСПОЗ (TRANSPOSE) оларды тік күйге түрлендіру үшін, бірақ парақта басқа жерде. Мысалы, қорытынды көрсеткіштер кестесінде тік орналасу ыңғайлырақ болар еді. Функцияны пайдалану ТРАНСПОЗ (TRANSPOSE), бастапқы көлденең деректерге оның орнын өзгертпестен сілтеме жасай аласыз.
Көлденең диапазонды ауыстыру үшін 2 × 4 тік диапазонға 4 × 2:
- Алынған тік ауқымды орналастырғыңыз келетін 8 ұяшықты таңдаңыз. Біздің мысалда бұл B4:C7 ұяшықтары болады.
- Келесі формуланы енгізіп, оны басу арқылы массив формуласына айналдырыңыз Ctrl + Shift + Enter пернелер тіркесімі.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Жиым формуласының енгізілгенін көрсету үшін формуланың басына және соңына бұйра жақшалар автоматты түрде қосылады.
Орнына ТРАНСПОЗ (TRANSPOSE), Деректерді түрлендіру үшін басқа функцияны пайдалануға болады, мысалы, INDEX (ИНДЕКС). Ол массив формуласын енгізуді талап етпейді және формуланы жасау кезінде мақсатты аймақтағы барлық ұяшықтарды таңдаудың қажеті жоқ.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
2-мысал: Сілтемесіз бағдарды өзгерту
Бастапқы деректерге сілтеме жасамай деректеріңіздің бағытын өзгерткіңіз келсе, Арнайы қоюды пайдалана аласыз:
- Бастапқы деректерді таңдап, оны көшіріңіз.
- Нәтижені орналастырғыңыз келетін аймақтың жоғарғы сол жақ ұяшығын таңдаңыз.
- Қосымша қойындысында Home (Басты) пәрменнің ашылмалы мәзірін басыңыз паста (Кірістіру).
- таңдау Ауыстыру (Транспозиция).
- Бастапқы деректерді жою (міндетті емес).
3-мысал: Өткен жылдардағы ең жақсы жалпы жалақы
функция ТРАНСПОЗ (TRANSP) осы керемет формуладағы сияқты басқа мүмкіндіктермен бірге пайдалануға болады. Оны Excel жаңалықтар блогында Харлан Гроув соңғы 5 жылдағы ең жақсы жалпы жалақыны есептеу туралы пікірталаста жариялады (қатарынан!).
=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Как можно понять по фигурным скобкам в строке формул – бұл формула массива. Ячейка A5 названа нөмір и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 немесе 0) умножаются на значения ячеек, чтобы получить суммарный объём заработной платы.
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения үшін каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Ето более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!
