Ратягивание формулы в колонке с заменой буквы показателя

Уважаемые эксперты, помогите советом! Если файл, в котором формула в колонке зависит от показателей в строке. Напр., в ячейке С6 формула =IF(D5=0;2;IF(D5=1;1;IF(D5=2;0))), а в ячейке С7 - =IF(D6=0;2;IF(D6=1;1;IF(D6=2;0))). При этом формула в строке зависит от показателей в колонкею Напр., В ячейке О19 формула =IF(Q17=0;2;IF(Q17=1;1;IF(Q17=2;0))), а в ячейке Р19 - =IF(Q18=0;2;IF(Q18=1;1;IF(Q18=2;0))). Как растянуть формулу в колонке, чтобы он менял не цифру ячейки в формуле, в букву?

Надеюсь, что объяснила понятно.

Картинка с взаимосвязью ячеек прилагается.

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

Все, мне помогли решить проблему другой формулой: =--TEXT(INDEX($C$5:$Q$19,COLUMN()-COLUMN($B6),ROW()-ROW(C$4))-1,"\0;2;1")

Спасибо за идеи

$ перед числом поставить?

В теории XL понимает алгоритм продолжения формулы по 2-3 клеткам. Попробуйте перед "протяжкой" выделить 2 клетки примера (или 3).

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

Эксель слаб

Я могу это сделать, а он нет?!

Числа прописью в Excel быстро и без вспомогательных инструментов!

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

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

Вот пример её действия:

Файл с формулой можно скачать по следующей ссылке:

Как можете заметить, в функции очень много ссылок на одну и ту же ячейку – H6. Естественно, вручную перетаскивать все ссылки H6 не вариант (их 97!). Поэтому вот как работаем с этой формулой:

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

2. Выбираем две ячейки, включая ячейку с формулой (показываю на примере самого файла по ссылке – в формуле с выводом сотых заменим ссылку на новое значение в ячейке C6):

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

3. Вызываем окно поиска и замены с помощью CTRL+H

4. В «Найти» вписываем заменяемый адрес – H6, в «Заменить на» - адрес новой нужной ячейки – например, C6

5. Нажимаем на «Заменить все». Замена происходит лишь в обеих выбранных ячейках (именно для этого мы и выбирали вторую ячейку, чтобы ограничить замену лишь на выбранный диапазон). Готово! Формула сразу ссылается на нужное значение:

Из особенностей формулы:

1. Формула абсолютно самостоятельна и не использует VBA или сторонние Add-Ins

2. Формула работает с числами до 999.999.999

3. Формула правильно склоняет слова

4. Вся формула в свою очередь состоит в основном из формул ЛЕВСИМВ, ПСТР, ЕСЛИ, ТЕКСТ, И, ВЫБОР

5. Количество символов в формуле с прописью целых и сотых: 6034

Вот в этом видео разобран принцип работы формулы, то, как её использовать, а также рассказаны еще некоторые детали, обязательно советую его посмотреть:

Формулы Excel – все особенности, тонкости и трюки в одном видео!

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

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

Excel. Как заставить прибавлять в одну ячейку определенные значения по условию?

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

Есть ячейка "итого"

Есть диапазон ячеек в которых установлены значения X, Y, Z (по одному в каждой ячейке случайным образом)

Если в ячейке из диапазона стоит X, то в ячейку "итого" прибавляется 2

Если в ячейке из диапазона стоит Y, то в ячейку "итого" прибавляется 3

Если в ячейке из диапазона стоит Z, то в ячейку "итого" прибавляется 0

| X | Y | Z | X | Z | Z | Y | X | X | Y | Y | Z | X | Z | X | X |

В ячейке "итого" должно получиться 26

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

Главный вопрос это - "Как заставить прибавлять в одну ячейку определенные значения по условию"

Если ткнете носом в статью в интернете я буду счастлив.

Визуализация принципа работы ячеек в Excel

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

Итак, пустой рабочий лист:

Первое с чего начнём, это внесём статичное значение 8 в ячейку A1. Просто выбираем её и, нажав на клавишу 8 на клавиатуре вписываем это значение в ячейку:

Под статичным значением понимается именно такое – внесенное с помощью клавиатуры, которое не изменяется.

Теперь в соседней ячейке B1 впишем уже динамически вычисляемое значение – то есть формулу. Выбрав ячейку, вписываем в ней следующее:

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

Это первый пункт, который нужно понять. Ячейки в Excel – это своего рода контейнеры, в которые помещаются либо формулы, либо статичные значения. По итогу, после «просчёта» всех контейнеров, на поверхности ячейки Excel показывает нам либо внесённое статичное значение, либо значение, вычисленное формулой. Формула же при этом всё также остаётся внутри «контейнера».

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

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

Конечно же, ссылки и статичные значения можно комбинировать:

Получается мы уже рассмотрели два уровня отображения ячеек. Первый уровень – это то, что мы видим, находясь непосредственно «внутри» наших ячеек, то есть формулы и статичные значения. Второй уровень – это результат вычисления – то есть всё тоже статичное значение или результат вычисления формулы.

И нам остаётся рассмотреть лишь последний уровень, своеобразную линзу – уровень форматирования.

Выберем ячейки из первого столбца и поменяем их формат на денежный (Главная -> Число -> Денежный):

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

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

То есть таким образом форматирование — это дополнительная линза, наложенная поверх рабочего листа, которая меняет для нас лишь отображение значения.

В этом можно кстати еще раз убедиться вот так: скопировав область значений, попробуем вставить их ниже. Щелчок ПКМ по А4 выводит следующее контекстное окно:

1 – Вставка в качестве значений: форматирование теряется, и все значения вставляются в статичном виде (то есть формулы заменяются результатами вычисления этих формул):

2 – Вставка функций: форматирование теряется, формулы вставляются формулами (следим за сдвигом ссылок! Поможет здесь F4), а статичные значения – всё также статичными значениями:

3 – Копирование форматирования: копируется лишь эта итоговая «линза» форматирования (здесь значения оставлены с предыдущей вставки):

Вот такое небольшое резюме и визуализация того, как работают ячейки в Excel.

В видео ниже я рассказал об описанном выше принципе работы ячеек в виде 3D-визуализации, а также перечислил и показал все самые важные особенности и фишки профессиональной работы с формулами и функциями в Excel, так что предлагаю также посмотреть его:

Нужен макрос или скрипт

Доброго времени суток уважаемому Сообществу.

Имеется склад. На складе - дохленький комп и сканер штрих-кода. Каждый день, на склад приезжает около 1000+ коробок, на которых наклеены этикетки с штрих-кодом.

Штрих-код выглядит так: 11540507202024780029090102.Количество цифр - постоянно.

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

Порядковый номер должен ставиться автоматически.Первая цифра в штрих-коде - № стеллажа.Следующие три цифры - № ячейки в стеллаже.Далее, восемь цифр - дата упаковки коробки.Следом, одна цифра - № смены.А вот следом, две сложности :-) 13 цифр - это код наименования товара, находящегося в коробке. Список всех наименований с кодами, расположен на соседнем листе документа. Можно расположить на этом же листе, если так будет проще.

Выглядит список вот так:

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

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

Если нужно, можно на комп, где всё это будет работать, установить какой-нибудь MS Office.

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

Если кто сможет самостоятельно сделать - пишите ([email protected]), скину исходный файл + оплачу какую-нибудь разумную денюжку. Межбанковским переводом, ибо нахожусь в ближнем забугорье и тут на картах другая валюта.

Excellama: Выпадающие списки и логические формулы

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

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

В этом примере мы разберем несколько инструментов Excel, а именно:

- логическая формула ЕСЛИ;

- формула блока «ссылки и массивы» ВПР.

Шаг 0 – введение.

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

Шаг 1 – работа с прайсом.

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

Для этого в ячейке C2 ставим наценку, допустим 1,47 (увеличение цены по сравнению с ценой закупки – 47%). Ее обязательно вывести в отдельную ячейку, потому что «а вдруг кризис» и придется все цены пересчитывать, не будем же мы всю таблицу заново просчитывать. Да и на будущее - в случае, если все строчки будут производить некоторые действия (умножение, сложение, вычитание, деление) с одной единственной ячейкой, то легче ее вывести отдельно и зафиксировать. Опять же, если мы захотим изменить наценку на все товары, то нам достаточно поменять значение только в одной этой ячейке, и вся наша таблица автоматически пересчитается.

А чтобы каждый раз в формуле вручную не ссылаться на одну и ту же ячейку (C2), то ее необходимо зафиксировать. Для этого необходимо поместить курсор в строку формул после знака умножить на C2 и нажать F4. Если в формуле появилось два (!) знака $, то ячейка зафиксирована.

Первоначальная формула в ячейке D5 будет выглядеть следующим образом =C5*$C$2

С первого взгляда все хорошо. Но это не так. В полученном результате в ячейке D5 больше двух знаков после запятой. Избавляемся от концов с помощью формулы ОКРУГЛ. Это наше первое знакомство с вложенной формулой. Формула в формуле. Самый простой способ «вложить» одно в другое – скопировать полученную формулу и следовать по инструкции ниже.

В ячейке D5 в строке формул пишем =ОКРУГЛ и открываем Аргументы функции (элемент Fx).

В поле «Число» вставляем скопированную формулу без знака «=».

В поле «Число_разрядов» ставим цифру 2, так как нам надо 2 знака после запятой.

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

Готово. Вы великолепны!

Шаг 2 – заполнение карточки заказа.

Начнем с выпадающего списка.

Выделяем диапазон, где хотим видеть выпадающий список (диапазон C8:C14). Переходим на вкладку Данные, группа Работа с данными, элемент Проверка данных.

Тип данных – список, источник – список товаров с листа «прайс» (диапазон B5:B16).

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

Есть два способа сделать нумерацию, но изначально в первую ячейку списка ставим цифру 1.

- щелкаем 2 раза за маркер автозаполнения и в появившемся окошке выбираем значение «Заполнить» - подходит при нумерации большого списка.

- тянем за маркер автозаполнения, параллельно зажав клавишу CTRL – может работать даже тогда, когда ячейка начинается не с 1, а с числа 824789, удобно продолжать нумерацию в середине списка.

Ура. Осталось два крупных шага (ВПР и логическая формула ЕСЛИ).

Для разгона давайте на ячейке F8 пропишем простую формулу умножения =D8*E8

Переходим к ВПР. Если по-простому, то формула «вертикальный поиск результата/вертикальный просмотр» берет ячейку со значением (товар в карточке заказа) и ищет ее в предложенном списке (в нашем случае в прайсе). После того, как формула нашла это значение в списке, она пробегает по этой строчке в прайсе и забирает оттуда нужное нам значение.

Если же говорить на языке формул, то все выглядит следующим образом:

Искомое значение - что ищем. Мы ищем блокнот (ячейка C8)

Таблица - где ищем. Ищем в прайсе. Указываем диапазон всей таблицы прайса (внимание: именно с ячейки B4 до ячейки D16).

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

Интервальный_просмотр - 0. Ставим 0 для получения точного результата (чтобы формула нашла конкретно "Блокнот А5", а не "блакнот а 5".

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

Пара простых шагов для финального штриха. Ставим автосумму в ячейке D15 и F15. Для упрощения действия можно запомнить следующее сочетание клавиш «ALT» и «=» (горячая клавиша для автосуммы).

В ячейке D16 считаем скидку с помощью логической формулы ЕСЛИ. Примем за правило, что если заказ собран на сумму более 5 000 руб., то скидка будет 10%.

Финальным аккордом в нашей и так уже затянувшейся песни будет простая формула

Все! С официальной частью закончили.

Небольшие советы:

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

2. Если Вас не устраивают и вездесущие нули, то избавиться от них тоже можно (не теряя при этом формулы). Заходим Файл – Параметры – Дополнительно – Показать параметры для следующего листа – Показывать нули в ячейках, которые содержат нулевые значения. Убираем галочку с данного пункта. Теперь нули не видны, но в ячейках все равно остались формулы, и при заполнении таблицы все будет считаться как раньше.

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

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

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