MS, Libreoffice & Google docs

762 поста • 14 933 подписчика

Если хочется избежать SUMIFS и COUNTIFS⁠⁠

5 лет назад

Имеется простая таблица

Требуется посчитать разные комбинации, например "Север и Юг за месяц январь". Можно использовать формулы SUMIFS и COUNTIFS. Но мне они не нравятся, т.к. слишком громоздкие. Особенно если критериев много.

Поэтому часто использую следующий трюк. Работает на всех версиях Экселя, с операторами равен "=", больше ">", меньше "<", и не равен "<>". Сначала назначаем названия нашим ячейкам.

А дальше:

Счет продаж где месяц = "Январь" и регион = "Север" (Результат: 1)

{=SUM((Месяц="Январь")*(Регион="Север"))}

Счет продаж где месяц = "Январь" и регион = "Север" или регион = "Юг" ( Результат: 2)

{=SUM((Месяц="Январь")*((Регион="Север")+(Регион="Юг")))}

Сумма продаж где месяц = "Январь" и регион = "Север" (Результат: 200)

{=SUM((Месяц="Январь")*(Регион="Север")*Продажи)}

Сумма продаж между 300 и 400 (Результат: 1350)

{=SUM((Продажи>=300)*(Продажи<=400)*(Продажи))}

Ахтунг! Формула вводится комбинацией клавиш Ctrl + Shift + Enter.

Показать полностью

292

sngisback

MS, Libreoffice & Google docs

Google Таблицы: функция GOOGLEFINANCE⁠⁠

5 лет назад

Специально для Пикабу-сообщества MS, Libreoffice & Google docs :)

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

Итак, "ключ" ко всем возможностям функции - это код финансового инструмента.

Для тех, кто хочет покопаться в годной таблице, где эта функция вовсю используется - то вот вам ссылка на таблицу - просто нажмите "ФАЙЛ - СОЗДАТЬ КОПИЮ", чтобы создать свою редактируемую копию. Вот вам ссылка на другую таблицу, с данными о российских компаниях.

Давайте посмотрим, какие тут у нас есть варианты "ключа", и какие есть особенности:

1. Тикер ценной бумаги

Здесь всё просто - у каждой акции есть свой код, называемый Тикером. Формула ссылается на столбец с тикерами, и прекрасно их читает, с одним лишь "если" (см. п.2)

2. Тикер с уточнением, с какой биржи нужно брать данные

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

Но почему цена настолько сильно отличается и что за загадочная приписка "МСХ:"?

Дело в том, что Яндекс торгуется на нескольких биржах. И основная биржа для Яндекса - это американский NASDAQ, поэтому Тикер без приписок по умолчанию выдает цену Яндекса на Насдаке, в долларах США. А вот вторая цена - в рублях, и чтобы функция поняла, что именно эту цену мы от неё хотим, мы вынуждены ей подсказать, что цена нам нужна на акцию с Мосбиржи. Можно в принципе запомнить это и все тикеры с Мосбиржи писать с этим префиксом.

3. Биржевые индексы

Здесь вообще полный хаос - американские индексы с точкой в начале кода, немецкий - достается с префиксом "INDEXDB:". Возможно, где-то в Сети есть полный список индексов, но я использую один-единственный .INX

4. Курс валюты

Вот вам список всех валют, которые распознает GOOGLEFINANCE. Можете афганские афгани переводить в костариканские колоны, мало ли вам это очень нужно.

Что может "вытянуть" функция GOOGLEFINANCE? Посмотрите опять же в справке. Я использую:

"name" - название компании;

"price" - текущая цена;

"changepct" - изменение за день;

"pe" - один из мультипликаторов, отношение цены к годовой прибыли;

"eps" - прибыль на одну акцию.

Что ещё можно сделать с функцией GOOGLEFINANCE?

Используя комбинацию с функцией SPARKLINE, можно построить график изменения цены на акцию. "-365" можно изменить на другое количество дней - хоть 1095, хоть 90.

Зачем мне всё это?

Как бы это странно и возмутительно ни звучало сейчас, но у меня свой собственный "мини-пенсионный фонд", я откладываю с зарплаты средства на "пенсию", покупая бумаги на фондовом рынке, которые растут, платят дивиденды, иногда падают конечно (прекрасные моменты для покупки подешевле, жаль что так редко они бывают, эх...). И мне нужно учитывать финансы, вести статистику, держать руку на пульсе. Если вам интересно, как это вообще нахрен возможно, живя в России - подписывайтесь на блог (если телеграм не открывается, можете там найти, вбив в поиск @finindie), ну и просто нажмите на мой никнейм на Пикабу и читайте, что я там пишу.

P.S.: я знать не знаю, что из этого может MS Excel, даже не спрашивайте - а лучше сами расскажите в комментах. Я просто Экселем не пользуюсь. Google Spreadsheets бесплатен, умеет и любит тянуть данные из онлайна - не только через GOOGLEFINANCE, но и через IMPORTHTML, IMPORTXML, IMAGE, GOOGLETRANSLATE и многие другие функции. Google Spreadsheets работает из браузера везде где можно и хранит все таблицы в облаке, т.е. нет заморочки с пересылкой файла с одного устройства на другое. Да, всё это можно реализовать и в экселе, я не сомневаюсь! Но я перешел в Таблицы, и обратно не хочу ;)

Может ещё что-то рассказать о Таблицах?

Показать полностью 6

[моё] Google Табличка Финансы Инвестиции Курс валют Доллары Яндекс Длиннопост

104

151

jorra.jorra

MS, Libreoffice & Google docs

О пользе продвинутого знания Ёкселя и о программировании⁠⁠

5 лет назад

Надо внести в онлайновую базу, по работе, 180 записей. Каждая из 15 полей. Обычно мы это делали через CTRL+C/CTRL+V из Ёкселя, куда данные вбивались заранее. Т.к. обычно вносилась одна запись в неделю-две, редко больше.- А тут целых 180, причём надо, как обычно, "вчера".

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

===

Где-то минут за 40 неспешной и творческой работы наваял простенький конвертер, не выходя из Ёкселя:

- страница с настройками и константами

- страница, куда копипастится блок из рабочего файла Ёкселя, который надо экспортнуть

- страница с результатами, которая тупо сохраняется в CSV.

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

===

Затем за пару прогонов через утилиту перегнал в XML, подписал ЭЦП и загнал на сайт. Ошибок: 0.

- Это заняло у меня ещё минут 15, вместе с проверкой исходных данных и результата, подписью ЭЦП и проверки, что всё успешно залилось и открывается онлайн.

===

Стал считать, сколько набивал бы вручную. Я работаю практически только с клавы, поэтому на копипаст 1 поля уходит, пускай, 1 секунда.

- Т.е. CTRL+C ALT+TAB CTRL-V TAB ALT-TAB Right... и так много раз подряд.

- Это 15*180=2700 секунд или 45 минут непрерывной, неотрывной работы.

- Это без учёта необходимости кликнуть в браузере "Добавить запись" и в конце "Сохранить" и промотать колонки в Ёкселе, т.к. их порядок не совпадает с порядком полей на сайте.

- И без учёта возможных косяков при копипащении.

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

===

Ну и да, я НЕНАВИЖУ монотонную работу. Для меня и 10 записей подряд внести напряжно. Так что сидел бы я пару рабочих дней точно...

===

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

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

- Попробовал смоделировать её скорость работы (мышкой) - получилось секунд 10 на одно поле. Т.е. 7.5 часов, опять же - непрерывной, - работы.

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

===

Мораль простая. По возможности, изучайте программирование в целом и формулы MS Excel в частности.

- Это реально ОЧЕНЬ помогает в работе.

- И ОЧЕНЬ экономит нервы.

Показать полностью

[моё] Microsoft Excel Программирование Текст

1421

Veseliy.4el

MS, Libreoffice & Google docs

Выпадающие списки с удалением⁠⁠

5 лет назад

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

Этот приём удобно применять при распределении дежурств (товаров), чтобы не запутаться и не назначить одного человека дважды, а также наглядно видеть тех, кто остался в запасе. В итоге у нас получится вот такая таблица:

1. Сперва необходимо подсчитать, кто из имеющихся сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к серой таблице ещё один столбец, в который введём формулу: =СЧЁТЕСЛИ($B$2:$B$8;E2)

=COUNTIF($B$2:$B$8;E2)

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

2. Далее выявим свободных сотрудников. Добавим ещё один столбец и введём в него формулу, которая будет выводить номера свободных сотрудников:

=ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1)

=IF(F2-G2<=0;"";ROW(E2)-ROW($E$2)+1)

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

=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;

НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))

=IF(D2>COUNT($H$2:$H$10);"";INDEX($E$2:$E$10;

SMALL($H$2:$H$10;ROW(E2)-1)))

Эта формула выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

4. Далее создаём именованный диапазон сотрудников нажав Ctrl+F3 или на вкладке «Формулы» - «Диспетчер имён», где из столбца списка E2 зададим название «Имена», а в строке «Диапазон» введём формулу:

=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))

=OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-

COUNTBLANK(Лист1!I$2:I$10))

5. Создадим выпадающий список выделив ячейки B2:B8 и на вкладке «Данные» - «Проверка данных» - «Список» - «Источник» =Имена

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

Показать полностью 5

[моё] Microsoft Excel Таблица Список Полезное На заметку Длиннопост

volodiy87

MS, Libreoffice & Google docs

Как сгенерировать случайный процент от числа в excel⁠⁠

5 лет назад

Нужна помощь гуру экселя. Собственно вопрос в следующем: Необходимо число (принятое за 100%) разделить неравномерно на N количество частей, с условием, что на каждую часть будет приходится от A до B % и в сумме получалось то самое число (принятое за 100%).

Как сгенерировать случайный процент от числа в excel

Как будут выглядеть ячейки В6:F6?

[моё] Microsoft office Microsoft Excel Вопрос Помощь Без рейтинга

Petrov210217

MS, Libreoffice & Google docs

Расчет стоимости ремонтных работ в квартире в Опен офисе⁠⁠

5 лет назад

Здравствуйте! Предстоит ремонт в квартире, поэтому взял и сделал себе калькулятор расчета стоимости работы без учета стоимости материала. Сделано в опен офисе, но в экселе должно работать. Расценки взял с одного из сайтов по своему региону.

Для расчета необходимо указать площадь помещения в квадратных метрах (ячейка C3), в столбце "E", напротив количества единиц вида работ, значение объема работ, если в столбце "E" напротив вида работ поле не заполнено, то позиция в расчете не участвует. В конце таблицы дана итоговая сумма стоимости работ. В таблице 248 наименований работ с ценой за 1 ед.измерений.

Ссылка на файл https://yadi.sk/d/RRJIlpW9XaLJfg

Расчет стоимости ремонтных работ в квартире в Опен офисе

Мыло для вопросов Petrov210217@yandex.ru

Показать полностью 1

[моё] Microsoft Excel Таблица Open Office

484

Veseliy.4el

MS, Libreoffice & Google docs

Связанные выпадающие списки в Excel⁠⁠

5 лет назад

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

1. С помощью функции ДВССЫЛ

ДВССЫЛ (INDIRECT) преобразовывает содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. Т.е. если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Мир», то функция выдаст ссылку на именованный диапазон со словом Мир.

Итак, начнём. Выделяем весь список «Певец» (с ячейки А2 и вниз до конца списка) и нажав Ctrl+F3 или на вкладке Формулы - Диспетчер имён – Создать задаём имя диапазону (Певец). Затем таким же способом проделаем то же самое с остальными списками «Группа» и «Дата».

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

Теперь создадим первый выпадающий список для выбора певца. Выделяем пустую ячейку и на вкладке «Данные» нажимаем кнопку «Проверка данных». Затем из выпадающего списка «Тип данных» выбираем вариант «Список» и в поле «Источник» выделяем ячейки с названиями (ячейки A1:C1).

После нажатия на ОК первый выпадающий список готов:

Дальше создадим второй (зависимый) выпадающий список, в котором будет отображаться содержимое списков в зависимости от выбранной категории. Аналогично предыдущему способу, в окне «Проверка данных» в поле «Источник» вводим формулу =ДВССЫЛ(E2), где E2 – адрес ячейки с первым выпадающим списком.

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

Минусы данного способа:

- Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нём есть текст с пробелами, то придётся их заменять на подчёркивания с помощью функции ПОДСТАВИТЬ, =ДВССЫЛ(ПОДСТАВИТЬ(E2;" ";"_"))

=INDIRECT(SUBSTITUTE(E2;" ";"_"))

- Надо вручную создавать много именованных диапазонов (если у нас много значений).

2. Для следующего способа нам нужна таблица, разбитая по категориям и видам:

Как и в предыдущем способе через Ctrl+F3 создаём именованные диапазоны каждой категории (без заголовков). Т.е. сначала «Мебель», затем «Техника» и так каждую по отдельности.

Теперь создаём первый выпадающий список по категориям. Для этого на вкладке «Данные» - «Проверка данных» - «Тип данных» выбираем «Список» и в поле «Источник» вводим названия диапазонов (Мебель;Техника)

Далее для второго зависимого списка тем же способом для ячейки «Вид» в поле «Источник» вводим формулу =ДВССЫЛ($B$14), а для «Тип» =ДВССЫЛ($B$15). В результате у нас получится следующее:

3. С помощью функций СМЕЩ и ПОИСКПОЗ

Для использования этого способа необходим отсортированный список с отдельным образцом имеющихся значений:

Далее нажимаем Ctrl+F3, где задаём имя диапазону D1:D3 (Товар). Затем на вкладке «Данные» - «Проверка данных» - «Тип данных» - «Список» и в строке «Источник» указываем =Товар или просто выделить ячейки D1:D3 (если они на том же листе, где список).

Чтобы выпадающий список автоматически пополнялся новыми данными из категории «Товар», открыв «Диспетчер имён» в строке диапазон вписываем формулу =СМЕЩ($D$1;0;0;СЧЁТЗ($D$1:$D$400);1) где $D$400 количество ячеек необходимое для образца выпадающего списка.

=OFFSET($D$1;0;0;COUNTA($D$1:$D$400);1)

Для зависимого списка товара создадим именованный диапазон с функцией СМЕЩ, который будет динамически ссылаться только на ячейки товара определенного вида. Для этого нажав Ctrl+F3 создаём новый именованный диапазон с любым именем (например, Вид) и в поле «Диапазон» в нижней части окна вводим следующую формулу:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов.

Функция СМЕЩ выдаёт ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. Вот так: =СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

Таким образом:

- начальная ячейка – берём первую ячейку нашего списка, т.е. А1

- сдвиг_вниз – считает функция ПОИСКПОЗ, которая выдаёт порядковый номер ячейки с выбранным товаром (G2) в заданном диапазоне (столбце А)

- сдвиг_вправо = 1, т.к. мы хотим сослаться на Вид в соседнем столбце (В)

- размер_диапазона_в_строках – вычисляем с помощью функции СЧЁТЕСЛИ, которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений – товара (G2)

- размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с Видом товара

В итоге у вас должно быть так:

Добавляем выпадающий список на основе созданной формулы к ячейке G3, нажав на вкладке «Данные» команду «Проверка данных», где выбираем «Список» и в качестве «Источника» указываем =Вид