Как сделать ранг в excel

Функция ранг в excel примеры

​Смотрите также​ ранги?​MCH​ 1,2. 7,8,8,10,11,11​ 3(3) . 4(4)​ что является средним​ решить задачу, применим​ список отсортирован в​ EXCEL использует понятие​ файл примера столбец​ заголовком (см. рисунок),​Формула​ Этот коэффициент может​ Обязательный. Массив чисел​ функция РАНГ.СР(число;ссылка;[порядок]) Если несколько значений​Функции НАИБОЛЬШИЙ() и РАНГ()​ ранг также не​Функция РАНГ(), английский вариант​В примере условным​: Пример неудачный, не​т.к. Вам необходимо​ . 5(5) .​ для 4, 5​

​ функцию ЕСЛИ (=ЕСЛИ(A2​

​ порядке убывания); аргумент​​ процентиль (функция ПРОЦЕНТИЛЬ.ВКЛ()​

​ L).​​ то нужно установить​Описание​

​ применяться в случаях,​​ или ссылка на​ имеют одинаковый ранг,​ являются взаимодополняющими в​ присваивается, функция воспринимает​ RANK(), возвращает ранг​ форматированием выделил повторы​ понятно что нужно.​ получить средние ранги​ 6(6) . 7(7)​ и 6.​​Серым цветом выделены значения,​​ равен любому неравному​ и Условное форматирование)​Столбец Ранг. Это столбец​​ галочку в поле​​Результат​ когда ранг вычисляется​ список чисел. Нечисловые​ возвращается среднее. ​

​ том смысле, что​ их как текст.​ числа в списке​ красным.​ У Вас заполнено​ для повторяющихся значений,​

​ . 8(8,5) .​​Сравним работу двух функций:​ которые соответствуют заданному​

• ​ нулю числу –​ и понятие персентиль​ рангов значений. Максимальному​ Метки в первой​=РАНГ.РВ(A2;A2:A6;1)​ и по убыванию​​ значения в ссылке​​Для построения некоторых сложных​
• ​ записав формулу =НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11))​Порядок​ чисел. Ранг числа​Serge_007​ не 20 чисел​​ то их можно​​ 8(8,5) . 9(10)​​Напомним, что эти две​​ условию. Для чисел,​

Определяем ранг в списке без повторов

​ номер ранжирования 1​ (надстройка Пакет анализа).​​ значению будет присвоен​​ строке. Указывать заголовки​Ранг числа 7 в​ (аргумент «порядок» имеет​​ игнорируются.​​ формул массива может​

​ мы получим тот​​— число, определяющее​​ — это его​: Это из-за проблем​ из 35, а​ получить по формуле​ . 10 (11,5)​ функции работают только​ которые больше 30,​

​ присваивается минимальному числу​

​ В MS WORD​​ ранг=1. Подробнее см.​​ удобно, когда анализируется​ списке, содержащемся в​ нулевое значение или​Порядок​ потребоваться массив рангов,​

​ же исходный массив​ способ упорядочения.​ величина относительно других​ с округлением​

Связь функций НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ() и РАНГ()

​ 11 из 12​ (РАНГмин + РАНГмакс)​ . 10 (11,5)​ в Excel 2010​ выводится пустая строка.​ в списке (как​​ правильным считается термин​​ статью про функцию​

Определяем ранг в списке с повторами

​ сразу несколько массивов​ диапазоне A2:A6. Так​ опущен), и по​ Необязательный. Число, определяющее​ т.е. тот же​A7:A11​Если порядок равен 0​ значений в списке.​Самый простой способ​ и ондно текстовое​ / 2,​Формулы рассчёта: складываем​ и выше. В​​ будто список отсортирован​ перцентиль.​ РАНГ(). Этот столбец​ данных. В нашем​

​ как значение аргумента​ возрастанию (значение аргумента​ способ упорядочения.​ набор рангов, но​.​ (нулю) или опущен,​ Например, в массиве​

​ решения во вложении​ значение.​

​соответственно РАНГмакс =​ по порядку ранги​ более ранних версиях​​В версиях Excel, начиная​​ в порядке возрастания).​

​Для ранжирования данных в​​ можно сформировать с​ случае это не​ «Порядок» (1) не​ «порядок» не равно​Если значение аргумента «порядок»​ в одной ячейке.​Если список содержит повторы,​ то MS EXCEL​ <10;20;5>число 5​Fertion​Смотрите файл, может​ РАНГмин + (СЧЕТ-1),​ повторяющихся значений (8+9)=17​ для этих целей​

Массив рангов

​ с 2010 года,​Определим ранжирование чисел в​ Excel применяются статистические​ помощью формулы =РАНГ.РВ(I7;$B$7:$B$32).​ принципиально, т.к. у​ равно нулю, список​

​ нулю).​ равно 0 (нулю)​Как видно из картинки​​ то повторяющимся значениям​​ присваивает ранг=1 максимальному​​ будет иметь ранг​​: Благодарю за помощь,​ что-нибудь пригодится​в итоге получается​ и делим на​

​ можно задействовать формулу​ появилась функция РАНГ.РВ.​ списке без повторов:​

​ функции РАНГ, РАНГ.РВ,​Столбец Процент. Столбец содержит​ нас только 1​ сортируется от наименьшего​Поправочный коэффициент для связанных​ или опущено, ранг​

РАНГ.РВ (функция РАНГ.РВ)

​ выше, значения из​ (выделено цветом) будет​ числу, меньшим значениям​​ 1, т.к. это​​ помогло.)​

Описание

​Guest​ формула: (РАНГ +​ колличество повторяющихся значений​ массива.​ Это абсолютный аналог​Аргумент, определяющий способ упорядочения​ РАНГ.СР. Все они​ процентиль-ранг (percentile rank),​ массив. Можно указать​

​ к наибольшему.​ рангов = [СЧЕТ(ссылка)​ числа определяется в​

Синтаксис

​ присвоен одинаковый ранг​ присваиваются б​

​ наименьшее число, число​​Dubick​: Ваш пример-это как​

​ РАНГ + СЧЕТ​​ 17/2=8,5 следующий за​Скачать примеры функции ранжирования​ предыдущей функции. Синтаксис​ чисел, равен «0».​ возвращают номер числа​

​ который служит оценкой​​ ссылку на диапазон​5​

Замечания

​ + 1 –​ Excel так, как​В60:В67​ (максимальный). Наличие повторяющихся​о​ 10 — ранг​: Есть такая задача:​ раз то, что​

​ — 1)/2, упростив​ ними ранг идёт​ РАНГ в Excel.​ такой же. Буквы​ Следовательно, в данной​ в ранжированном списке​ относительного положения значения​ только с числами​

​=РАНГ.РВ(A6;A2:A6)​ РАНГ.РВ(число, ссылка, 0)​ если бы ссылка​и в ячейке​ чисел влияет на​льшие ранги.​ 2, а 20​есть порядок чисел:​ и нужно было!​ ее математически получилась​ 10.​Таким образом, все выше​ «РВ» в названии​ функции номера присваивались​ числовых значений. Рассмотрим​ в массиве. Для​

​ и не ставить​Ранг числа 2 в​ – РАНГ.РВ(число, ссылка,​ была списком, отсортированным​D60​ ранги последующих чисел.​Если порядок — любое​ — ранг 3​ 10; 4; 8;​ Текстовое n. 35 я​ вышеуказанная формула​Какие мысли?​ описанные примеры позволяют​ указывают на то,​ значениям от большего​ подробнее синтаксис, примеры.​ заданного значения вычисляется​ галочку.​ том же списке.​ 1)]/2.​ по убыванию.​совпадают. Такой массив​

​ Например, если в​ ненулевое число, то​ (это ранг по​ 12; 4; 5;​ указывал последовательность до​Guest​

​Guest​ автоматизировать работу по​ что при обнаружении​ к меньшему. Максимальному​Функция используется при ранжировании​ сколько значений в​Ссылку на выходной интервал​ Так как значение​В примере, содержащемся в​Если значение аргумента «порядок»​ можно получить с​ списке целых чисел,​ то MS EXCEL​ возрастанию, когда наименьшему​ 17; 19; 7​ 35! Мне и​: Спасибо, очень доступно​

Пример

​: функция РАНГ​ ранжированию данных и​ формулой одинаковых значений​ числу 87 присвоен​ в перечне чисел.​ массиве меньше или​ укажем на текущем​ аргумента «Порядок» не​ книге, РАНГ.РВ(A3,A2:A6,1) равен​ — любое число,​ помощью формулы =РАНГ(A60:A67;A60:A67) или с​ отсортированных по возрастанию,​ присваивает ранг=1 минимальному​

​ значению присваивается ранг​

​Функцией РАНГ.РВ их​

​ нужно было, чтоб​

​ составлению рейтинга значений​

​ функция вернет высший​

​ То есть позволяет​

​ равно ему. Точнее​

​ листе, например, в​ указано, список сортируется​ 3. Поправочный коэффициент​ кроме нуля, то​ помощью формулы =СЧЁТЕСЛИ(A60:A67;»>»&A60:A67)+1​ дважды встречается число​ числу, б​ 1). Если список​

​Guest​: Большое спасибо, суть​ без применения сортировки.​ номер ранжирования (то​В третьем столбце приведена​ узнать величину числа​ — какой процент​

​ по умолчанию — от​

​ равен (5 +​ ранг числа определяется​

​Ранги по возрастанию можно​

Ранг и Персентиль в надстройке Пакет анализа MS EXCEL

​ 10, имеющее ранг​о​ отсортировать, то ранг​

​ 1; 5; 7;​ «0» как значения​: Начал вставлять и​ понятна, всё оказалось​

​Infix_khv​​ есть первого обнаруженного​​ формула с рангом​ относительно других числовых​

​ значений массива меньше​D6​ наибольшего к наименьшему.​ 1 – 2​ в Excel так,​ получить с помощью​

​ 5, число 11​​льшим значениям присваиваются б​ числа будет его​ 1; 3; 8;​ !​ снова столкнулся с​

​ проше чем я​

​: Доброго времени суток,​ элемента в перечне​ по возрастанию.​ значений. Если отсортировать​ или равен ему.​(это будет левый​4​ – 3)/2 =​ как если бы​ формулы =РАНГ(A60:A67;A60:A67;1) или =СЧЁТЕСЛИ(A60:A67;»​ будет иметь ранг​о​ позицией (если нет​ 9; 4​Guest​ проблемой!​ думал ! !​ люди добрые !​

​ равных).​Определим номера значений в​ список по возрастанию,​ Понятно, что для​​ верхний угол таблицы​​=РАНГ.РВ(A3;A2:A6;1)​ 0,5, а ранг,​ ссылка была списком,​

​Такой подход использется в​ 7 (ни одно​льшие ранги.​ повторов).​Нужно обратить функцию​

• ​: Спасибо большое! Ваша​Формула =РАНГ(A1;A$1:A$12;1)+((СЧЁТЕСЛИ(A$1:A$12;A1))-1)/2 считает​ !​ Помогите, не могу​Как видно из примера,​ списке, где присутствуют​
• ​ то функция вернет​ максимального значения процентиль-ранг​ результатов).​Ранг числа 3,5 в​ пересмотренный с учетом​ отсортированным по возрастанию.​ статьях Отбор уникальных значений​ из чисел не​Если список чисел находится​Синтаксис​ РАНГ, что б​ формула очень пригодилась​ всё правильно и​Guest​ разобраться! Суть в​ данная функция обрабатывает​ повторяющиеся значения.​ позицию числа. Например,​ равен 0,00%, а​
• ​После нажатия кнопки ОК​ том же списке.​ связей, равен 3​Функция РАНГ.РВ присваивает повторяющимся​ с сортировкой в​ будет иметь ранга​ в диапазоне​РАНГчислоссылка​
• ​ по рангу мне​ и помогла. ​ хорошо, но у​: а можно расшифровать​ следующем:​ повторяющиеся числа в​Желтым цветом выделены повторяющиеся​ в массиве чисел​ для минимального -​ будет выведена таблица​3​ + 0,5 =​ числам одинаковые значения​ MS EXCEL и Динамическая сортировка​ 6).​A7:A11​;порядок)​ выдавались числа, например​Fertion​ меня последовательность из​ поподробнее формулу, чтоб​есть у меня​ списке точно так​ числа. Для них​ <30;2;26>число 2​ 100% (все значения​ результатов, содержащая следующие​Рассмотрим инструмент Ранг и​

​ 3,5. Если число​ ранга. Однако наличие​ таблицы в MS​Иногда это не удобно​, то формула =РАНГ(A7;$A$7:$A$11)​Число​1 3 4​: Есть большой столбик​ 35 значений (​ её понять (в​ набор значений (к​

​ же, как и​​ определяется один и​ будет иметь ранг​ массива меньше или​ столбцы (см. рисунок​ Персентиль надстройки Пакет​ встречается в ссылке​ повторяющихся чисел влияет​ EXCEL.​ и требуется, чтобы​ определит ранг числа​

Функции РАНГ, РАНГ.РВ и РАНГ.СР для ранжирования данных в Excel

​— число, для​ 5 и т.д​ цифр, часто они​ берутся с ссылкой​ эксел новичок)​ примеру) 1 .​ в обычной формуле.​ тот же номер.​

Пример функции РАНГ в Excel

​ 1; 26 –2;​ равны ему). Повторы​ выше):​ анализа.​ только один раз,​ на ранги последующих​В этой статье описаны​ ранги не повторялись​ из ячейки​ которого определяется ранг.​ соотвествовало 4, 5,​ повторяются. Нужно каждому​ на другой лист).​=РАНГ(A1;A$1:A$12;1)+((СЧЁТЕСЛИ(A$1:A$12;A1))-1)/2​ 2 . 3​ Если необходимо избежать​

​ Например, числу 7​

1. ​ 30 –3 (как​ в массиве имеют​Столбец Массив. Это просто​
2. ​Надстройка Пакет анализа MS​ поправочный коэффициент будет​ чисел. Например, если​ синтаксис формулы и​ (например, при определении​А7​Ссылка​ 7, 8 и​ значению присвоить свой​
3. ​ Так вот когда​сначала указываем функцию​ . 4 .​ повторений рангов, используем​ во втором столбце​ наибольшее значение в​ один и тот​ наш входной массив​ EXCEL позволяет быстро​ равен 0, поскольку​ в списке целых​ использование функции​ призовых мест, когда​(см. файл примера). ​— ссылка на​ т.д.​

​ уникальный ранг. В​ у меня на​

​ РАНГ, A$1:A$12 -​ 5 . 6​ другую формулу (см.​ присвоен номер 9​ списке).​ же ранг и​ сортированный по убыванию.​ вычислить ранги для​

​ РАНГ.РВ для связи​ чисел, отсортированных по​РАНГ​

​ нельзя занимать нескольким​Т.к. аргумент​ список чисел (диапазон​

​AlexM​ случае, если значения​ другом листе последовательность​ ссылка на массив,​ . 7 .​ выше).​ (и во второй​Синтаксис функции:​ процентиль-ранг. Подробнее про​ Его можно сформировать​ значений массива.​ не будет изменяться.​ возрастанию, дважды встречается​в Microsoft Excel.​ людям одно место).​порядок​ ячеек с числами).​

​: Изобразите задание в​ одинаковы, по идее,​ скажем не из​ а что тут​ 8 . 8​

​Возвращает номера числового значения​ строке, и в​Число. Для, которого необходимо​ процентиль-ранг см. статью про​ формулой =НАИБОЛЬШИЙ($B$7:$B$32;A7) (см.​Пусть в диапазоне​Скопируйте образец данных из​

​ число 10, имеющее​Возвращает ранг числа в​В этом нам поможет​опущен, то MS​ Напрямую массив задать​

​ файле. Будет понятнее.​

Пример функции РАНГ.РВ в Excel

​ каждому последующему нужно​ 35, а к​ указывают A1 и​ . 9 .​ в списке (порядковый​ девятой); в третьем​ определить номер в​ Процентили (раздел про Функцию​ файл примера)​В7:В32​ следующей таблицы и​ ранг 5, число​ списке чисел, то​ формула =РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1​ EXCEL присвоил ранг=1​

​ нельзя, формула =РАНГ(10;<10:50:30:40:50>)​buchlotnik​ лишь прибавлять единицу.​ примеру, из 20,​ 1. далее условие​ 10 . 10​ номер относительно других​ столбце – 3.​ ранжировании.​ ПРОЦЕНТРАНГ.ВКЛ()). Этот столбец​

Пример функции РАНГ.СР в Excel

​Столбец со странным заголовком​имеется числовой массив​ вставьте их в​ 11 будет иметь​ есть его величину​Предполагается, что исходный список​ максимальному числу (50),​ работать не будет.​

​К примеру:​ то остальные 15​ идёт- всё что​ и мне необходимо​ значений). То есть​

​ Но ни одно​Ссылка. На массив чисел​ можно сформировать с​

​ Точка. В нем​ (см. файл примера).​ ячейку A1 нового​ ранг 7 (ни​ относительно других значений​

​ с числами находится​

​ а максимальный ранг​ Но, если ввести​Dubick​Значение Ранг​ в данном листе​ я понял, а​ их проранжировать (т.е​ выполняет ту же​

​ из чисел во​ или диапазон ячеек​

​ помощью формулы =ПРОЦЕНТРАНГ.ВКЛ($B$7:$B$32;I7).​ выведены первоначальные позиции​Вызовем диалоговое окно надстройки​ листа Excel. Чтобы​ одно из чисел​ в списке. Если​

Ранжирование

​ в диапазоне​​ (5 = количеству​ формулу =РАНГ(B7;$A$7:$A$11), то​, думаю Вам поможет​3 2​ где рассчитывается ранг​
​ что за -1​ присвоить каждому числовому​ задачу. Только при​ втором столбце не​ с числовыми значениями.​Не смотря на свое​ значений из столбца​ Пакет анализа (Вкладка​ отобразить результаты формул,​ не будет иметь​ несколько значений имеют​А37:А44​ значений в списке)​ она будет работать​ функция НАИМЕНЬШИЙ. С​1 1​ имеют значение 0,​ и деление на​ значению РАНГ, в​ обнаружении идентичных значений​ будет иметь 10,​ Если задать в​ название Ранг и​ Массив исх. Например,​
​ Данные/ Анализ/ Анализ​ выделите их и​ ранга 6).​ одинаковый ранг, возвращается​.​ — минимальному (10).​ (хотя ячейка​ файлом было бы​3 3​
​ и естественно формула​ 2, а также​ соседнем столбце, в​ возвращает средний показатель.​ а в третьем​ качестве аргумента просто​ Персентиль, надстройка Пакет​ до сортировки значение​
​ данных) и выберем​

​ нажмите клавишу F2,​​В некоторых случаях может​

​ наивысший ранг этого​​Примечание​Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;»>»&A7)+1​B7​ проще​8 4​

​ начинает считать неверно​​ зачем функция СЧЕТ. ​ зависимости от его​Вот результат работы функции:​ – 4.​
​ числа, то функция​
​ анализа не возвращает​ 50, которое сейчас​ инструмент Ранг и​ а затем —​ потребоваться использование определения​ набора значений.​. В MS EXCEL​В столбце​- вне списка​gling​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=РАНГ(D3;D:D;1)+СЧЁТЕСЛИ($D$3:D3;D3)-1​ т.к. она анализирует​

​Serge​​ величены ). НО​Формула в столбце «по​

​Чтобы ранги не повторялись​​ вернет ошибку. Нечисловым​ Персентили. По крайней​ стоит первым, находилось​
​ Персентиль.​ клавишу ВВОД. При​
​ ранга с учетом​Если отсортировать список, то​
​ 2010 добавилась функция РАНГ.РВ(число;ссылка;[порядок]) Если​С​ с числами). Если​: Или так​Нашел вот такую​ 35 значений. Можно​: СЧЁТЕСЛИ подсчитывает кол-во​
​ если встречаются одинаковые​ убыванию»: =РАНГ.СР(A2;$A$2:$A$9;0). Так,​
​ (иногда это мешает​ значениям номер не​ мере в том​ в 18-й строке​СОВЕТ​ необходимости измените ширину​

​ связей. Допустим, что​​ ранг числа будет​ несколько значений имеют​

​приведена формула =РАНГ(A7;$A$7:$A$11;1)​​ в ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС($A$1:$A$10;ПОИСКПОЗ(СТРОКА(A1);РАНГ.РВ($A$1:$A$10;$A$1:$A$10;1);0))​ формулу, и для​
​ как-нибудь исправить данную​ одинаковы рангов.​ значения ( в​ функция значению 87​ пользователю решить поставленную​ присваивается.​ понимании, которое заложено​ исходного списка. Если​: О том как​ столбцов, чтобы видеть​ в предыдущем примере​ его позицией.​ одинаковый ранг, возвращается​ с рангом по​B7​$A$1:$A$10- диапазон значений​ небольших диапазонов она​ проблему? к примеру,​MCH​ примере это 8​ присвоила средний номер​ задачу), используется следующая​Порядок. Способ упорядочения чисел​ в функции ПРОЦЕНТИЛЬ.ВКЛ().​ во входном массиве​

​ подключить и работать​​ все данные.​

​ вам требуется получить​​РАНГ.РВ(число;ссылка;[порядок])​

​ наивысший ранг этого​​ возрастанию, ранг=1 присвоен​содержится число вне​ для РАНГ. Формула​ работает. Но когда​ чтобы 0 игнорировала​: По синтаксису функции​ и 8, 10​ 1,5.​

​ формула:​ в списке. Варианты:​

​ Подробнее про эту​​ есть повторы, то​ с этой надстройкой​Данные​ ранг 5,5 для​Аргументы функции РАНГ.РВ описаны​ набора значений (присваивает​ минимальному числу. Альтернативный​ списка с числами,​ массивная, вводится одновременным​ я расширил её,​

​ или ещё как?​​ РАНГ — читайте​ и 10) то​Допустим, в списке чисел​

Присвоить каждому значению уникальный ранг (Формулы/Formulas)

​Для работы функции можно​​ аргумент равен «0»​ функцию также см.​ каждому повтору будет​ см. статью Пакет​7​ числа 10. Для​ ниже.​ повторяющимся числам одинаковые​ вариант: =СЧЁТЕСЛИ($A$7:$A$11;»​
​ то формула вернет​
​ нажатием Ctrl+Shift+Enter в​
​ она перестала корректно​
​MCH​
​ справку.​
​ им присваивается СРЕДНИЙ​
​ три повторяющихся значения​
​ установить пределы. Например,​ или опущен –​ статью про Процентили.​ соответствовать своя позиция.​ анализа MS EXCEL.​3,5​ этого следует добавить​
​Число​ значения ранга). В​Если исходный список отсортировать,​
​ ошибку #Н/Д.​ строке формул.​ работать.​

​: Нужен пример (​​Если записать просто​ РАНГ.​
​ (выделены оранжевым цветом).​ необходимо ранжировать только​

​ значение 1 присваивается​​Примечание​ Этот столбец можно​

функция РАНГ наоборот (Формулы/Formulas)

​Появится диалоговое окно:​​3,5​
​ поправочный коэффициент (см.​ Обязательный. Число, для​ файле примера дается​ то ранг числа​
​Нечисловые значения в ссылке​P.S.Не тестировал правильность,​Как исправить формулу,​Guest​ РАНГ(. )​
​т.е. 1 (1)​Функция присвоила каждому из​ значения от 0​ максимальному числу в​
​: Существует некоторая путаница​ сформировать с помощью​Если ссылку на массив​1​ ниже) к значению,​

​ которого определяется ранг.​​ пояснение работы этой​ будет его позицией​

​ игнорируются. Числам, сохраненным​​ сами проверьте.​​ чтобы исключить повторяющиеся​​: Пример!​то получатся ранги:​ . 2(2) .​ них ранг 5,​

​ до 30. Чтобы​​ списке (как будто​ ​ в терминологии: MS​ ​ формулы массива (см.​ указать вместе с​2​ возвращаемому функцией РАНГ.РВ.​Ссылка​
​ функции. Также добавилась​ в списке.​

Функции РАНГ, РАНГ.РВ и РАНГ.СР для ранжирования данных в Excel

Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.

Пример функции РАНГ в Excel

Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел <30;2;26>число 2 будет иметь ранг 1; 26 –2; 30 –3 (как наибольшее значение в списке).

1. Число . Для, которого необходимо определить номер в ранжировании.
2. Ссылка . На массив чисел или диапазон ячеек с числовыми значениями. Если задать в качестве аргумента просто числа, то функция вернет ошибку. Нечисловым значениям номер не присваивается.
3. Порядок . Способ упорядочения чисел в списке. Варианты: аргумент равен «0» или опущен – значение 1 присваивается максимальному числу в списке (как будто список отсортирован в порядке убывания); аргумент равен любому неравному нулю числу – номер ранжирования 1 присваивается минимальному числу в списке (как будто список отсортирован в порядке возрастания).

Определим ранжирование чисел в списке без повторов:

Аргумент, определяющий способ упорядочения чисел, равен «0». Следовательно, в данной функции номера присваивались значениям от большего к меньшему. Максимальному числу 87 присвоен номер 1.

В третьем столбце приведена формула с рангом по возрастанию.

Определим номера значений в списке, где присутствуют повторяющиеся значения.

Желтым цветом выделены повторяющиеся числа. Для них определяется один и тот же номер. Например, числу 7 во втором столбце присвоен номер 9 (и во второй строке, и в девятой); в третьем столбце – 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем – 4.

Чтобы ранги не повторялись (иногда это мешает пользователю решить поставленную задачу), используется следующая формула:

Для работы функции можно установить пределы. Например, необходимо ранжировать только значения от 0 до 30. Чтобы решить задачу, применим функцию ЕСЛИ (=ЕСЛИ(A2<30;РАНГ(A2;$A$2:$A$13);"")).

Серым цветом выделены значения, которые соответствуют заданному условию. Для чисел, которые больше 30, выводится пустая строка.

Пример функции РАНГ.РВ в Excel

В версиях Excel, начиная с 2010 года, появилась функция РАНГ.РВ. Это абсолютный аналог предыдущей функции. Синтаксис такой же. Буквы «РВ» в названии указывают на то, что при обнаружении формулой одинаковых значений функция вернет высший номер ранжирования (то есть первого обнаруженного элемента в перечне равных).

Как видно из примера, данная функция обрабатывает повторяющиеся числа в списке точно так же, как и в обычной формуле. Если необходимо избежать повторений рангов, используем другую формулу (см. выше).

Пример функции РАНГ.СР в Excel

Возвращает номера числового значения в списке (порядковый номер относительно других значений). То есть выполняет ту же задачу. Только при обнаружении идентичных значений возвращает средний показатель.

Вот результат работы функции:

Формула в столбце «по убыванию»: =РАНГ.СР(A2;$A$2:$A$9;0). Так, функция значению 87 присвоила средний номер 1,5.

Допустим, в списке чисел три повторяющихся значения (выделены оранжевым цветом).

Функция присвоила каждому из них ранг 5, что является средним для 4, 5 и 6.

Сравним работу двух функций:

Напомним, что эти две функции работают только в Excel 2010 и выше. В более ранних версиях для этих целей можно задействовать формулу массива.

Таким образом, все выше описанные примеры позволяют автоматизировать работу по ранжированию данных и составлению рейтинга значений без применения сортировки.

Функция РАНГ в Excel. Составляем простой рейтинг ⁠ ⁠

В этом видео речь пойдет о стандартной функции РАНГ (РАНГ.РВ), с помощью которой можно создать рейтинг значений. Например, есть школьный журнал, в котором рассчитан средний балл учеников. Нам необходимо создать рейтинг учеников по их успеваемости.

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

В видео было сказано, что в следующем видео будет рассказано, что делать если есть одинаковые значения. Как найти это следующее?

Автор молодчик конечно, что даешь уроки по экселю, но на моей личной практике эта функция встретилась впервые. Зачем такое словоблудие и понимание доселе неизвестной формулы, если есть средневзвешенное и сортировка?

Это как раньше с VBA было. Все всегда спрашивали, но за много лет его знания (в целом, не мои) пригодились раза два всего. И туда же целый набор формул

Вообще вот что надо в вузах и школах приподавтаь. А то приходят интерны и в экселе не шарят от слова совсем. Не то-что индекс метчем не владеют, но про ВПР не слышали.

Читать ещё на Пикабу

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1⁠ ⁠

Друзья, всем привет.

Сегодня хотел бы поговорить с вами о том, чего в Excel делать не нужно, чтобы в будущем избавить себя от головной боли и перманентных хлопков в нижней части тела. Планировал сначала про 10 вещей написать, но при подготовке материала понял, что в рамках одной статьи слишком уж много получится. Поэтому решил разбить на 2 части.

Важное примечание!

Сразу оговорюсь, что речь здесь скорее пойдёт не про то, как исправлять уже кривые данные, а про то, как правильно первоначально их оформлять. Так что статья больше ориентирована не на тех, кто работает с готовыми данными, а собирает все свои таблицы «с нуля».

1 — Объединение ячеек.

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

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

Если уж так хочется, чтобы название месяца было по центру, то поступаем следующим образом.

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание — по горизонтали — по центру выделения:

К остальным заголовкам можно уже применить «Формат по образцу» (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.

2 — «Правильная» таблица

Этот пункт, безусловно, спорный, но я его всё же включил. И, в первую очередь, совет касается тех таблиц, но основе которых вы в дальнейшем планируете строить сводные таблицы. Сделали мы вот такую таблицу по месяцам за несколько лет:

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

Всё просто — не делайте трёхмерные таблицы. Плоская таблица — вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:

Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.

3 — «Правильные» даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

«-» — дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 — Цветовое оформление таблицы.

Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 — Ручное закрашивание данных

Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции⁠ ⁠

Друзья, всем привет. В прошлой статье я рассказывал про мгновенное заполнение в Excel. Вещь интересная, но, как некоторые пользователи отмечали в комментариях (и вполне обоснованно), мгновенное заполнение не всегда подходит для решения тех или иных задач. В первую очередь, это касается данных, которые в таблице часто меняются. Мгновенное заполнение не реагирует на изменение данных. В этом случае лучше использовать функции. Да, это дольше, да, это будет выглядеть страшно (далее поймёте, почему), но зато один раз сделал и потом наслаждаешься плодами своих трудов. В качестве примера я решил взять тестовое задание, которое давно проходил при приёме на работу в одну довольно известную компанию.

Важное уточнение! В Excel можно решать очень сложные задачи. Но должна быть хоть какая-то логика. Чем сложнее логика, тем сложнее будут сочетания функций, набор инструментов и т.д. Потратишь много времени, но решишь. А вот если логики нет, то не ждите чуда. Чудите сами, как говорится.

Что же, давайте приступим.

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному — наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 — понятно, ячейка с текстом.

D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

Вот и решение задачи. Вообще, моё мнение, такие штуки в любом случае нужно сначала решать пошагово, а потом только впихивать всё в одну ячейку. Но тут уж каждый сам для себя решает.

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в «Должно быть»:

Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать — это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:

Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

Так как же это работает? Всё довольно просто. В первой ячейке мы задаём образец, чего хотим получить, далее Excel распознаёт нашу логику и заполняет остальные ячейки по образу и подобию.

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

Теперь, когда с пояснениями закончено, давайте посмотрим, на что ещё способен этот удивительный инструмент.

Извлечение только чисел из столбца

Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:

На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

После заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробок

ЗЫ Отдельное спасибо @XaXa3Pa3a

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).

Небольшое пояснение

Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

Представления, с моей точки зрения, являются одним из самых недооценённых инструментов в Excel. Предположим, у вас есть таблица, в которой вы часто фильтруете несколько столбцов по разным критериям: отдел, пол и город.

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

Вообще, про специальную вставку в Excel можно написать отдельную статью, наверное. Инструмент во многих случаях просто незаменимый. Но в рамках данной статьи я расскажу только про одну возможность. Представим, что есть две таблицы:

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

Если б мишки были пчёлами… условия в Excel⁠ ⁠

Решил попробовать формат коротких постов по Excel

Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.

В общем виде выглядит так:

ЕСЛИ (условие; результат если условие верное; результат если условие не верное)

Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце

При множественных условиях иногда удобно применять функции И, и ИЛИ, которые позволяют соединять несколько условий

=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)

Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.

=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)

Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.

А на этом сегодня все, пойду дальше писать про макросы…

Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»⁠ ⁠

Если по-простому, формулами, то так:

Таблицу 3 переделываем в другой вид:

Это будет состав детали, даты будем добавлять вправо.

(Таблица соответствия количества материала количеству детали)

Таблица 2 тогда будет выглядеть так:

(тупо суммируем кол-во материалов на дату)

1. Важно чтобы во всех 3-х таблицах даты были синхронизированы, чтобы можно было протягивать формулы не опасаясь что-то поломать.

2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.

Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:

Ответ на пост «Excel, делим покупки на несколько человек»⁠ ⁠

И так господа, перед Вами методичка по распиливанию затрат после попоек.

Разработана она мною, но на копирайт не претендую.

Ситуация проста: Пятница, Вы собрались пошуметь. Заказаны «стартовые» места, собрана компания друзей, сняты ограничения с карточек, побрились, подмылись, вызвали такси и поехали!

Сразу рекомендация: договоритесь как будете делить счёт. Вполне возможно, что в вашей компании тот, кому завтра платить за ипотеку. И он вообще в принципе не собирается много тратить.

Помните, между друзьями не должно быть обид или споров по денежным вопросам. Если у вас в компании есть друг, не желающий сопровождать общий счёт, объясните ему, что он сам платит за себя и главное платит СРАЗУ! Принесли счёт в ресторане, он сам запоминает и считает сумму напитого и наеденного и СРАЗУ кидает на стол деньги, а ещё фиксит это, например в общем чате. Кстати, общий чат — это тема. История с карточками — это хорошо, но налом платить в некоторых местах безопасней. Кидайте сразу в общий чат, кто, сколько и главное, за что заплатил. Поверьте, с утра вам будет не до воспоминаний, да и не всё вспомнить. Не забывайте о конспирации! Нельзя писать «3000 Диме на приват». Продумайте заранее все фразы и договоритесь об обозначениях.

Вы проснулись в субботу, в обед с привкусом тухлых кошачьих носков во рту, тремя размытыми печатями, двумя клубными браслетами и одной татуировкой с рожей чихуахуа на правом бедре. На карте минус месячный доход. Но вы всё предусмотрели, поэтому гоу в чат и историю операций с карты в приложении банка.

Помните! Очень большая вероятность, что вы вчера платили за всех и вам все должны (что не факт). В этом случае не один из ваших друзей чисто морально не хочет к похмелью ещё добавлять чувство долга! Поэтому от того, как быстро вернёте деньги зависит только от вас. Теперь вы коллектор и начинаете обзвон друзей.

1. Кидаем в чат позывной. Кто, за что, вчера платил.

2. Открываем Excell и создаём следующую таблицу:

И так: Перед вами таблица со следующими параметрами:

Столбец «Событие» — кратко фиксим события оплаты;

Столбцы «Участники» — все, кто присутствовал на пьянке;

Столбец «Сумма» — Общая сумма к разделу;

Столбец «N» — кол-во человек участвующих в разделе;

Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.

Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».

Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.

Тогда для Пети пишется формула:

Для всех, кто учувствовал пишется формула:

В результате получаем:

Все кто с минусом — те в долгах;

Кто с плюсом -тот кредитор.

Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:

Как мы видим, в пабе к нам пришёл ещё Ваня.

Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.

Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.

Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:

После стрипухи все возвращаются в кафе на завтрак, при этом по дороге подбирая уже сбежавшего под утро Ваню. В кафе решают платить по честному! Поэтому каждый платит за то, сто съел (Платит Ваня):

Как использовать формулу РАНГ ЕСЛИ в Excel

Вы можете использовать следующие методы для создания формулы РАНГ ЕСЛИ в Excel:

Метод 1: РАНГ, ЕСЛИ

Эта формула находит ранг значения в ячейке B2 среди всех значений в диапазоне B2:B11 , где соответствующее значение в диапазоне A2:A11 равно значению в ячейке A2 .

При использовании этого метода наибольшему значению присваивается значение 1.

Метод 2: Обратный ранг, если

Эта формула находит ранг значения в ячейке B2 среди всех значений в диапазоне B2:B11 , где соответствующее значение в диапазоне A2:A11 равно значению в ячейке A2 .

При использовании этого метода наименьшему значению присваивается значение 1.

В следующих примерах показано, как использовать каждый метод со следующим набором данных в Excel:

Пример 1: РАНГ, ЕСЛИ

Мы можем ввести следующую формулу в ячейку C2 , чтобы вычислить ранг значения очков в ячейке B2 среди всех игроков команды «Mavs»:

Затем мы можем перетащить эту формулу в каждую оставшуюся ячейку в столбце C:

Вот как интерпретировать вывод:

• Первый игрок набрал 3 -е место по количеству очков среди всех игроков команды Mavs.
• Второй игрок набрал 5 -е место по количеству очков среди всех игроков команды Mavs.

Используя эту формулу, игрок с наибольшим количеством очков в каждой команде получает ранг 1.

Пример 2: Обратный ранг, если

Мы можем ввести следующую формулу в ячейку C2 , чтобы вычислить ранг значения очков в ячейке B2 среди всех игроков команды «Mavs»:

Затем мы можем перетащить эту формулу в каждую оставшуюся ячейку в столбце C:

Вот как интерпретировать вывод:

• Первый игрок набрал 3 -е место по наименьшему количеству очков среди всех игроков команды Mavs.
• Второй игрок набрал 1 -е место с наименьшим количеством очков среди всех игроков команды Mavs.

Используя эту формулу, игрок с наименьшим количеством очков в каждой команде получает ранг 1.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel: