MS, Libreoffice & Google docs

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

Пример:

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

Есть диапазон ячеек в которых установлены значения 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 и для каждой мне надо будет создавать клона, к тому же раз в месяц диапазон ячеек с данными меняется, поэтому зеркалить таблицы отнимет много времени.

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

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

UPD: Ответ комментарий

Если в Excel, LibreOffice, Google Sheets и т.д. нужно изменить отрицательные числа на положительные, то для этого можно использовать одну универсальную методику с применением вспомогательных столбцов.

Рассмотрим на примере следующей таблицы:

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

Поэтому формулу нужно поправить. Вписываем следующее:

Прописав эту формулу, протягиваем её на соответствующий таблице диапазон:

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

Всё готово. Вспомогательные временные столбцы после этого можно удалить.

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

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

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

Для тех, кому надо сделать не только функционально, но и красиво читаемо и наглядно - в экселе (да и во всех табличных редакторах в принципе) придумано достаточно много всяких удобностей, но, готов спорить, многие их не используют. О самых простых, наверное, не будем, а вот как нарисовать солнышко выделить цветом/ форматированием строки, подходящие под какое-либо ваше условие - с удовольствием расскажу.
Для начала как это выглядит (строка 124, столбцы T ,V):

То есть, если есть какое-то условие (которое вы задаете сами) строка/столбец/любая выбранная (вами) область меняет форматирование (и тут тоже все можно настроить).

Теперь о том, как это сделать:

- Переходим на вкладку "Home" (тут прошу заранее извинить - русского Экселя под рукой нет и никогда не было, но, по логике - первая или вторая вкладка).

- Находим раздел Styles (картинка выше, самый правый), нажимаем на первый значок Conditional Formatting (что в вольном переводе "Условное форматирование"), видим вот такое выпадающее меню

- Видим набор функций, которые как раз таки и отвечают за визуальное форматирование таблицы согласно условиям. Если будет интересно - расскажу обо всех подробно, но сейчас переходим сразу к пункту Manage Rules (на русском, наверное, Редактировать правила, но не уверен). Почему сразу сюда? Создать новое правило можно и из раздела Редактирование, как и удалить тоже + вы увидите все правила форматирования, которые применены на вашей таблице. Открывается редактор правил, и вот тут начинается простор для творчества...

Для начала - общие правила применения/ отображения (ДО таблицы, пункты Show formatting rules for / Change rule order)
Show formatting rules for  - выбор правил для заданой области. Показывает все правила форматирования для выбранной области. Для примера оставьте заданное значение, далее, опять же, будут вопросы - расскажу подробнее.

Change rule order - порядок применения правил. Правила применяются по очереди, по старшинству. То есть правило #1 будет применено ПОСЛЕДНИМ, поверх правил #2 и #3.

Теперь сам набор правил:

Первый столбец - само правило (опять-же, вариантов тьма, рассмотрим ТОЛЬКО правила на основе формул. Смотрим на первую формулу

=$I2<90

Разбираемся:
"=$I2" - указывает на расположение данных. В данном случае:
- столбец безусловно $I, оператором $ указываем на абсолютный стиль при выборе столбца (это важно)
- строка 2 (тут стиль ОТНОСИТЕЛЬНЫЙ, при этом условии Эксель будет перебирать значения построково, что нам собственно и нужно)
"<90" - менее заданного значения. Тут  опять-же ньюансы на отдельную статью... В двух словах - работает ТОЛЬКО если значение цифровое (то есть 89 - сработает, а вот 89i -  не сработает). Снова - будет интересно - углублюсь и объясню разницу.

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

Четвертый столбец - Применить только для первого подходящего условия. Поясню - Эксель отформатирует только первую строку, подходящую под условие, для дальнейших данное правило НЕ СРАБОТАЕТ.

Создадим новое правило.

Для начала - выберем условие. Допустим нам нужно выделить все строки, с условием - значение столбца J в строке N БОЛЬШЕ 1

Переходим Home> Styles > Conditional Formatting > Manage Rules, снизу слева видим кнопку +, нажимаем - открывается интерфейс создание нового правила.

Style - Classic, Use a formula to... (опять-же, об остальных вариантах - отдельный разговор)
В поле вводим формулу (напомню значение столбца J в строке N БОЛЬШЕ 1)

=$J2>1

В подменю Format wit - выбираем стиль. Можно сразу переходить к последнему пункту Customised Format и дать волю фантазии.

Выбрали? Супер. Нажимаем OK, видим созданный стиль в меню управления стилями:

Задаем область применения (в моем случае вот такую):

$A$2:$AP$2037

Нажимаем OK, еще раз открываем Manage Rules и.. в большинстве случаев ловим следующий "глюк" - значение поля Formula изменилось с =$J2>1 на =$J1048576>2. Нажимаем на формулу, исправляем на =$J2>1, нажимаем OK, и еще раз OK в редакторе правил, и получаем желаемый результат:

Естественно это очень поверхностно, иначе формат статьи перерос бы в формат учебника, но суть, я думаю, более чем понятна.
Если есть вопросы / пожелания / что-то непонятно / что-то описать подробнее - в комменты)

Всё, что написано ниже, относится к VBA, реализованному внутри Excel из комплекта Office 365 по состоянию на февраль 2021 года. Скорее всего, всё обстоит так же и в остальных версиях офиса, насколько помню - с 97.

Что такое переменные, зачем они нужны и прочие базовые принципы я пропущу, здесь всё достаточно очевидно(*) даже на уровне ощущений.
Но есть одна особенность, которая неочевидна, упомянута в мануле одной строкой, и в корне отличается от реализации в других диалектах VB - например, в .NET.

Во-первых, примем как должное, что код в VBA в любом (во всех!) модулях должен начинаться со строки

Option Explicit

Это не обязательное требование, но оно сродни требованию включать поворотник при любом манёвре и пристёгиваться до начала движения: однажды оно спасёт вам жизнь, а для этого должно стать - даже не привычкой - рефлексом.

https://docs.microsoft.com/ru-ru/office/vba/language/referen...

Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.
Оператор позволяет избежать ошибок при написании имён переменных и устраняет неоднозначности при определении области видимости переменных.

Дока для .NET https://docs.microsoft.com/ru-ru/dotnet/visual-basic/languag... идёт дальше:

Setting Option Explicit to Off is generally not a good practice. You could misspell a variable name in one or more locations, which would cause unexpected results when the program is run.
Отключение является плохой практикой, вы можете неправильно написать имя переменной, что может вызвать привести к неожиданному результату при выполнении программы.

Не будем говорить про стиль написания кода - тут больше вопрос собственного удобства, но иногда хочется или необходимо объявить несколько переменных в одной строке

Простейшая задача: объявить в одной строке две строковые переменные - вызывает лютое желание написать

Dim S1, S2 as String

Эта строчка абсолютно корректна с точки зрения синтаксиса, будет работать почти всегда, однако, делает она совсем не то, что было задано. А именно, она объявляет одну строковую переменную и одну - нетипизированную.
Нетипизированные переменные, напомню, получают тот тип, который имеют записанные в них данные, и могут менять тип в процессе выполнения кода. Как любая мощная магия, они требуют понимания как оно работают и умения обращаться с ними.
По большому счёту, код с нетипизироанными переменными работает ровно так же, как и с типизированными. Отличие в том, что он делает не то, что вы задумали, а то, что понял интерпретатор из ваших косноязычных объяснений.
Пример кода, объясняющего происходящее:

Как видно, переменная S1 в начале выполнения кода вовсе не имеет типа (Empty), а потом меняет его несколько раз.
В то же время, S2 также принимает разные значения, при этом всегда оставаясь строковой. В её случае происходит неявное преобразование данных разных типов к строковому типу.
Данное поведение описано в документации по VBA https://docs.microsoft.com/ru-ru/office/vba/language/concept...

You can declare several variables in one statement. To specify a data type, you must include the data type for each variable.
Вы можете объявить несколько переменных в одном операторе. Для указания типа вы должны сделать это для каждой переменной.

Данная особенность VBA неочевидна. Более того, в VB для .NET данная конструкция ведёт себя противоположным образом: https://docs.microsoft.com/ru-ru/dotnet/visual-basic/program...

You can declare several variables in one statement without having to repeat the data type.
Вы можете определить несколько переменных в одном операторе без необходимости повторять тип данных.

Резюмируя:
1. Option Explicit - желательна и рекомендуется.

2. При определении переменных нужно учитывать особенность VBA.

------------------------------------------------------------
(*)Читать документацию нужно с самого начала, даже если кажется, что всё это знаешь.