2 просмотров
Π Π΅ΠΉΡ‚ΠΈΠ½Π³ ΡΡ‚Π°Ρ‚ΡŒΠΈ
1 Π·Π²Π΅Π·Π΄Π°2 Π·Π²Π΅Π·Π΄Ρ‹3 Π·Π²Π΅Π·Π΄Ρ‹4 Π·Π²Π΅Π·Π΄Ρ‹5 Π·Π²Π΅Π·Π΄
Π—Π°Π³Ρ€ΡƒΠ·ΠΊΠ°...

Как Π²Π΅Ρ€Π½ΡƒΡ‚ΡŒ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ ячСйки ΠΊΠ°ΠΆΠ΄Ρ‹Π΅ ΠΏΡΡ‚ΡŒ ΠΈΠ»ΠΈ n-ю строку Π² Excel?

Π‘ΠΊΠΎΠΏΠΈΡ€ΡƒΠΉΡ‚Π΅ ΠΊΠ°ΠΆΠ΄ΡƒΡŽ n-ю строку с ΠΎΠ΄Π½ΠΎΠ³ΠΎ листа Π½Π° Π΄Ρ€ΡƒΠ³ΠΎΠΉ

ЭлСктронная Ρ‚Π°Π±Π»ΠΈΡ†Π° Π² Excel ΠΈΠΌΠ΅Π΅Ρ‚ 700 строк, 1 столбСц. Π― Π·Π°Π±ΠΎΡ‡ΡƒΡΡŒ ΠΎ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ 7-ΠΉ строкС. Π‘Ρ‹Π»ΠΎ Π±Ρ‹ ΡƒΡ‚ΠΎΠΌΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ Π·Π°Ρ…ΠΎΠ΄ΠΈΡ‚ΡŒ ΠΈ ΡƒΠ΄Π°Π»ΡΡ‚ΡŒ 6 строк ΠΌΠ΅ΠΆΠ΄Ρƒ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ ΠΈΠ½Ρ‚Π΅Ρ€Π΅ΡΡƒΡŽΡ‰Π΅ΠΉ мСня строкой. Π§Ρ‚ΠΎΠ±Ρ‹ Ρ€Π΅ΡˆΠΈΡ‚ΡŒ эту ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΡƒ, я создал Π΄Ρ€ΡƒΠ³ΠΎΠΉ Ρ€Π°Π±ΠΎΡ‡ΠΈΠΉ лист, Π² ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΌ я ΡΡΡ‹Π»Π°ΡŽΡΡŒ Π½Π° ΠΊΠ°ΠΆΠ΄ΡƒΡŽ ячСйку.

Π£ мСня Π½Π΅Ρ‚ Π²Ρ€Π΅ΠΌΠ΅Π½ΠΈ Π²Π²ΠΎΠ΄ΠΈΡ‚ΡŒ всС эти Ρ„ΠΎΡ€ΠΌΡƒΠ»Ρ‹. 100 Ρ€Π°Π·. ΠŸΠ΅Ρ€Π²ΠΎΠ½Π°Ρ‡Π°Π»ΡŒΠ½ΠΎ я Π΄ΡƒΠΌΠ°Π», Ρ‡Ρ‚ΠΎ ΠΎΠ½ ΠΏΠΎΠΉΠΌΠ΅Ρ‚, Ρ‡Ρ‚ΠΎ я ΠΏΡ‹Ρ‚Π°ΡŽΡΡŒ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ, Ссли я Π²Ρ‹Π±Π΅Ρ€Ρƒ Ρ‚Ρ€ΠΈ ΠΈ ΠΏΠ΅Ρ€Π΅Ρ‚Π°Ρ‰Ρƒ ΠΏΠΎΠ»Π΅, Π½ΠΎ Π½Π΅ ΠΏΠΎΠ²Π΅Π·Π»ΠΎ.

Π•ΡΡ‚ΡŒ ΠΈΠ΄Π΅ΠΈ, ΠΊΠ°ΠΊ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ Π΅Π³ΠΎ элСгантным/эффСктивным?

ΠŸΠΎΠΌΠ΅ΡΡ‚ΠΈΡ‚Π΅ это Π² А1 вашСго Π½ΠΎΠ²ΠΎΠ³ΠΎ листа:

. ΠΈ ΠΊΠΎΠΏΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ. НачнитС со строки 1 ΠΈΠ»ΠΈ Π΄Ρ€ΡƒΠ³ΠΎΠΉ ячСйки Π² строкС 1, Π·Π°Ρ‚Π΅ΠΌ ΠΈΠ·ΠΌΠ΅Π½ΠΈΡ‚Π΅ ROW() Π½Π° ROW (A1) ΠΈΠ»ΠΈ Π΄Ρ€ΡƒΠ³ΡƒΡŽ ячСйку Π² строкС 1.

Если Π²Ρ‹ Ρ…ΠΎΡ‚ΠΈΡ‚Π΅ ΡΠΊΠΎΠΏΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ n-ΡƒΡŽ строку, Π½ΠΎ нСсколько столбцов, ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠΉΡ‚Π΅ Ρ„ΠΎΡ€ΠΌΡƒΠ»Ρƒ:

Π•Π΅ Ρ‚Π°ΠΊΠΆΠ΅ ΠΌΠΎΠΆΠ½ΠΎ ΡΠΊΠΎΠΏΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ.

Если Π±Ρ‹ я ΠΈΠ·Π²Π»Π΅ΠΊΠ°Π» ΠΊΠ°ΠΆΠ΄ΡƒΡŽ ΡΠ΅Π΄ΡŒΠΌΡƒΡŽ строку, я Π±Ρ‹ «Π²ΡΡ‚Π°Π²ΠΈΠ»» столбСц ΠΏΠ΅Ρ€Π΅Π΄ столбцом «A». Π—Π°Ρ‚Π΅ΠΌ (Ссли строка 1 ΠΈΠΌΠ΅Π΅Ρ‚ строку Π·Π°Π³ΠΎΠ»ΠΎΠ²ΠΊΠ°) я Π±Ρ‹ написал числа 1,2,3,4,5,6,7,7 Π² строкС 2,3,4,5,6,7,8, ΠΈΠ·Π²Π»Π΅ΠΊ 1,2,3,4,5,6,7 ΠΈ вставил этот Π±Π»ΠΎΠΊ Π² ΠΊΠΎΠ½Π΅Ρ† листа (700 строк). Π Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚ Π±ΡƒΠ΄Π΅Ρ‚ ΡΠ»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΌ: 1,23,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7. Π’Π΅ΠΏΠ΅Ρ€ΡŒ Π²Ρ‹ΠΏΠΎΠ»Π½ΠΈΡ‚Π΅ сортировку ΠΏΠΎ Π²ΠΎΠ·Ρ€Π°ΡΡ‚Π°Π½ΠΈΡŽ Π΄Π°Π½Π½Ρ‹Ρ… Π² столбцС «A». ПослС сортировки всС ΠΏΠ΅Ρ€Π²Ρ‹Π΅ Π±ΡƒΠ΄ΡƒΡ‚ ΠΏΠ΅Ρ€Π²Ρ‹ΠΌΠΈ Π² сСрии, Π° всС ΡΠ΅Π΄ΡŒΠΌΡ‹Π΅ — ΡΠ΅Π΄ΡŒΠΌΡ‹ΠΌΠΈ.

Π­Ρ‚ΠΈ ΠΎΡ‚Π²Π΅Ρ‚Ρ‹, Π½Π° ΠΌΠΎΠΉ взгляд, слишком ΠΊΠΎΠ½ΠΊΡ€Π΅Ρ‚Π½Ρ‹. Π—Π΄Π΅ΡΡŒ прСдставлСно ΠΎΠ±Ρ‰Π΅Π΅ объяснСниС с двумя ΠΏΡ€ΠΈΠΌΠ΅Ρ€Π°ΠΌΠΈ ΠΈ двумя ΠΏΠΎΠ΄Ρ…ΠΎΠ΄Π°ΠΌΠΈ.

ΠŸΠΎΠ΄Ρ…ΠΎΠ΄ OFFSET

F FSET Ρ‚Ρ€Π΅Π±ΡƒΠ΅Ρ‚ 3 ΠΎΠ±ΡΠ·Π°Ρ‚Π΅Π»ΡŒΠ½Ρ‹Ρ… Π°Ρ€Π³ΡƒΠΌΠ΅Π½Ρ‚Π°. ΠŸΠ΅Ρ€Π²Ρ‹ΠΉ — это заданная ячСйка, ΠΎΡ‚ ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΉ ΠΌΡ‹ Ρ…ΠΎΡ‚ΠΈΠΌ произвСсти ΠΊΠΎΠΌΠΏΠ΅Π½ΡΠ°Ρ†ΠΈΡŽ. Π‘Π»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠ΅ Π΄Π²Π° ΡƒΠΊΠ°Π·Ρ‹Π²Π°ΡŽΡ‚, сколько строк ΠΈ столбцов ΠΌΡ‹ Ρ…ΠΎΡ‚ΠΈΠΌ ΡΠΌΠ΅ΡΡ‚ΠΈΡ‚ΡŒ (Π²Π»Π΅Π²ΠΎ ΠΈ Π²ΠΏΡ€Π°Π²ΠΎ). OFFNET Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ содСрТимоС ячСйки, ΠΊ ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΉ Π²Π΅Π΄Π΅Ρ‚. OFFSET(A1, 1, 2), Π½Π°ΠΏΡ€ΠΈΠΌΠ΅Ρ€, Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ содСрТимоС ячСйки C2, ΠΏΠΎΡ‚ΠΎΠΌΡƒ Ρ‡Ρ‚ΠΎ A1 — это 1. Π’ΠΎΠ³Π΄Π°, Ссли ΠΌΡ‹ ΠΏΡ€ΠΈΠ±Π°Π²ΠΈΠΌ ΠΊ этому 1, Ρ‚ΠΎ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΠΌ 2. Π§Ρ‚ΠΎ соотвСтствуСт ячСйкС C2.

Π§ΠΈΡ‚Π°ΠΉΡ‚Π΅ Ρ‚Π°ΠΊ ΠΆΠ΅:
Как автоматичСски Π²Π²ΠΎΠ΄ΠΈΡ‚ΡŒ Π΄Π°Ρ‚Ρƒ ΠΏΡ€ΠΈ Π²Π²ΠΎΠ΄Π΅ Π΄Π°Π½Π½Ρ‹Ρ… Π² столбСц?

R OW() ΠΌΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ для Π²ΠΎΠ·Π²Ρ€Π°Ρ‚Π° n-ΠΉ строки ΠΈΠ· Π΄Ρ€ΡƒΠ³ΠΎΠ³ΠΎ столбца. Если эта функция вызываСтся Π±Π΅Π· Π°Ρ€Π³ΡƒΠΌΠ΅Π½Ρ‚ΠΎΠ², ΠΎΠ½Π° Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ Π½ΠΎΠΌΠ΅Ρ€ строки Ρ‚Π΅ΠΊΡƒΡ‰Π΅ΠΉ ячСйки. ΠŸΡ€ΠΈ сочСтании OFFSET ΠΈ ROW ΠΌΠΎΠΆΠ½ΠΎ ΡΠΎΠ·Π΄Π°Ρ‚ΡŒ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ, которая Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ Π»ΡŽΠ±ΡƒΡŽ n-ю ячСйку, добавляя ΠΌΠ½ΠΎΠΆΠΈΡ‚Π΅Π»ΡŒ ΠΊ Π·Π½Π°Ρ‡Π΅Π½ΠΈΡŽ, Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅ΠΌΠΎΠΌΡƒ ROW. НапримСр, OFFSET(A$1,ROW()*3,0) . ΠžΠ±Ρ€Π°Ρ‚ΠΈΡ‚Π΅ Π²Π½ΠΈΠΌΠ°Π½ΠΈΠ΅ Π½Π° использованиС $1 Π² Ρ†Π΅Π»Π΅Π²ΠΎΠΉ ячСйкС. Он Π±ΡƒΠ΄Π΅Ρ‚ смСщСн ΠΎΡ‚Π½ΠΎΡΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ Π΄Ρ€ΡƒΠ³ΠΈΡ… ячССк, эффСктивно увСличивая ΠΌΠ½ΠΎΠΆΠΈΡ‚Π΅Π»ΡŒ Π½Π° 1.

ΠŸΠΎΠ΄Ρ…ΠΎΠ΄ ADDRESS + INDIRECT

ADDRESS ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Π΅Ρ‚ Π΄Π²Π° цСлочислСнных Π²Ρ…ΠΎΠ΄Π½Ρ‹Ρ… сигнала ΠΈ Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ адрСс/имя ячСйки Π² Π²ΠΈΠ΄Π΅ строки. НапримСр, ADDRESS(1,1) Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ «$A$1». INDIRECT ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Π΅Ρ‚ адрСс ячСйки ΠΈ Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ Π΅Π΅ содСрТимоС. НапримСр, INDIRECT(«A1») Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ содСрТимоС ячСйки A1 (Ρ‚Π°ΠΊΠΆΠ΅ ΠΏΡ€ΠΈΠ½ΠΈΠΌΠ°Π΅Ρ‚ записи $). Если ΠΌΡ‹ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌ ROW Π²Π½ΡƒΡ‚Ρ€ΠΈ ADDRESS с ΠΌΠ½ΠΎΠΆΠΈΡ‚Π΅Π»Π΅ΠΌ, ΠΌΡ‹ ΠΌΠΎΠΆΠ΅ΠΌ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ адрСс ΠΊΠ°ΠΆΠ΄ΠΎΠΉ n-ΠΎΠΉ ячСйки. НапримСр, ADDRESS(ROW(), 1) Π² строкС 1 Π²Π΅Ρ€Π½Π΅Ρ‚ «$A$1», строка 2 Π²Π΅Ρ€Π½Π΅Ρ‚ «$A$1», строка 2 Π²Π΅Ρ€Π½Π΅Ρ‚ «$A$2». строка 2 Π²Π΅Ρ€Π½Π΅Ρ‚ «$A$2» ΠΈ Ρ‚Π°ΠΊ Π΄Π°Π»Π΅Π΅. Π’Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ, Ссли ΠΌΡ‹ помСстим это Π² INDIRECT, ΠΌΡ‹ смоТСм ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ содСрТимоС ΠΊΠ°ΠΆΠ΄ΠΎΠΉ n-ΠΎΠΉ ячСйки. НапримСр, INDIRECT(ADDRESS(1*Row()*3,1)) ΠΏΡ€ΠΈ пСрСтаскивании Π²Π½ΠΈΠ· Π²Π΅Ρ€Π½Π΅Ρ‚ содСрТимоС ΠΊΠ°ΠΆΠ΄ΠΎΠΉ Ρ‚Ρ€Π΅Ρ‚ΡŒΠ΅ΠΉ ячСйки Π² ΠΏΠ΅Ρ€Π²ΠΎΠΌ столбцС.

ΠŸΡ€ΠΈΠΌΠ΅Ρ€

Рассмотрим ΡΠ»Π΅Π΄ΡƒΡŽΡ‰ΠΈΠΉ ΡΠΊΡ€ΠΈΠ½ΡˆΠΎΡ‚ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹. Π—Π°Π³ΠΎΠ»ΠΎΠ²ΠΊΠΈ (пСрвая строка) содСрТат Π²Ρ‹Π·ΠΎΠ², ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅ΠΌΡ‹ΠΉ Π² строках Π½ΠΈΠΆΠ΅. Π²Π²Π΅Π΄ΠΈΡ‚Π΅ описаниС изобраТСния здСсь Π‘Ρ‚ΠΎΠ»Π±Π΅Ρ† A содСрТит Π΄Π°Π½Π½Ρ‹Π΅ нашСго ΠΏΡ€ΠΈΠΌΠ΅Ρ€Π°. Π’ этом случаС это просто ΠΏΠΎΠ»ΠΎΠΆΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹Π΅ Ρ†Π΅Π»Ρ‹Π΅ числа (подсчСт продолТаСтся Π·Π° ΠΏΡ€Π΅Π΄Π΅Π»Π°ΠΌΠΈ ΠΎΡ‚ΠΎΠ±Ρ€Π°ΠΆΠ°Π΅ΠΌΠΎΠΉ области). Π­Ρ‚ΠΎ Ρ‚Π΅ значСния, ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΌΡ‹ Ρ…ΠΎΡ‚ΠΈΠΌ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ ΠΊΠ°ΠΆΠ΄Ρ‹ΠΉ Ρ‚Ρ€Π΅Ρ‚ΠΈΠΉ, Ρ‚ΠΎ Π΅ΡΡ‚ΡŒ ΠΌΡ‹ Ρ…ΠΎΡ‚ΠΈΠΌ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ 1, 4, 7, 10 ΠΈ Ρ‚.Π΄.

ΠœΡ‹ Π·Π°Π±Ρ‹Π»ΠΈ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ $, ΠΊΠΎΠ³Π΄Π° использовали ΠΏΠΎΠ΄Ρ…ΠΎΠ΄ OFFSET Π² столбцС B. Как Π²Ρ‹ ΠΌΠΎΠΆΠ΅Ρ‚Π΅ Π²ΠΈΠ΄Π΅Ρ‚ΡŒ, умноТая Π½Π° 3, ΠΌΡ‹ ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ ΠΊΠ°ΠΆΠ΄ΡƒΡŽ Ρ‡Π΅Ρ‚Π²Π΅Ρ€Ρ‚ΡƒΡŽ строчку.

Колонка C содСрТит Π½Π΅ΠΏΡ€Π°Π²ΠΈΠ»ΡŒΠ½ΡƒΡŽ ΠΏΠΎΠΏΡ‹Ρ‚ΠΊΡƒ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ ΠΏΠΎΠ΄Ρ…ΠΎΠ΄ OFFSET, ΠΊΠΎΠ³Π΄Π° ΠΌΡ‹ ΠΏΠΎΠΌΠ½ΠΈΠΌ, Ρ‡Ρ‚ΠΎ Π½ΡƒΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ $, Π½ΠΎ Π·Π°Π±Ρ‹Π²Π°Π΅ΠΌ Π²Ρ‹Ρ‡Π΅ΡΡ‚ΡŒ. Π’Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ, хотя ΠΌΡ‹ ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ ΠΊΠ°ΠΆΠ΄ΠΎΠ΅ Ρ‚Ρ€Π΅Ρ‚ΡŒΠ΅ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅, ΠΌΡ‹ пропускаСм Π½Π΅ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ (1 ΠΈ 4).

Π§ΠΈΡ‚Π°ΠΉΡ‚Π΅ Ρ‚Π°ΠΊ ΠΆΠ΅:
Как Π²Π΅Ρ€Π½ΡƒΡ‚ΡŒ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅, Ссли Π΄Π°Π½Π½ΠΎΠ΅ Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ сущСствуСт Π² ΠΎΠΏΡ€Π΅Π΄Π΅Π»Π΅Π½Π½ΠΎΠΌ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π΅ Π² Excel?

Π‘Ρ‚ΠΎΠ»Π±Π΅Ρ† D содСрТит ΠΏΡ€Π°Π²ΠΈΠ»ΡŒΠ½ΡƒΡŽ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ, ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΡ ΠΏΠΎΠ΄Ρ…ΠΎΠ΄ OFFSET.

Колонка E содСрТит Π½Π΅ΠΏΡ€Π°Π²ΠΈΠ»ΡŒΠ½ΡƒΡŽ ΠΏΠΎΠΏΡ‹Ρ‚ΠΊΡƒ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ ΠΏΠΎΠ΄Ρ…ΠΎΠ΄ DIRECT + INDRECT, Π½ΠΎ ΠΌΡ‹ Π·Π°Π±Ρ‹Π»ΠΈ Π²Ρ‹Ρ‡Π΅ΡΡ‚ΡŒ. Π’ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Π΅ ΠΌΡ‹ сначала пропустили Π½Π΅ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ряды. Π’Π° ΠΆΠ΅ ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ°, Ρ‡Ρ‚ΠΎ ΠΈ Π² ΠΊΠΎΠ»ΠΎΠ½ΠΊΠ΅ C.

ΠœΠ΅Ρ‚ΠΎΠ΄ ADDRESS + INDRECT описываСт ΠΏΡ€Π°Π²ΠΈΠ»ΡŒΠ½ΡƒΡŽ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ Π² ΠΊΠΎΠ»ΠΎΠ½ΠΊΠ΅ F.

Π’Ρ€ΡŽΠΊ β„–69. Как Π² Excel 2010 ΠΏΡ€ΠΎΡΡƒΠΌΠΌΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ ΠΊΠ°ΠΆΠ΄ΡƒΡŽ Π²Ρ‚ΠΎΡ€ΡƒΡŽ, Ρ‚Ρ€Π΅Ρ‚ΡŒΡŽ ΠΈΠ»ΠΈ n-ΡƒΡŽ строку ΠΈΠ»ΠΈ ячСйку

Часто Π±Ρ‹Π²Π°Π΅Ρ‚ Ρ‚Π°ΠΊ, Ρ‡Ρ‚ΠΎ каТдая вторая, Ρ‚Ρ€Π΅Ρ‚ΡŒΡ, чСтвСртая ΠΈ Ρ‚.Π΄. ячСйка Π² элСктронной Ρ‚Π°Π±Π»ΠΈΡ†Π΅ нуТдаСтся Π² Π΄ΠΎΠ±Π°Π²Π»Π΅Π½ΠΈΠΈ. Π’Π΅ΠΏΠ΅Ρ€ΡŒ, благодаря ΡΠ»Π΅Π΄ΡƒΡŽΡ‰Π΅ΠΌΡƒ совСту, это Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎ.

НСвозмоТно ΡΡƒΠΌΠΌΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ ΠΊΠ°ΠΆΠ΄ΡƒΡŽ ячСйку Π² Excel с ΠΏΠΎΠΌΠΎΡ‰ΡŒΡŽ стандартной Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ. Для выполнСния этой Π·Π°Π΄Π°Ρ‡ΠΈ ΠΌΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ нСсколько Ρ€Π°Π·Π»ΠΈΡ‡Π½Ρ‹Ρ… способов. ВсС эти способы основаны Π½Π° функциях БРЗНАЧ ΠΈ ΠœΠžΠ”.

Ѐункция БВРОКА (ROW) Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ Π½ΠΎΠΌΠ΅Ρ€ строки для Π·Π°Π΄Π°Π½Π½ΠΎΠΉ ссылки Π½Π° ячСйку: ROW(reference), Π² русской вСрсии Excel БВРОКА(ссылка).
Ѐункция ОБВАВ (MOD) Π²ΠΎΠ·Π²Ρ€Π°Ρ‰Π°Π΅Ρ‚ остаток ΠΎΡ‚ дСлСния числа Π½Π° Π΄Π΅Π»ΠΈΡ‚Π΅Π»ΡŒ: MOD(number;divisor), Π² русской вСрсии Excel ОБВАВ(число;Π΄Π΅Π»ΠΈΡ‚Π΅Π»ΡŒ).

ΠŸΠΎΠΌΠ΅ΡΡ‚ΠΈΡ‚Π΅ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ ROW Π² Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ MOD (для ΠΏΠ΅Ρ€Π΅Π΄Π°Ρ‡ΠΈ числового Π°Ρ€Π³ΡƒΠΌΠ΅Π½Ρ‚Π°), Ρ€Π°Π·Π΄Π΅Π»ΠΈΡ‚Π΅ Π½Π° 2 (для добавлСния ΠΊΠ°ΠΆΠ΄ΠΎΠΉ Π²Ρ‚ΠΎΡ€ΠΎΠΉ ячСйки) ΠΈ ΠΏΡ€ΠΎΠ²Π΅Ρ€ΡŒΡ‚Π΅, Ρ€Π°Π²Π΅Π½ Π»ΠΈ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚ Π½ΡƒΠ»ΡŽ. Если Π΄Π°, Ρ‚ΠΎ ячСйка добавляСтся. Π­Ρ‚ΠΈ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ ΠΌΠΎΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ ΠΏΠΎ-Ρ€Π°Π·Π½ΠΎΠΌΡƒ, ΠΏΡ€ΠΈΡ‡Π΅ΠΌ Π½Π΅ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΈΠ· Π½ΠΈΡ… Π΄Π°ΡŽΡ‚ Π»ΡƒΡ‡ΡˆΠΈΠ΅ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹, Ρ‡Π΅ΠΌ Π΄Ρ€ΡƒΠ³ΠΈΠ΅. НапримСр, табличная Ρ„ΠΎΡ€ΠΌΡƒΠ»Π° для слоТСния всСх ΠΎΡΡ‚Π°Π»ΡŒΠ½Ρ‹Ρ… ячССк Π² Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π΅ $A$1:$A$100 Π±ΡƒΠ΄Π΅Ρ‚ Π²Ρ‹Π³Π»ΡΠ΄Π΅Ρ‚ΡŒ Ρ‚Π°ΠΊ: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$A$1:$A$500;0)) Π’ русском Excel =SUM(IF(ROW($A$1:$A$500);2)=0;$A$1:$A$500;0)) .

Π’ΠΎΡ‚ Π΅Ρ‰Π΅ ΠΎΠ΄Π½Π° Ρ„ΠΎΡ€ΠΌΡƒΠ»Π°, которая прСдставляСт собой Π½Π΅ΠΌΠ½ΠΎΠ³ΠΎ Π»ΡƒΡ‡ΡˆΠΈΠΉ Π²Π°Ρ€ΠΈΠ°Π½Ρ‚: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) Π’ русском Excel =SUMMPRODUCT((ROW($A$1:$A$500);2)=0)*($A$1:$A$500)) .

Однако ΠΎΠ±Ρ€Π°Ρ‚ΠΈΡ‚Π΅ Π²Π½ΠΈΠΌΠ°Π½ΠΈΠ΅, Ρ‡Ρ‚ΠΎ эта Ρ„ΠΎΡ€ΠΌΡƒΠ»Π° Π²Π΅Ρ€Π½Π΅Ρ‚ #Π—Π½Π°Ρ‡Π΅Π½ΠΈΠ΅! (#Π—Π½Π°Ρ‡Π΅Π½ΠΈΠ΅!), Ссли ΠΊΠ°ΠΊΠΈΠ΅-Π»ΠΈΠ±ΠΎ ячСйки Π² Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π΅ содСрТат тСкст вмСсто чисСл. Π­Ρ‚Π° Ρ„ΠΎΡ€ΠΌΡƒΠ»Π°, хотя ΠΈ Π½Π΅ являСтся Ρ‚Π°Π±Π»ΠΈΡ‡Π½ΠΎΠΉ Ρ„ΠΎΡ€ΠΌΡƒΠ»ΠΎΠΉ, Ρ‚Π°ΠΊΠΆΠ΅ замСдляСт Ρ€Π°Π±ΠΎΡ‚Ρƒ Excel, Ссли ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ слишком часто ΠΈΠ»ΠΈ Ссли ΠΊΠ°ΠΆΠ΄Ρ‹ΠΉ Ρ€Π°Π· ссылаСтся Π½Π° большой Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½.

Π§ΠΈΡ‚Π°ΠΉΡ‚Π΅ Ρ‚Π°ΠΊ ΠΆΠ΅:
Как Π²Π΅Ρ€Π½ΡƒΡ‚ΡŒ ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰ΠΈΠΉ Ρ€Π°Π±ΠΎΡ‡ΠΈΠΉ ΠΈΠ»ΠΈ Ρ€Π°Π±ΠΎΡ‡ΠΈΠΉ дСнь Π² Excel?

Π₯ΠΎΡ€ΠΎΡˆΠ°Ρ Π½ΠΎΠ²ΠΎΡΡ‚ΡŒ Π·Π°ΠΊΠ»ΡŽΡ‡Π°Π΅Ρ‚ΡΡ Π² Ρ‚ΠΎΠΌ, Ρ‡Ρ‚ΠΎ сущСствуСт Π±ΠΎΠ»Π΅Π΅ эффСктивная ΠΈ гибкая Π°Π»ΡŒΡ‚Π΅Ρ€Π½Π°Ρ‚ΠΈΠ²Π°. Она Ρ‚Ρ€Π΅Π±ΡƒΠ΅Ρ‚ использования Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ DSUM. Π’ этом ΠΏΡ€ΠΈΠΌΠ΅Ρ€Π΅ ΠΌΡ‹ использовали Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ A1:A500 Π² качСствС Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π°, Π² ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠΌ Π½Π΅ΠΎΠ±Ρ…ΠΎΠ΄ΠΈΠΌΠΎ ΠΏΡ€ΠΎΡΡƒΠΌΠΌΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ ΠΊΠ°ΠΆΠ΄ΡƒΡŽ n-ΡƒΡŽ ячСйку.

Π’ ячСйку Π•1 Π²Π²Π΅Π΄ΠΈΡ‚Π΅ слово Criteria. Π’ ячСйку Π•2 Π²Π²Π΅Π΄ΠΈΡ‚Π΅ ΡΠ»Π΅Π΄ΡƒΡŽΡ‰ΡƒΡŽ Ρ„ΠΎΡ€ΠΌΡƒΠ»Ρƒ: =MOD(ROW(A2)-$C$2-1;$C$2)=0 , Π² русской вСрсии Excel =ОБВАВ(БВРОКА(А2)-$Π‘$2-1;$Π‘$2)=0 . Π’Ρ‹Π΄Π΅Π»ΠΈΡ‚Π΅ ячСйку Π‘2 ΠΈ Π²Ρ‹Π±Π΅Ρ€ΠΈΡ‚Π΅ ΠΊΠΎΠΌΠ°Π½Π΄Ρƒ Π”Π°Π½Π½Ρ‹Π΅ → ΠŸΡ€ΠΎΠ²Π΅Ρ€ΠΊΠ° (Data → Validation).

Π’ ΠΏΠΎΠ»Π΅ Π’ΠΈΠΏ Π΄Π°Π½Π½Ρ‹Ρ… (Allow) Π²Ρ‹Π±Π΅Ρ€ΠΈΡ‚Π΅ ΠΏΡƒΠ½ΠΊΡ‚ Бписок (List), Π° Π² ΠΏΠΎΠ»Π΅ Π˜ΡΡ‚ΠΎΡ‡Π½ΠΈΠΊ (Source) Π²Π²Π΅Π΄ΠΈΡ‚Π΅ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Π£Π΄ΠΎΡΡ‚ΠΎΠ²Π΅Ρ€ΡŒΡ‚Π΅ΡΡŒ, Ρ‡Ρ‚ΠΎ установлСн Ρ„Π»Π°ΠΆΠΎΠΊ Бписок допустимых Π·Π½Π°Ρ‡Π΅Π½ΠΈΠΉ (In-Cell), ΠΈ Ρ‰Π΅Π»ΠΊΠ½ΠΈΡ‚Π΅ Π½Π° ΠΊΠ½ΠΎΠΏΠΊΠ΅ ОК. Π’ ячСйкС Π‘1 Π²Π²Π΅Π΄ΠΈΡ‚Π΅ тСкст SUM every…. Π’ любой ячСйкС, ΠΊΡ€ΠΎΠΌΠ΅ строки 1, Π²Π²Π΅Π΄ΠΈΡ‚Π΅ ΡΠ»Π΅Π΄ΡƒΡŽΡ‰ΡƒΡŽ Ρ„ΠΎΡ€ΠΌΡƒΠ»Ρƒ: =DSUM($A:$A;1;$E$1:$E$2) , Π² русской вСрсии Excel =Π‘Π”Π‘Π£ΠœΠœ($А:$А;1;$Π•$1:$Π•$2) .

Π’ ячСйкС нСпосрСдствСнно Π½Π°Π΄ Ρ‚ΠΎΠΉ, Π³Π΄Π΅ Π²Ρ‹ Π²Π²Π΅Π»ΠΈ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ Π‘Π”Π‘Π£ΠœΠœ (DSUM), Π²Π²Π΅Π΄ΠΈΡ‚Π΅ тСкст =»Summing Every» & $Π‘$2 & CHOOSE($C$2;»st»;»nd»;»rd»;»th»;»th»;»th»;»th»;»th»;»th»;»th») & «Cell» . Π’Π΅ΠΏΠ΅Ρ€ΡŒ ΠΎΡΡ‚Π°Π»ΠΎΡΡŒ Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π²Ρ‹Π±Ρ€Π°Ρ‚ΡŒ Π½ΡƒΠΆΠ½ΠΎΠ΅ число Π² ячСйкС Π‘2, Π° ΠΎΡΡ‚Π°Π»ΡŒΠ½ΠΎΠ΅ сдСлаСт функция Π‘Π”Π‘Π£ΠœΠœ (DSUM).

D SUM суммируСт Π΄Π°Π½Π½Ρ‹Π΅ Π·Π° ΠΈΠ½Ρ‚Π΅Ρ€Π²Π°Π», ΡƒΠΊΠ°Π·Π°Π½Π½Ρ‹ΠΉ ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»Π΅ΠΌ. Ѐункция DSUM Π½Π°ΠΌΠ½ΠΎΠ³ΠΎ эффСктивнСС, Ρ‡Π΅ΠΌ Ρ„ΠΎΡ€ΠΌΡƒΠ»Π° массива ΠΈΠ»ΠΈ функция SUMPRODUCT. Π₯отя Π½Π° Π΅Π΅ настройку трСбуСтся Π½Π΅ΠΊΠΎΡ‚ΠΎΡ€ΠΎΠ΅ врСмя, ΠΎΠ½Π° являСтся ΠΏΡ€ΠΈΠΌΠ΅Ρ€ΠΎΠΌ Ρ‚ΠΎΠ³ΠΎ, ΠΊΠ°ΠΊ тяТСлыС Ρ‚Ρ€Π΅Π½ΠΈΡ€ΠΎΠ²ΠΊΠΈ становятся Π»Π΅Π³ΠΊΠΈΠΌΠΈ Π² бою.

голоса
Π Π΅ΠΉΡ‚ΠΈΠ½Π³ ΡΡ‚Π°Ρ‚ΡŒΠΈ
Бсылка Π½Π° ΠΎΡΠ½ΠΎΠ²Π½ΡƒΡŽ ΠΏΡƒΠ±Π»ΠΈΠΊΠ°Ρ†ΠΈΡŽ
Adblock
detector