Как сделать статус в excel
Строка состояния в Excel
Панель состояния Excel (Содержание)
Строка состояния в Excel
«Строка состояния», как следует из слова, возвращается со статусом некоторых простых опций в Excel. Строка состояния находится горизонтально внизу любой рабочей книги Excel. Строка состояния дает быстрый просмотр некоторых параметров, таких как номер страницы (количество страниц в рабочей книге при работе в представлении «Макет страницы» или «Просмотр печати»), Количество ячеек, Сумма, Среднее, Минимальное и максимальное (по умолчанию отключено) ), Числовой счетчик (количество числовых значений в выбранном диапазоне ячеек) и т. Д. Строка состояния одинакова для всех офисных приложений (MS Word, MS PowerPoint, MS Access).
Как настроить строку состояния в Excel?
По умолчанию некоторые параметры уже активны в строке состояния Excel, такие как «Счет», «Сумма», «Среднее» и т. Д. Однако, что если нам понадобятся некоторые из них, которые по умолчанию не активны, например «Минимум», «Максимум» и т. Д.? Нам нужно настроить строку состояния, чтобы эти параметры также были активными.
Примечание. Если вы хотите выйти из настраиваемой строки состояния, просто щелкните в любом месте листа за пределами всплывающего меню.
Довольно просто, да?
В этом уроке я расскажу о некоторых опциях, которые по умолчанию включены в строке состояния Excel, а также о некоторых опциях, которые не включены по умолчанию, но могут быть полезны, если их добавить туда.
Параметр «Режим ячейки» в строке состояния Excel
Опция «Режим ячейки» включена по умолчанию в Excel и отображается в крайней левой части строки состояния. Он имеет различные режимы, перечисленные ниже:
Пожалуйста, смотрите прикрепленный образец изображения для вашей справки.
Слайдер масштабирования в строке состояния Excel
Смотрите пример изображения ниже для вашей справки.
Процент увеличения
Статистические / Математические Варианты
Есть несколько статистических / математических опций для быстрого расчета числовых данных в строке состояния Excel. Эти параметры включены по умолчанию. Давайте пройдем через это.
Примечание. Обратите внимание, что параметры «Среднее» и «Сумма» можно использовать только для тех ячеек, которые имеют числовые значения.
Минимум и максимум
Примечание : Вы заметили, что есть одна ячейка, которая является пустой. Однако Excel не учел это при расчете как минимума, так и максимума. Причина этого в том, что пустая ячейка всегда рассматривается как ячейка без данных (не думайте, что пустая ячейка означает ячейку с нулевым значением)
Представления рабочего листа
Нормальный вид:
Вид макета страницы:
Предварительный просмотр разрыва страницы
Номер страницы
Это все с моей стороны, ребята, мы воспользуемся моментом, чтобы быстро обернуть вещи некоторыми вещами, которые нужно запомнить.
Что нужно помнить о строке состояния в Excel
Рекомендуемые статьи
5 способов создания выпадающего списка в ячейке Excel
Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие списки в Excel. Более сложные способы, основанные на динамических диапазонах и использовании данных из других таблиц, мы также рассмотрим.
Как нам это может пригодиться?
Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров. Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.
Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых необходимо указать только одно.
Важно то, что вы теперь будете не вводить, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок. Проверка того, что мы вписали в таблицу, теперь уже не нужна.
Как проще всего добавить выпадающий список? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.
Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).
В качестве источника можно использовать также и именованный диапазон.
К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».
Кроме того, и источник и в виде обычного диапазона ячеек, и именованный диапазон могут находиться на других листах вашей рабочей книги.
Вставим на лист новый объект – элемент управления «Поле со списком» с последующей привязкой его к данным на листе Excel. Делаем:
Вставив элемент управления на рабочий лист, щелкните по нему правой кнопкой мышки и выберите в появившемся меню пункт «Формат объекта». Далее указываем диапазон ячеек, в котором записаны допустимые значения для ввода. В поле «Связь с ячейкой» укажем, куда именно поместить результат. Важно учитывать, что этим результатом будет не само значение из указанного нами диапазона, а только его порядковый номер.
Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).
Формулу в F3 запишем, как показано на рисунке:
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.
Задача: Создать перечень, в который будут автоматически добавляться значения из заданного динамического диапазона. Если в диапазон будут внесены изменения, то сразу же изменится и набор предлагаемых значений. Никакие формулы и настройки здесь корректировать не нужно.
Вот как автозаполнение может выглядеть на простом примере:
Способ 1. Укажите заведомо большой источник.
Действительно самый простой способ, но не слишком удобный. Ведь зарезервированное место может и закончиться.
Конечно, в качестве источника можно указать и весь столбец:
Но обработка такого большого количества ячеек может несколько замедлить вычисления.
Способ 2. Применяем именованный диапазон.
Именованный диапазон отличается от обычного тем, что ему присвоено определенное наименование. С ним гораздо проще работать, так как не нужно вводить ссылку, а достаточно просто указать его имя. Давайте рассмотрим небольшой пример.
В столбце А находятся имена сотрудников, которые мы будем вводить. Перечень может быть сколь угодно длинным. Нам необходимо, чтобы каждая новая запись включалась в раскрывающийся список без всяких дополнительных действий с нашей стороны.
Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул. Создадим в С2 перечень значений. В качестве источника для него укажем выражение
Перечень ещё можно отсортировать, чтобы удобно было пользоваться.
А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.
Способ 3. «Умная» таблица нам в помощь.
Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.
Следовательно, автоматическое обновление набора используемой информации в списке можно организовать, если использовать содержимое какого-либо столбца «умной» таблицы.
Осталось только обозначить ее как источник. Проблема заключается в том, что программа в качестве источника в списке не понимает выражение вида
и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.
Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.
Формула теперь будет выглядеть следующим образом:
Не забудьте также заключить все выражение в кавычки, чтобы обозначить его как текстовую переменную.
Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.
Надеемся, вы сможете теперь с помощью списков без ошибок вводить часто повторяющиеся данные в таблицу.
Как сделать статус в excel
В Excel гистограмма выполнения может помочь вам отслеживать прогресс в достижении цели, как показано на следующем снимке экрана. Но как создать гистограмму выполнения на листе Excel?
Создайте индикатор выполнения в Excel с помощью обычной функции Insert Bar
Обычно вы можете вставить столбчатую диаграмму с кластерами, а затем отформатировать ее как диаграмму хода выполнения, выполните следующие действия:
1. Выберите данные, на основе которых вы хотите создать гистограмму выполнения, а затем щелкните Вставить > Вставить столбец или гистограмму > Кластерный бар под 2-мерный бар раздел, как показано на следующем снимке экрана:
2. Затем была вставлена кластерная диаграмма, затем щелкните панель целевого ряда данных, а затем щелкните правой кнопкой мыши, чтобы выбрать Форматировать ряд данных из контекстного меню см. снимок экрана:
3. В Форматировать ряд данных панели, нажмите Заливка и линия кнопку со значком, затем выберите Без заливки под Заполнять раздел, а затем выберите Сплошная линия и выберите один цвет для границы полосы в Граница раздел, см. снимок экрана:
4. Затем закройте Форматировать ряд данных панель, а затем щелкните, чтобы выбрать всю диаграмму, и щелкните Дизайн > Добавить элемент диаграммы > Этикетки данных > Внутри базы, все метки данных были вставлены в диаграмму, как показано на следующем снимке экрана:
5. Затем вы должны удалить другие метки данных и сохранить только текущие метки данных, как показано на следующем снимке экрана:
6. Затем щелкните правой кнопкой мыши целевую серию данных и выберите Форматировать ряд данных, В Форматировать ряд данных панели, нажмите Варианты серий кнопку со значком и измените Перекрытие серий в 100%, см. снимок экрана:
7. Затем выберите горизонтальную ось значений и щелкните правой кнопкой мыши, чтобы выбрать Ось формата вариант, см. снимок экрана:
8. В Ось формата панель, под Параметры оси на вкладке введите максимальное значение ваших целевых данных в поле максимальная текстовое поле, и индикатор выполнения будет полностью создан, см. снимок экрана:
Создайте индикатор выполнения в Excel с помощью функции условного форматирования
В Excel вы также можете создать гистограмму выполнения, используя Условное форматирование, пожалуйста, сделайте следующее:
1. Выделите ячейки значений, в которые вы хотите вставить диаграмму индикатора выполнения, а затем щелкните Главная > Условное форматирование > Панели данных > Другие правила, см. снимок экрана:
2. В Новое правило форматирования диалоговом окне выполните следующие действия:
(1.) В Тип раздел, выбрать Число in минимальный и максимальная раскрывающийся список;
(2.) Установите минимальное и максимальное значения в минимальный и максимальная коробка на основе ваших данных;
(3.) Наконец, выберите Сплошная заливка вариант под Заполнять раскрывающийся список, а затем выберите нужный цвет.
3. Затем нажмите OK кнопки, в ячейки вставлены индикаторы выполнения, см. снимок экрана:
Создайте индикатор выполнения в Excel с помощью удобной функции
Если у вас есть Kutools for Excel, он предоставляет множество специальных расширенных диаграмм, таких как Диаграмма индикатора выполнения, Таблица спидометра, График промокания, Диаграмма леденца на палочке, Столбчатая диаграмма переменной ширины и так далее. С этими Диаграмма индикатора выполнения вы можете быстро и легко вставить гистограмму на основе процентного значения или целевого и фактического значений.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Kutools > Графики > Прогресс > Диаграмма индикатора выполнения, см. снимок экрана:
2. В выскочившем Диаграмма индикатора выполнения диалоговом окне выполните следующие действия:
3. После настройки операций нажмите OK кнопка, и сразу был вставлен символ индикатора выполнения, см. снимок экрана:
Как сделать статус в excel?
Можно сделать в Excel выпадающий список для каждой ячейки который представляет из себя небольшой треугольник и если нажать на него то откроется список из которого можно выбрать нужный пункт и он будет отображаться в этой ячейке.
Так раскрывается в Excel выпадающий список в ячейке
Обычно раскрывающийся список в Excel создают для быстрой вставки в таблицу часто встречающихся названий и данных. Создать в эксель выпадающий список можно только на двух листах.
На первом листе составляются столбцы с названиями и данными которые должны отображаться в раскрывающихся списках, а на втором листе делают таблицу с раскрывающимися списками.
Приведу пример как сделать выпадающий список в Excel в таблице в которой будет три колонки: номер магазина, название фрукта, страна поставщик.
Составление списка
Чтобы создать выпадающий список в Excel с подстановкой данных сначала на первом листе составляем три списка.
Создать раскрывающийся список Excel 2010 можно на первом листе, а таблицу на втором
Каждый список должен составляться в один столбик или в одну строку и при этом не иметь пустых ячеек. Обязательно между списками должен быть пробел в виде столбика или строки, в моём случае пробел делается в виде столбца.
В каком порядке в списках вы поставите названия в ячейках в таком порядке они и будут раскрываться в таблице при раскрытии.
Когда списки будут составлены и рассортированы нужно будет произвести форматирование ячеек в Excel отдельно для каждого списка.
Лучше сделать выпадающий список в экселе с форматированием
Форматирование в эксель делается следующим образом. В первом списке с номерами магазинов наводите курсор на любой магазин и нажимаете левую кнопку мыши. Затем нажимаете на панели эксель Форматировать как таблицу и выбираете из предложенных стилей любой стиль и наведя на него курсор мыши нажимаете левую кнопку мыши.
Создать раскрывающийся список в Excel 2010 можно любого стиля
Откроется окошко под названием Форматирование таблицы. В этом окошке нужно поставить галочку у пункта Таблица с заголовками и после этого нажимаете кнопку ОК.
Лучше всего в Excel 2010 сделать выпадающий список форматированным
После того как форматирование в экселе всех списков закончите нужно присвоить каждому списку имя. Для того чтобы присвоить имя в Excel для одного списка нужно выделить весь список, а для этого нужно навести курсор на самый первый магазин в списке, нажать левую кнопку мыши и не отпуская её довести до последнего магазина в списке. После этого можно отпустить левую кнопку мыши.
Прежде чем сделать выпадающие списки в Excel 2010 необходимо создать эти списки
Затем в небольшом поле находящимся в левой части на панеле инструментов эксель нужно вписать название списка в моём случае это будет магазины и после этого обязательно нужно нажать на клавиатуре клавишу Enter. Нужно учесть, что название списка должно быть из одного слова или из нескольких слов но без пробелов между ними. Таким же образом готовим другие списки.
Чтобы сделать выпадающий список в эксель обязательно необходимо присвоить каждому списку имя
Когда все списки получат названия нужно будет проверить их, нажав на всплывающий список, при этом все ваши вводимые названия должны отобразиться.
Таблица с раскрывающимся списком
При создание в Excel выпадающего списка можно не беспокоится о точном количестве строк в таблице так как в неё легко можно будет добавлять строки.
Чтобы в таблице в Excel сделать раскрывающийся список необходимо сначала выделить первый столбец. Потом нужно перейти с вкладки Главная на вкладку Данные.
На этой вкладке нужно как сделать ниспадающий список в Excel нажав в панели инструментов на Проверку данных.
В эксель сделать выпадающий список можно на вкладке Данные
Откроется окошко под названием Проверка вводимых значений. В этом окошке на вкладке Параметры нужно выбрать из всплывающего списка пункт Список, а в появившейся строке источник нужно вписать название списка. Обязательно перед названием списка ставить знак равенства и будет выглядеть так =номер магазина.
Нажав в этом окошке кнопку ОК сразу в первом столбике этой таблицы можно будет производить выбор из списка в Excel. Точно также делаются выпадающие списки Excel и в других столбиках.
Когда таблица будет заполнена рекомендуется произвести её форматирование как и со спискам. Нажмите любую ячейку в таблице, а затем на главной вкладке эксель нажмите Форматировать как таблицу.
Вам предоставят выбрать какой-нибудь стиль. Выбрав стиль сразу откроется окошко в котором нужно нажать ОК.
Добавление и удаление пунктов из всплывающего списка
Из списков можно удалять какие-то пункты, а также можно дополнять списки. Чтобы удалить из списка какой-то пункт нужно открыть лист где находятся составленные списки.
Всплывающий список Excel легко дополнять
Находите в списках ячейку в которой находится пункт который нужно удалить. Наводите курсор на эту ячейку и нажимаете правую кнопку мыши. Откроется контекстное меню в котором нужно выбрать пункт Удалить.
От пункта удалить идут два пункта выбрать нужный нужно в зависимости от того как у вас расположены списки. Если у вас списки расположены в столбец то выбираете пункт Строки таблицы, а если списки расположены построчно то тогда выбираете пункт Столбцы таблицы.
После этого ячейка сразу удалится, а также и в таблице из раскрывающегося списка.
Чтобы добавить в список какой-то пункт нужно открыть лист где находятся составленные списки. Навести курсор на пустую ячейку которая находится сразу под последним пунктом списка и нажимаете левую кнопку мыши.
Теперь вписываете в ячейку нужные данные или название. После этого в таблице при открытии всплывающего списка вы увидите, что новый пункт уже имеется и его можно выбрать.
Видео
В этом видео показано как сделать список в экселе, а затем как с помощью него сделать раскрывающийся список в эксель 2010.
Выпадающие списки
Хитрости » 1 Май 2011 Дмитрий 319127 просмотров
Excel обладает очень неплохим инструментом для проверки введенных данных. В их число входит создание выпадающего списка. В этом случае в одной ячейке может содержаться несколько значений, организованных в виде списка 
Предназначен такой список не только для удобства ввода, но и для контроля вводимых в ячейку данных. Создав такой список можно настроить его так, чтобы пользователь имел возможность вводить в ячейку только значения из списка (такие настройки используются по умолчанию) или использовал список, но мог так же ввести любое значение не из списка.
Создание списков
Необходимо выбрать ячейку(или сразу несколько ячеек), в которую необходимо поместить этот список. В меню выбираем Данные (Data) —Проверка данных (Data Validation).
Переходим на вкладку Параметры (Settings) и в выпадающем списке Тип данных (Allow) выбираем Список (List).
Поле Источник (Source): предназначено как раз для ввода значений, из которых будет состоять выпадающий список. Варианты указания значений:
Большое преимущество данного способа в том, что если имя имеет область действия Книга, то списки можно создавать из данных, расположенных на любом листе книги. Особенно это полезно для версий Excel 2007 и более ранних, т.к. для них нет возможности в качестве диапазона для выпадающего списка указать данные другого листа(не того, на котором создается сам список).
Параметры списков
Распространить изменения на другие ячейки с тем же условием (Apply this chages to all other cells with the same settings) — данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК. Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.
Статья помогла? Поделись ссылкой с друзьями!
Цветовые статусы
Часто возникает необходимость в отчётах акцентировать статус какого-либо процесса при помощи цвета. При этом довольно популярна метафора светофора.
Если всё хорошо — зелёный статус, что-то настораживает — жёлтый, всё плохо — красный. Таким образом, тот, кто смотрит ваши отчёты, имеет возможность по цвету сразу обращаться к проблемным цифрам.
Отчёты становятся более наглядными, интуитивно понятными и экономят время просматривающих.
Ограничения условного форматирования
В Excel есть стандартный инструмент, который решает эту задачу, — условное форматирование при помощи набора значков. Инструмент отличный, но в некоторых ситуациях вам его может быть недостаточно.
Я, например, вижу следующую проблему: данные значки довольно мелкие и хорошо выглядят только в своём оригинальном размере.
Если вам потребуется значок побольше и/или поинтересней, то придётся его делать самому при помощи фигур.
Фигуры
Фигурами в MS Office можно нарисовать всё, что угодно. Серьёзно. Любой сложный рисунок «собирается» из простых элементов. Это вопрос только времени и стараний.
В этой статье мы будем управлять вот такими несложными, но достаточно привлекательными светофорами, которые легко делаются из фигур овал (круг — частный случай овала/эллипса) и кольцо.
Мы хотим визуализировать соотношение фактических и плановых расходов по проектам при помощи наших светофоров. Вот так:
Пример
Скачать
Последовательность шагов
Для начала подготовим данные, на основе которых будем вычислять статусы. На нашем примере это столбцы: Проект, Бюджет, Факт.
Подготовим вспомогательную таблицу, на основе которой будем присваивать значения статусов. В нашем случае эта таблица располагается на листе Настройки, оформлена в виде умной таблицы с названием Шкала. Статус G означает Green (зеленый), Y — Yellow (жёлтый), R — Red (красный).
Для выбора значения из Шкалы идеально подходит формула ВПР в своей диапазонной версии, которая ищёт диапазон, в который попадает значение ((D3-C3)/C3) в справочнике (Шкала), и возвращает из справочника содержимое ячейки на пересечении найденной строки и указанного столбца (2).
Если вычисление функции ВПР (VLOOKUP) оканчивается ошибкой (например, когда Бюджет=0), то формула ЕСЛИОШИБКА (IFERROR) её перехватывает и возвращает в ячейку значение D, что будет означать, что светофор не горит (серый). Формулу из E3 распространяем на E4:E5.
Формат данных диапазона E3:E5 устанавливаем в «;;;», что предотвращает появление значений ячеек на экране, чтобы цифры не выглядывали из-за светофоров, которые мы поместим над этими ячейками.
Создаём именованный диапазон rngTrafLight для ячеек E3:E5.
Создаём из фигур наши светофоры. Круги, цвет которых мы будем менять, называем именами figTL1 для E3, figTL2 для E4 и figTL3 для E5. Располагаем фигуры, там где они должны находиться.
В редакторе VBA в лист Лист1 (Статусы) поместите код:
Проверьте как всё работает.
Попробуйте! Это не сложно, но эффектно.
Формирование списка с помощью формул Excel
Сегодня хотелось бы рассмотреть прием формирования отдельного списка с помощью формул Excel. А рассмотрим мы это на таком примере. Пришел вопрос от читателя моего сайта примерно следующего содержания
Хочу сделать список должников документов в excel. Как сделать так чтобы строка в которой есть слово «нет» (т.е у этого контрагента нет документов), автоматически копировалась на отдельный лист.
То есть, необходимо формировать список на отдельно листе в зависимости от определенного условия. На первый взгляд для решения задачи без макросов не обойтись, но есть определенный прием, который позволяет сделать это с помощью формул. Для наглядности я буду делать это на одном листе, но принцип один, при необходимости можно перенести на отдельный лист.
Итак, посмотрите на рисунок выше, к чему мы должны прийти. Список должников должен автоматически пополняться фамилиями, если в столбце «B» будет появляться слово «Нет». Нам потребуется добавить вспомогательный столбец.
Для удобства мы добавим его самым первым, чтобы можно было использовать функцию ВПР (чтобы искомое значение находилось левее возвращаемого значения), можно было бы использовать функцию ИНДЕКС, в этом случаем вспомогательный столбец можно было разместить где угодно.
В данном столбце мы пропишем такую формулу
Функция так и прописывается без аргументов. Эта функция возвращаем нам номер строки. То есть если эта функция будет прописана в третьей строке, то результатом будет 3 и так далее.
Единственное, нам нужно, чтобы вычисления номера строки было только в тех строках, где есть слово «Нет». Для этого воспользуемся функцией ЕСЛИ и сделаем проверку.
Если в столбце «B» есть слово «Нет», то вычислять номер строки, если нет, то указать в ячейку пусто. Формула примет следующий вид.
где k — это порядок старшинства. Например, если указать 2, то будет выбрано второе наименьшее число из столбца «A».
Нам же нужно, чтобы сначала было самое маленькое число и потом оно должно увеличиваться, то есть k должно начинаться с единицы и с каждой ячейке должно увеличиваться.
Для этого мы можем сделать еще один дополнительный столбец и прописать пункты по порядку 1, 2, 3 и так далее и сделать ссылку на эти ячейки, но мы попробуем обойтись без вспомогательного столбца.
Вместо k мы можем так же воспользоваться функцией СТРОКА(), которая при протягивании вниз как раз будет увеличиваться на единицу, но так как формулу мы начинаем со второй строки, а нам нужна первая k равная 1, то мы можем прописать СТРОКА()-1, в итоге формула примет вид в ячейке «E2», эту формулу протянем вниз с запасом.
Вот теперь, всё как необходимо. Осталось последнее действие. С помощью функции ВПР, по искомому значению из столбца „E“, возвращаем соответствующие фамилии.
Сделаем так же проверку, если в столбце „E“ будет пустая ячейка, то будет возвращать пусто, если ячейка не пустая, то будем выполнять функцию. Пропишем итоговую формулу в ячейке „F2“ и протянем формулу вниз так же с запасом.