Excel имя столбца по значению

ГЛАВНАЯ

ТРЕНИНГИ

   Быстрый старт
   Расширенный Excel
   Мастер Формул
   Прогнозирование
   Визуализация
   Макросы на VBA

КНИГИ

   Готовые решения
   Мастер Формул
   Скульптор данных
ВИДЕОУРОКИ

ПРИЕМЫ

   Бизнес-анализ
   Выпадающие списки
   Даты и время
   Диаграммы
   Диапазоны
   Дубликаты
   Защита данных
   Интернет, email
   Книги, листы
   Макросы
   Сводные таблицы
   Текст
   Форматирование
   Функции
   Всякое
 PLEX

   Коротко
   Подробно
   Версии
   Вопрос-Ответ
   Скачать
   Купить
ПРОЕКТЫ

ОНЛАЙН-КУРСЫ

ФОРУМ

   Excel
   Работа
   PLEX

© Николай Павлов, Planetaexcel, 2006-2022
info@planetaexcel.ru


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

Техническая поддержка сайта

ООО «Планета Эксел»

ИНН 7735603520

ОГРН 1147746834949 		        ИП Павлов Николай Владимирович
        ИНН 633015842586
        ОГРНИП 310633031600071 

Источник

всем огромное спасибо .

Все работает идеально.

Благодарю за оперативную помощь.

всем огромное спасибо .

Все работает идеально.

Благодарю за оперативную помощь. exel

Сообщение всем огромное спасибо .

Все работает идеально.

Благодарю за оперативную помощь. Автор — exel
Дата добавления — 25.08.2013 в 21:16

Источник

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

Этот учебник рассказывает о главных преимуществах функций ИНДЕКС и ПОИСКПОЗ в Excel, которые делают их более привлекательными по сравнению с ВПР. Вы увидите несколько примеров формул, которые помогут Вам легко справиться со многими сложными задачами, перед которыми функция ВПР бессильна.

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

Зачем нам это? – спросите Вы. Да, потому что ВПР – это не единственная функция поиска в Excel, и её многочисленные ограничения могут помешать Вам получить желаемый результат во многих ситуациях. С другой стороны, функции ИНДЕКС и ПОИСКПОЗ – более гибкие и имеют ряд особенностей, которые делают их более привлекательными, по сравнению с ВПР.

Базовая информация об ИНДЕКС и ПОИСКПОЗ

Так как задача этого учебника – показать возможности функций ИНДЕКС и ПОИСКПОЗ для реализации вертикального поиска в Excel, мы не будем задерживаться на их синтаксисе и применении.

Приведём здесь необходимый минимум для понимания сути, а затем разберём подробно примеры формул, которые показывают преимущества использования ИНДЕКС и ПОИСКПОЗ вместо ВПР.

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

Каждый аргумент имеет очень простое объяснение:

  • array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
  • row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
  • column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

Если указаны оба аргумента, то функция ИНДЕКС возвращает значение из ячейки, находящейся на пересечении указанных строки и столбца.

Вот простейший пример функции INDEX (ИНДЕКС):

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.

Очень просто, правда? Однако, на практике Вы далеко не всегда знаете, какие строка и столбец Вам нужны, и поэтому требуется помощь функции ПОИСКПОЗ.

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

  • lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
  • lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
  • match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ, хотите ли Вы найти точное или приблизительное совпадение:
    • 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
    • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС/ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
    • -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

Теперь, когда Вам известна базовая информация об этих двух функциях, полагаю, что уже становится понятно, как функции ПОИСКПОЗ и ИНДЕКС могут работать вместе. ПОИСКПОЗ определяет относительную позицию искомого значения в заданном диапазоне ячеек, а ИНДЕКС использует это число (или числа) и возвращает результат из соответствующей ячейки.

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX( столбец из которого извлекаем ,(MATCH ( искомое значение , столбец в котором ищем ,0))
=ИНДЕКС( столбец из которого извлекаем ;(ПОИСКПОЗ( искомое значение ; столбец в котором ищем ;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

Теперь давайте разберем, что делает каждый элемент этой формулы:

  • Функция MATCH (ПОИСКПОЗ) ищет значение «Japan» в столбце B, а конкретно – в ячейках B2:B10, и возвращает число 3, поскольку «Japan» в списке на третьем месте.
  • Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?

В данном случае – смысла нет! Цель этого примера – исключительно демонстрационная, чтобы Вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают в паре. Последующие примеры покажут Вам истинную мощь связки ИНДЕКС и ПОИСКПОЗ, которая легко справляется с многими сложными ситуациями, когда ВПР оказывается в тупике.

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: Как находить значения, которые находятся слева покажет эту возможность в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP(«lookup value»,A1:C10,2)
=ВПР(«lookup value»;A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.

Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.

С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.

ИНДЕКС и ПОИСКПОЗ – примеры формул

Теперь, когда Вы понимаете причины, из-за которых стоит изучать функции ПОИСКПОЗ и ИНДЕКС, давайте перейдём к самому интересному и увидим, как можно применить теоретические знания на практике.

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

Любой учебник по ВПР твердит, что эта функция не может смотреть влево. Т.е. если просматриваемый столбец не является крайним левым в диапазоне поиска, то нет шансов получить от ВПР желаемый результат.

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

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

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

    Подсказка: Правильным решением будет всегда использовать абсолютные ссылки для ИНДЕКС и ПОИСКПОЗ, чтобы диапазоны поиска не сбились при копировании формулы в другие ячейки.

    Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

    Вы можете вкладывать другие функции Excel в ИНДЕКС и ПОИСКПОЗ, например, чтобы найти минимальное, максимальное или ближайшее к среднему значение. Вот несколько вариантов формул, применительно к таблице из предыдущего примера:

    1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

    2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

    3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

    О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

    Используя функцию СРЗНАЧ в комбинации с ИНДЕКС и ПОИСКПОЗ, в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в случае, если Вы не уверены, что просматриваемый диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.

    • Если указываете 1, значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
    • Если указываете -1, значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

    В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

    Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

    Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

    В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

    Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

    И я поздравляю тех из Вас, кто догадался!

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

    =INDEX( Ваша таблица ,(MATCH( значение для вертикального поиска , столбец, в котором искать ,0)),(MATCH( значение для горизонтального поиска , строка в которой искать ,0))
    =ИНДЕКС( Ваша таблица ,(MATCH( значение для вертикального поиска , столбец, в котором искать ,0)),(MATCH( значение для горизонтального поиска , строка в которой искать ,0))

    Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

    А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

    Хорошо, давайте запишем формулу. Когда мне нужно создать сложную формулу в Excel с вложенными функциями, то я сначала каждую вложенную записываю отдельно.

    Итак, начнём с двух функций ПОИСКПОЗ, которые будут возвращать номера строки и столбца для функции ИНДЕКС:

      ПОИСКПОЗ для столбца – мы ищем в столбце B, а точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

    Результатом этой формулы будет 4, поскольку «USA» – это 4-ый элемент списка в столбце B (включая заголовок).
    ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:

    Результатом этой формулы будет 5, поскольку «2015» находится в 5-ом столбце.

    Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

    Если заменить функции ПОИСКПОЗ на значения, которые они возвращают, формула станет легкой и понятной:

    Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

    Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

    В учебнике по ВПР мы показывали пример формулы с функцией ВПР для поиска по нескольким критериям. Однако, существенным ограничением такого решения была необходимость добавлять вспомогательный столбец. Хорошая новость: формула ИНДЕКС/ПОИСКПОЗ может искать по значениям в двух столбцах, без необходимости создания вспомогательного столбца!

    Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

    Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

    Эта формула сложнее других, которые мы обсуждали ранее, но вооруженные знанием функций ИНДЕКС и ПОИСКПОЗ Вы одолеете ее. Самая сложная часть – это функция ПОИСКПОЗ, думаю, её нужно объяснить первой.

    MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)
    ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)

    В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

    • Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
    • Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
    • Далее, мы делаем то же самое для значений столбца B (Product).
    • Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

    Теперь понимаете, почему мы задали 1, как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.

    Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.

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

    Если всё сделано верно, Вы получите результат как на рисунке ниже:

    ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

    Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

    Синтаксис функции ЕСЛИОШИБКА очень прост:

    Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

    Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

    =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
    «Совпадений не найдено. Попробуйте еще раз!») =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
    «Совпадений не найдено. Попробуйте еще раз!»)

    И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

    Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:

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

    Источник

    Adblock
    detector

    • Источник

    Как по значению одной ячейки, вывести название столбца

    dansant

    		 Дата: Понедельник, 28.05.2012, 01:00 |
    Сообщение № 1

    Группа: Пользователи

    Ранг: Прохожий

    Сообщений: 4


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    Доброго времени суток всем,

    Долго лопатил ваш сайт, но к сожалению ничего похожего на нужный мне ответ пока не нашел.. Поэтому, прошу меня простить если такой вопрос уже встречался:

    Как по минимальному значению одной ячейки, вывести название столбца (в другой ячейке), откуда было взято это значение?

    Пример из файла:

    В колонке «Best Rate» из прикрепленного файла указано наименьшее значение строки относительно определенного парамета (20s, 40s, 40hc).
    Т.е. если в ячейке D3 значение = 2200 (20s, название NYK), то как в ячейке A3 вывести название к которому это значение принадлежит (в данном случае NYK)?

    Заранее всем низкий поклон за помощь..
     

    Ответить

    Michael_S

    		 Дата: Понедельник, 28.05.2012, 03:03 |
    Сообщение № 2

    Группа: Друзья

    Ранг: Старожил

    Сообщений: 2012


    Репутация:
    373
    ±

    Замечаний:
    0% ±


    Excel2016

    Проверяйте

    К сообщению приложен файл:

    dansant.xls
    (74.5 Kb)
     

    Ответить

    Serge_007

    		 Дата: Понедельник, 28.05.2012, 09:47 |
    Сообщение № 3

    Группа: Админы

    Ранг: Местный житель

    Сообщений: 15888


    Репутация:
    2623
    ±

    Замечаний:
    ±


    Excel 2016

    Вариант покороче:
    [vba]

    Code

    =ИНДЕКС($I$1:$AF$1;;СУММПРОИЗВ((D3=$I3:$AF3)*(A$2=$I$2:$AF$2)*СТОЛБЕЦ($A:$X))-СТОЛБЕЦ()+1)

    [/vba]

    ЗЫ Кстати, dansant, неплохо было бы указать, что начало этой темы здесь

    К сообщению приложен файл:

    dansant2.xls
    (63.5 Kb)

    ЮMoney:41001419691823 | WMR:126292472390

     

    Ответить

    dansant

    		 Дата: Понедельник, 28.05.2012, 11:35 |
    Сообщение № 4

    Группа: Пользователи

    Ранг: Прохожий

    Сообщений: 4


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    Всем большое спасибо за помощь!!
     

    Ответить

    dansant

    		 Дата: Вторник, 29.05.2012, 01:33 |
    Сообщение № 5

    Группа: Пользователи

    Ранг: Прохожий

    Сообщений: 4


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    Quote (Serge_007)

    Вариант покороче:
    =ИНДЕКС($I$1:$AF$1;;СУММПРОИЗВ((D3=$I3:$AF3)*(A$2=$I$2:$AF$2)*СТОЛБЕЦ($A:$X))-СТОЛБЕЦ()+1)

    ЗЫ Кстати, dansant, неплохо было бы указать, что начало этой темы здесь

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

    Простите мне мою тупость, но позвольте задать вам вопрос (если это офф топик — вам решать):

    Ваша формула гораздо короче , на мой субъективный взгляд, чем формула уважаемого Michael_S (спасибо ему отдельное), не могли бы вы мне это обьяснить?

    Просто сегодня почти целый день провел за чтением значения функции (или формулы? smile INDEX, и все что с ней связанно, но видимо не судьба..

    «Функция INDEX — Дает возможность выбрать значение нужной ячейки в массиве данных.»

    С уважением,
     

    Ответить

    Michael_S

    		 Дата: Вторник, 29.05.2012, 01:53 |
    Сообщение № 6

    Группа: Друзья

    Ранг: Старожил

    Сообщений: 2012


    Репутация:
    373
    ±

    Замечаний:
    0% ±


    Excel2016

    В случае, если есть два МИН в строке, формула

    Code

    =ИНДЕКС($I$1:$AF$1;;СУММПРОИЗВ((D3=$I3:$AF3)*(A$2=$I$2:$AF$2)*СТОЛБЕЦ($A:$X))-СТОЛБЕЦ()+1)

    вернет неверный результат (скорее всего — «0»)
     

    Ответить

    Serge_007

    		 Дата: Вторник, 29.05.2012, 09:49 |
    Сообщение № 7

    Группа: Админы

    Ранг: Местный житель

    Сообщений: 15888


    Репутация:
    2623
    ±

    Замечаний:
    ±


    Excel 2016

    Quote (dansant)

    не думал, что эти два сайта связаны

    Все сайты одинаковой тематики связаны между собой. В основном их постоянными посетителями smile

    Quote (dansant)

    Ваша формула гораздо короче, не могли бы вы мне это обьяснить?

    Объяснить что? Что моя формула короче? Вы и так это заметили smile

    Quote (dansant)

    «Функция INDEX — Дает возможность выбрать значение нужной ячейки в массиве данных.»

    Абсолютно верно. И в данном случае тоже так происходит. Из массива выбирается нужный столбец:

    Quote (Michael_S)

    В случае, если есть два МИН

    Миш, о каких МИН речь?

    К сообщению приложен файл:

    3210821.gif
    (25.8 Kb)

    ЮMoney:41001419691823 | WMR:126292472390

     

    Ответить

    Michael_S

    		 Дата: Вторник, 29.05.2012, 10:07 |
    Сообщение № 8

    Группа: Друзья

    Ранг: Старожил

    Сообщений: 2012


    Репутация:
    373
    ±

    Замечаний:
    0% ±


    Excel2016

    Quote (Serge_007)

    Миш, о каких МИН речь?
     

    Ответить

    Serge_007

    		 Дата: Вторник, 29.05.2012, 10:22 |
    Сообщение № 9

    Группа: Админы

    Ранг: Местный житель

    Сообщений: 15888


    Репутация:
    2623
    ±

    Замечаний:
    ±


    Excel 2016

    Это и так понятно. В примере повторов не было, я их и не учитывал smile
    Я спросил что такое «МИН»?

    ЮMoney:41001419691823 | WMR:126292472390

     

    Ответить

    Michael_S

    		 Дата: Вторник, 29.05.2012, 10:31 |
    Сообщение № 10

    Группа: Друзья

    Ранг: Старожил

    Сообщений: 2012


    Репутация:
    373
    ±

    Замечаний:
    0% ±


    Excel2016

    Quote (Serge_007)

    Я спросил что такое «МИН»?

    «МИН»=минимальных (в данном случае.
    Лень писать было wink
     

    Ответить

    dansant

    		 Дата: Вторник, 29.05.2012, 10:41 |
    Сообщение № 11

    Группа: Пользователи

    Ранг: Прохожий

    Сообщений: 4


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    Все, понял..!! Благодарю за помощь и доходчивое обьяснение. Удачи вам!!
     

    Ответить

    serg_but

    		 Дата: Воскресенье, 10.02.2013, 17:05 |
    Сообщение № 12

    Группа: Пользователи

    Ранг: Прохожий

    Сообщений: 3


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    Добрый день! Поясните новичку для чего нужна формула — СУММПРОИЗВ((D3=$I3:$AF3)*(A$2=$I$2:$AF$2)*СТОЛБЕЦ($A:$X))-СТОЛБЕЦ()+1) и в частности не понятна здесь функция СТОЛБЕЦ($A:$X))-СТОЛБЕЦ(). Помогите, пжл, правильно настроить формулу в приложенном файле. Заранее благодарен.

    К сообщению приложен файл:

    1064662.xlsx
    (11.0 Kb)
     

    Ответить

    ABC

    		 Дата: Воскресенье, 10.02.2013, 20:05 |
    Сообщение № 13

    Группа: Друзья

    Ранг: Обитатель

    Сообщений: 397


    Репутация:
    112
    ±

    Замечаний:
    0% ±


    Excel 2007

    я не понял задачу, но уверен что Вашем файле использовать СТОЛБЕЦ() без полезно…
    пробуйте через вычисление формул

    MS Excel 2007 and 2010…
    ——————————-
    С Уважением, Даулет

     

    Ответить

    AlexM

    		 Дата: Воскресенье, 10.02.2013, 20:29 |
    Сообщение № 14

    Группа: Друзья

    Ранг: Участник клуба

    Сообщений: 4257


    Репутация:
    1046
    ±

    Замечаний:
    0% ±


    Excel 2003

    Формула для А3

    Код

    =ИНДЕКС($F$1:$J$1;;СУММПРОИЗВ((B3=$F3:$K3)*(A$2=$F$2:$K$2)*СТОЛБЕЦ($A:$F)))


    Номер мобильного модема (без голосовой связи)
    9269171249 МегаФон, Московский регион.

     

    Ответить

    serg_but

    		 Дата: Воскресенье, 10.02.2013, 22:01 |
    Сообщение № 15

    Группа: Пользователи

    Ранг: Прохожий

    Сообщений: 3


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    AlexM, низкий поклон за помощь — получил то что хотел. Но хочется понять логику формулы: 1. Для чего используется функция СУММПРОИЗВ аргументом в формуле функции ИНДЕКС. 2. Для чего используется функция СТОЛБЕЦ аргументом в формуле функции СУММПРОИЗВ. 3. Почему аргументом функции СТОЛБЕЦ указан диапазон $A:$F а не какой-то другой. Пжл объясните чайнику.
     

    Ответить

    AlexM

    		 Дата: Воскресенье, 10.02.2013, 22:16 |
    Сообщение № 16

    Группа: Друзья

    Ранг: Участник клуба

    Сообщений: 4257


    Репутация:
    1046
    ±

    Замечаний:
    0% ±


    Excel 2003

    В вашем файле, в диапазоне F9:K11 показано что возвращает каждый фрагмент формулы
    Как видно внутри функции СУММПРОИЗВ все фрагменты перемножаются, а потом суммируются. Происходит то, что отражается в названии функции СУММируютсяПРОИЗВедения
    почему СТОЛБЕЦ($A:$F). Столбец А — 1-ый, Столбец В — 2-ой, С — 3 и тд
    Функция СУММПРОИЗВ вернет значение номер элемента массива $F$1:$J$1


    Номер мобильного модема (без голосовой связи)
    9269171249 МегаФон, Московский регион.

     

    Ответить

    serg_but

    		 Дата: Воскресенье, 10.02.2013, 22:45 |
    Сообщение № 17

    Группа: Пользователи

    Ранг: Прохожий

    Сообщений: 3


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    AlexM, еще раз спасибо!
     

    Ответить

    exel

    		 Дата: Воскресенье, 25.08.2013, 00:56 |
    Сообщение № 18

    Группа: Пользователи

    Ранг: Новичок

    Сообщений: 18


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    Excel 2003

    Всем доброй ночи!!!

    Вот сижу — ломаю голову… Никак вышеуказанные формулы у меня не работают. Есть нескольно ньюансов.

    1. Нужно из строки найти мин значение, по некоторым столбцам эти мин значения повторяются.

    2. В самом первом столбце не во всех строках есть значения.

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

    К сообщению приложен файл:

    9876245.xls
    (32.0 Kb)
     

    Ответить

    Nic70y

    		 Дата: Воскресенье, 25.08.2013, 09:21 |
    Сообщение № 19

    Группа: Друзья

    Ранг: Экселист

    Сообщений: 8132


    Репутация:
    1998
    ±

    Замечаний:
    0% ±


    Excel 2010


    формулой? я даже не знаю…

    К сообщению приложен файл:

    236.xls
    (37.5 Kb)

    ЮMoney 41001841029809

     

    Ответить

    exel

    		 Дата: Воскресенье, 25.08.2013, 12:10 |
    Сообщение № 20

    Группа: Пользователи

    Ранг: Новичок

    Сообщений: 18


    Репутация:
    0
    ±

    Замечаний:
    0% ±


    Excel 2003

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

    Хочу, чтобы в отдельной колонке считалась минимальная ставка (это понятно как сделать) и в отдельном столбце указан был перевозчик, у которого такая низкая ставка.
    А если, допустим, получается что несколько перевозчиков предоставляют одинаково низкие тарифы, то должно быть в этой колонке они и выскакивать вместе.

    К сообщению приложен файл:

    0271642.xls
    (30.5 Kb)
     

    Ответить
    Источник

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

    Описание

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

    Создание образца листа

    В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.

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

    A

    B

    C

    D

    E

    1

    Имя

    Правитель

    Возраст

    Поиск значения

    2

    Анри

    501

    Плот

    Иванов

    3

    Стэн

    201

    19

    4

    Иванов

    101

    максималь

    5

    Ларри

    301

    составляет

    Определения терминов

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

    Термин

    Определение

    Пример

    Массив таблиц

    Вся таблица подстановки

    A2: C5

    Превышающ

    Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

    E2

    Просматриваемый_массив
    -или-
    Лукуп_вектор

    Диапазон ячеек, которые содержат возможные значения подстановки.

    A2: A5

    Номер_столбца

    Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.

    3 (третий столбец в инфо_таблица)

    Ресулт_аррай
    -или-
    Ресулт_вектор

    Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

    C2: C5

    Интервальный_просмотр

    Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

    ЛОЖЬ

    Топ_целл

    Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.

    Оффсет_кол

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

    Функции

    LOOKUP ()

    Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.

    Ниже приведен пример синтаксиса формулы подСТАНОВКи.

       = Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)


    Следующая формула находит возраст Марии на листе «образец».

       = ПРОСМОТР (E2; A2: A5; C2: C5)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

    Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

    Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
     

    Использование функции Просмотр в Excel

    ВПР ()

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

    Ниже приведен пример синтаксиса формулы ВПР :

        = ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

    Следующая формула находит возраст Марии на листе «образец».

       = ВПР (E2; A2: C5; 3; ЛОЖЬ)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

    Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
     

    Как найти точное совпадение с помощью функций ВПР или ГПР

    INDEX () и MATCH ()

    Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.

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

        = Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

    Следующая формула находит возраст Марии на листе «образец».


    = ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

    Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
    Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:

    Поиск данных в таблице с помощью функции индекс

    СМЕЩ () и MATCH ()

    Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

    Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.

       = СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

    Эта формула находит возраст Марии на листе «образец».

       = СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

    Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

    Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
     

    Использование функции СМЕЩ

    Нужна дополнительная помощь?

    Источник

    MS Excel columns have a pattern like A, B, C, …, Z, AA, AB, AC, …., AZ, BA, BB, … ZZ, AAA, AAB ….. etc. In other words, column 1 is named “A”, column 2 as “B”, and column 27 as “AA”.
    Given a column number, find its corresponding Excel column name. The following are more examples.

    Input          Output
 26             Z
 51             AY
 52             AZ
 80             CB
 676            YZ
 702            ZZ
 705            AAC

    Thanks to Mrigank Dembla for suggesting the below solution in a comment.
    Suppose we have a number n, let’s say 28. so corresponding to it we need to print the column name. We need to take the remainder with 26. 

    If the remainder with 26 comes out to be 0 (meaning 26, 52, and so on) then we put ‘Z’ in the output string and new n becomes n/26 -1 because here we are considering 26 to be ‘Z’ while in actuality it’s 25th with respect to ‘A’.

    Similarly, if the remainder comes out to be non-zero. (like 1, 2, 3, and so on) then we need to just insert the char accordingly in the string and do n = n/26.

    Finally, we reverse the string and print. 

    Example: 
    n = 700
    The remainder (n%26) is 24. So we put ‘X’ in the output string and n becomes n/26 which is 26. 
    Remainder (26%26) is 0. So we put ‘Z’ in the output string and n becomes n/26 -1 which is 0.

    Following is the implementation of the above approach.

    C++

    #include <bits/stdc++.h>

    #define MAX 50

    using namespace std;

    void printString(int n)

    {

        char str[MAX];

        int i = 0;

        while (n > 0) {

            int rem = n % 26;

            if (rem == 0) {

                str[i++] = 'Z';

                n = (n / 26) - 1;

            }

            else

            {

                str[i++] = (rem - 1) + 'A';

                n = n / 26;

            }

        }

        str[i] = '';

        reverse(str, str + strlen(str));

        cout << str << endl;

        return;

    }

    int main()

    {

        printString(26);

        printString(51);

        printString(52);

        printString(80);

        printString(676);

        printString(702);

        printString(705);

        return 0;

    }

    Java

    public class ExcelColumnTitle {

        private static void printString(int columnNumber)

        {

            StringBuilder columnName = new StringBuilder();

            while (columnNumber > 0) {

                int rem = columnNumber % 26;

                if (rem == 0) {

                    columnName.append("Z");

                    columnNumber = (columnNumber / 26) - 1;

                }

                else

                {

                    columnName.append((char)((rem - 1) + 'A'));

                    columnNumber = columnNumber / 26;

                }

            }

            System.out.println(columnName.reverse());

        }

        public static void main(String[] args)

        {

            printString(26);

            printString(51);

            printString(52);

            printString(80);

            printString(676);

            printString(702);

            printString(705);

        }

    }

    Python

    MAX = 50

    def printString(n):

        string = [""] * MAX

        i = 0

        while n > 0:

            rem = n % 26

            if rem == 0:

                string[i] = 'Z'

                i += 1

                n = (n / 26) - 1

            else:

                string[i] = chr((rem - 1) + ord('A'))

                i += 1

                n = n / 26

        string[i] = ''

        string = string[::-1]

        print "".join(string)

    printString(26)

    printString(51)

    printString(52)

    printString(80)

    printString(676)

    printString(702)

    printString(705)

    C#

    using System;

    class GFG{

    static String reverse(String input)

    {

        char[] reversedString = input.ToCharArray();

        Array.Reverse(reversedString);

        return new String(reversedString);

    }

    private static void printString(int columnNumber)

    {

        String columnName = "";

        while (columnNumber > 0)

        {

            int rem = columnNumber % 26;

            if (rem == 0)

            {

                columnName += "Z";

                columnNumber = (columnNumber / 26) - 1;

            }

            else

            {

                columnName += (char)((rem - 1) + 'A');

                columnNumber = columnNumber / 26;

            }

        }

        columnName = reverse(columnName);

        Console.WriteLine(columnName.ToString());

    }

    public static void Main(String[] args)

    {

        printString(26);

        printString(51);

        printString(52);

        printString(80);

        printString(676);

        printString(702);

        printString(705);

    }

    }

    Javascript

    <script>

    function printString(columnNumber)

    {

            let columnName = [];

            while (columnNumber > 0) {

                let rem = columnNumber % 26;

                if (rem == 0) {

                    columnName.push("Z");

                    columnNumber = Math.floor(columnNumber / 26) - 1;

                }

                else

                {

                    columnName.push(String.fromCharCode((rem - 1) + 'A'.charCodeAt(0)));

                    columnNumber = Math.floor(columnNumber / 26);

                }

            }

            document.write(columnName.reverse().join("")+"<br>");

    }

    printString(26);

    printString(51);

    printString(52);

    printString(80);

    printString(676);

    printString(702);

    printString(705);

    </script>

    Output

    Z
AY
AZ
CB
YZ
ZZ
AAC

    Time Complexity: O(log26n), as we are using a loop and in each traversal, we decrement by floor division of 26.

    Auxiliary Space: O(50), as we are using extra space for storing the result.
    Method 2 
    The problem is similar to converting a decimal number to its binary representation but instead of a binary base system where we have two digits only 0 and 1, here we have 26 characters from A-Z.
    So, we are dealing with base 26 instead of base binary. 
    That’s not where the fun ends, we don’t have zero in this number system, as A represents 1, B represents 2 and so on Z represents 26. 
    To make the problem easily understandable, we approach the problem in two steps:

    1. Convert the number to base 26 representation, considering we have 0 also in the system.
    2. Change the representation to the one without having 0 in its system.

    HOW? Here is an example

    Step 1: 
    Consider we have number 676, How to get its representation in the base 26 system? In the same way, we do for a binary system, Instead of division and remainder by 2, we do division and remainder by 26.

    Base 26 representation of 676 is : 100

    Step2
    But Hey, we can’t have zero in our representation. Right? Because it’s not part of our number system. How do we get rid of zero? Well it’s simple, but before doing that let’s remind one simple math trick:

    Subtraction: 
5000 - 9, How do you subtract 9 from 0 ? You borrow
from next significant bit, right.
    • In a decimal number system to deal with zero, we borrow 10 and subtract 1 from the next significant.
    • In the Base 26 Number System to deal with zero, we borrow 26 and subtract 1 from the next significant bit.

    So Convert 10026 to a number system that does not have ‘0’, we get (25 26)26 
    Symbolic representation of the same is: YZ 

    Here is the implementation of the same:

    C++

    #include <iostream>

    using namespace std;

    void printString(int n)

    {

        int arr[10000];

        int i = 0;

        while (n) {

            arr[i] = n % 26;

            n = n / 26;

            i++;

        }

        for (int j = 0; j < i - 1; j++) {

            if (arr[j] <= 0) {

                arr[j] += 26;

                arr[j + 1] = arr[j + 1] - 1;

            }

        }

        for (int j = i; j >= 0; j--) {

            if (arr[j] > 0)

                cout << char('A' + arr[j] - 1);

        }

        cout << endl;

    }

    int main()

    {

        printString(26);

        printString(51);

        printString(52);

        printString(80);

        printString(676);

        printString(702);

        printString(705);

        return 0;

    }

    Java

    import java.util.*;

    class GFG{

    static void printString(int n)

    {

        int []arr = new int[10000];

        int i = 0;

        while (n > 0)

        {

            arr[i] = n % 26;

            n = n / 26;

            i++;

        }

        for(int j = 0; j < i - 1; j++)

        {

            if (arr[j] <= 0)

            {

                arr[j] += 26;

                arr[j + 1] = arr[j + 1] - 1;

            }

        }

        for(int j = i; j >= 0; j--)

        {

            if (arr[j] > 0)

                System.out.print(

                    (char)('A' + arr[j] - 1));

        }

        System.out.println();

    }

    public static void main(String[] args)

    {

        printString(26);

        printString(51);

        printString(52);

        printString(80);

        printString(676);

        printString(702);

        printString(705);

    }

    }

    Python3

    def printString(n):

        arr = [0] * 10000

        i = 0

        while (n > 0):

            arr[i] = n % 26

            n = int(n // 26)

            i += 1

        for j in range(0, i - 1):

            if (arr[j] <= 0):

                arr[j] += 26

                arr[j + 1] = arr[j + 1] - 1

        for j in range(i, -1, -1):

            if (arr[j] > 0):

                print(chr(ord('A') +

                      (arr[j] - 1)), end = "");

        print();

    if __name__ == '__main__':

        printString(26);

        printString(51);

        printString(52);

        printString(80);

        printString(676);

        printString(702);

        printString(705);

    C#

    using System;

    class GFG{

    static void printString(int n)

    {

      int []arr = new int[10000];

      int i = 0;

      while (n > 0)

      {

        arr[i] = n % 26;

        n = n / 26;

        i++;

      }

      for(int j = 0; j < i - 1; j++)

      {

        if (arr[j] <= 0)

        {

          arr[j] += 26;

          arr[j + 1] = arr[j + 1] - 1;

        }

      }

      for(int j = i; j >= 0; j--)

      {

        if (arr[j] > 0)

          Console.Write((char)('A' +

                         arr[j] - 1));

      }

      Console.WriteLine();

    }

    public static void Main(String[] args)

    {

      printString(26);

      printString(51);

      printString(52);

      printString(80);

      printString(676);

      printString(702);

      printString(705);

    }

    }

    Javascript

    <script>

    function printString(n){

        let arr = [];

        let i = 0;

        while (n) {

            arr[i] = n % 26;

            n = Math.floor(n / 26);

            i++;

        }

        for (let j = 0; j < i - 1; j++) {

            if (arr[j] <= 0) {

                arr[j] += 26;

                arr[j + 1] = arr[j + 1] - 1;

            }

        }

        let ans = '';

        for (let j = i; j >= 0; j--) {

            if (arr[j] > 0)

                ans += String.fromCharCode(65 + arr[j] - 1);

        }

        document.write(ans + "<br>");

    }

    printString(26);

    printString(51);

    printString(52);

    printString(80);

    printString(676);

    printString(702);

    printString(705);

    </script>

    Output

    Z
AY
AZ
CB
YZ
ZZ
AAC

    Time Complexity: O(log26n), as we are using a loop and in each traversal, we decrement by floor division of 26.

    Auxiliary Space: O(10000), as we are using extra space for the array.

    Method 3:

    We can use a recursive function which definitely reduces the time and  increase the efficiency:

    Alphabets are in sequential order like: ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’. You have experienced while using excel when you see columns and rows numbering are done in  Alphabetical ways.

    Here’s How I purposefully think about the logic of how it is arranged.

    (In Mathematical  terms, [a , b ] means from ‘a’ to ‘b’).

    [1,26] = [A,Z] (Understand by ‘1’ stands for ‘A’ and ’26” stands for “Z”). For [27,52] ,it will be like [AA,AZ], For [57,78] it will be [BA,BZ]

    Logic is to append an Alphabet sequentially whenever it ends up numbering at 26.

    For example, if the number is ’27’ which is greater than  ’26’, then we simply need to divide by 26, and we get the remainder as 1, We see “1” as “A” and can be recursively done.

    we will be using python for this.

    Algorithm is:

    1. Take an array and Sort the letters from A to Z . (You can also use the import string and string function to get “A to Z” in uppercase.)

    2. If the number is less than or equal to ’26’, simply get the letter from the array and print it.

    3. If it is greater than 26, use the Quotient  Remainder rule, if the remainder is zero, there are 2 possible ways, if the quotient is “1”, simply hash out the letter from the index [r-1]( ‘r’ is remainder), else call out the function from the num =(q-1) and append at the front to the letter indexing [r-1].

    4. If the remainder is not equal to “0”, call the function for the num = (q) and append at the front to the letter indexing [r-1].

    The code concerned with this is:

    C++

    #include<bits/stdc++.h>

    using namespace std;

    string alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    string num_hash(int num){

        if(num < 26){

          string res = "";

          res += alpha[num-1];

          return res;

        }

        else{

          int q = (num / 26);

          int r = num % 26;

          string res = "";

          if(r == 0){

            if(q == 1){

              res.append(1,alpha[(26 + r-1)%26]);

            }

            else{

              res = num_hash(q-1);

              res.append(1,alpha[(26 + r-1)%26]);

            }

          }

          else{

            res = num_hash(q);

            res.append(1,alpha[(26 + r-1)%26]);

          }

          return res;

        }

    }

    int main () {

        cout<< num_hash(26) << endl;

        cout<< num_hash(51) << endl;

        cout<< num_hash(52) << endl;

        cout<< num_hash(80) << endl;

        cout<< num_hash(676) << endl;

        cout<< num_hash(702) << endl;

        cout<< num_hash(705) << endl;

        return 0;

    }

    Java

    import java.io.*;

    class GFG

    {

      static String alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

      static String num_hash(int num){

        if(num < 26)

          return Character.toString(alpha.charAt(num-1));

        else{

          int q = Math.floorDiv(num, 26);

          int r = num % 26;

          if(r == 0){

            if(q == 1){

              return Character.toString(alpha.charAt((26 + r-1)%26));

            }

            else

              return num_hash(q-1) + alpha.charAt((26 + r-1)%26);

          }

          else

            return num_hash(q) + alpha.charAt((26 + r-1)%26);

        }

      }

      public static void main (String[] args) {

        System.out.println(num_hash(26));

        System.out.println(num_hash(51));

        System.out.println(num_hash(52));

        System.out.println(num_hash(80));

        System.out.println(num_hash(676));

        System.out.println(num_hash(702));

        System.out.println(num_hash(705));

      }

    }

    Python3

    alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    def num_hash(num):

        if num < 26:

            return alpha[num-1]

        else:

            q, r = num//26, num % 26

            if r == 0:

                if q == 1:

                    return alpha[r-1]

                else:

                    return num_hash(q-1) + alpha[r-1]

            else:

                return num_hash(q) + alpha[r-1]

    print(num_hash(26))

    print(num_hash(51))

    print(num_hash(52))

    print(num_hash(80))

    print(num_hash(676))

    print(num_hash(702))

    print(num_hash(705))

    C#

    using System;

    class GFG

    {

    static string alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    static string num_hash(int num){

        if(num < 26)

        return Char.ToString(alpha[num-1]);

        else{

        int q = num/26;

        int r = num % 26;

        if(r == 0){

            if(q == 1){

            return Char.ToString(alpha[(26 + r-1)%26]);

            }

            else

            return num_hash(q-1) + alpha[(26 + r-1)%26];

        }

        else

            return num_hash(q) + alpha[(26 + r-1)%26];

        }

    }

    public static void Main(String[] args) {

        Console.WriteLine(num_hash(26));

        Console.WriteLine(num_hash(51));

        Console.WriteLine(num_hash(52));

        Console.WriteLine(num_hash(80));

        Console.WriteLine(num_hash(676));

        Console.WriteLine(num_hash(702));

        Console.WriteLine(num_hash(705));

    }

    }

    Javascript

    <script>

    let alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    function num_hash(num)

    {

        if(num < 26)

            return alpha[num-1]

        else{

            let q = Math.floor(num/26),r = num % 26

            if(r == 0){

                if(q == 1)

                    return alpha[(26 + r-1)]

                else

                    return num_hash(q-1) + alpha[(26 + r-1)]

            }

            else

                return num_hash(q) + alpha[r-1]

        }

    }

    document.write(num_hash(26),"</br>")

    document.write(num_hash(51),"</br>")

    document.write(num_hash(52),"</br>")

    document.write(num_hash(80),"</br>")

    document.write(num_hash(676),"</br>")

    document.write(num_hash(702),"</br>")

    document.write(num_hash(705),"</br>")

    </script>

    Output

    Z
AY
AZ
CB
YZ
ZZ
AAC

    Time Complexity: O(log26n), as we are using recursion and in each recursive call, we decrement by floor division of 26.

    Auxiliary Space: O(1), as we are not using any extra space.
    Related Article : 
    Find the Excel column number from the column title
    This article is contributed by Kartik. Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.

    Источник

    Понравилась статья? Поделить с друзьями:

    А вот еще интересные статьи:

  • Excel имя сохраняемого файла
  • Excel имя созданной книги
  • Excel имя сбойного модуля kernelbase dll
  • Excel имя открытого листа
  • Excel имя на несколько ячеек

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии