Сообщество - MS, Libreoffice & Google docs
MS, Libreoffice & Google docs
120 постов 7 117 подписчиков
45

VBA Excel - выбор документа для обработки

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

VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост

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

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

VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост
VBA Excel - выбор документа для обработки Excel, Vba, Длиннопост

Как вы видите, я не заморачивался с названиями.

Код:

Public SrcName

Private Sub CommandButton1_Click()

SrcName= ""

If ListBox1.ListIndex >= 0 Then

SrcName= ListBox1.List(ListBox1.ListIndex)

UserForm1.Hide

End If

End Sub

Private Sub CommandButton2_Click()

SrcName= ""

UserForm1.Hide

End Sub

Private Sub OpnButton_Click()

iOpen = Application.Dialogs(xlDialogOpen).Show

If iOpen = True Then

SrcName= ActiveWorkbook.Name

UserForm1.Hide

Else

MsgBox "отмена", vbCritical, ""

Exit Sub

End If

End Sub

Private Sub UserForm_Activate()

SrcName= ""

ListBox1.Clear

NoShow = ThisWorkbook.Windows(1).Caption

For i = 1 To Application.Windows.Count

If Application.Windows(i).Caption <> NoShow Then ListBox1.AddItem (Application.Windows(i).Caption)

Next ' enum windows

End Sub

Пикабу сожрал все отступы, это не я!

Пример использования:

Dim SrcWB As Worksheet
UserForm1.Show
If UserForm1.SrcName= "" Then Exit Sub
Windows(UserForm1.SrcName).Activate
Set SrcWB = ActiveWorkbook

P.S. Баянометр считает, что эксель на 41% похож на клубничку. Мне кажется, что он недалёк от истины.

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

Картинка в диаграмме Excel

Недавно делал презентацию и захотелось внести разнообразия в простые диаграммы Excel, и я решил добавить картинку в фон диаграммы:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

Для построения такой диаграммы нам нужна таблица, где формула =ОКРВВЕРХ.МАТ(МАКС($B$3:$B$7);1000)-B3 будет добавлять в отдельном столбце недостающую сумму от округлённого максимального значения столбца «В» (это необходимо для заполнения диаграммы):

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

1. Выделяем нашу таблицу, на вкладке «Вставка» выбираем диаграмму «Гистограмма с накоплением»:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

2. Настраиваем формат оси, назначив предельное значение 6000

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

3. Удаляем сетку и легенду:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

4. В значке «Фильтры диаграммы» выбираем «Выбрать данные», затем «Изменить», где в открывшемся поле выделяем диапазон «Года»:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

5. Кликаем мышкой по центру диаграммы и в открывшемся меню вставляем рисунок:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

6. Ставим боковой зазор 0%

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

7. Выделяем оранжевый фон и заменяем его на белый:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

8. Затем выделяем столбцы диаграммы и выбираем «Нет заливки»:

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

9. Определяем границы диаграммы выбрав белый цвет и ширину 3 пт

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

10. Меняем название диаграммы нажав знак «равно» и указав ячейку «Доходы»

Картинка в диаграмме Excel Excel, Диаграмма, Полезное, На заметку, Длиннопост

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

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

Представления в Excel

Представления (Custom Views) — это своего рода альтернативный вариант «Группировки» и «Фильтра», при создании которого запоминается положение столбцов и ячеек, т.е. скрыты ли они или видны, какие группы строк-столбцов свёрнуты/развёрнуты и условия фильтрации.


Итак переходим к делу, чтобы вынести выпадающий список с представлениями на панель быстрого доступа в верхний левый угол окна Excel нажмите Файл - Параметры - Панель быстрого доступа, затем в выпадающем списке выберите Все команды и добавьте список Представления на панель:

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Допустим, что вы часто используете фильтр мужчины/женщины. Выбираем в фильтре пол «М», затем на вкладе Вид нажимаем Представления «Добавить» и в открывшемся окне вписываем имя «Мужчины», затем проделываем то же самое с фильтром «Женщины».

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Пример действия Представлений:

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Для удобной работы с отчётными таблицами можно сделать Представления с подробной статистикой и по кварталам:

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Выделяем столбцы с месяцами C-E, G-I и скрываем их правой кнопкой мыши - Скрыть или нажав Ctrl+0. Затем создаём Представление тем же способом или вписав новое имя в выпадающий список на панели быстрого доступа и нажав Enter.

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


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

Представления в Excel Excel, Таблица, Полезное, На заметку, Гифка, Длиннопост

Примечание:

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

- Представления несовместимы с «умными таблицами». Если в вашей книге на любом листе создана хоть одна «умная таблица», то представления перестают работать.


Примеры файлов можно скачать здесь

Для просмотра сперва нужно включить строку с представлениями

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

Поваренная книга Экселиста #0 - Пару слов об Экселе

Для начала - рад видеть, что моих любимых и обожаемых подписчиков стало 21.. Шутки про  "За 2 дня на Пикабу у меня - очко" оставлю себе))) В первую лавочку пишу для вас, ну и отдельная вам благодарочка за то, что вам это интересно.

Так как я такой же логичный, как стол-жираф-48, начну с того, с чего надо было начать изначально - а зачем, в принципе, Эксель то нужен, и что с ним можно делать (кроме Зиночка_Счет_В_Экселе_Сделает). Тут уже предвижу холивар, прям чувствую, потому сразу подкину на вентилятор.
Основные задачи в классическом понимании это НЕ ХРАНЕНИЕ данных, а их обработка. То есть, если рассматривать классическую MVC-модель - это контроллер. Потому что для представления есть бумага и принтер.

То, что мы храним в Экселе данные - ну да, нам так проще. Но по факту любая СУБД Эксель уничтожит по скорости работы непосредственно с данными. Но, чтобы что-то похожее на обработку организовать на уровне работы с СУБД - придется уже городить несколько этажей SQL запросов.

Потому данные выгружают в Эксель, а там уже и "понеслась звезда по кочкам".

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

Отсюда, как мне кажется, возникает одна такая ошибочка - Эксель это не способ сделать "правильно", это способ сделать БЫСТРО и РАБОЧЕ. 90% расчетных файлов в экселе понимает разработчик и Майкл Джексон, ситуация ровно такая же, как и с ремонтом ( "вот пусть тот криворукий исправляет пол, без пола лампочку не повесить").

Далее чуть объяснений и примеров...

В последнее время очень "модно и молодежно" вводить машинный анализ в прогнозирование возможного поведения клиента (про b2c сегмент говорим). То есть, допустим, на основе покупок клиента делать прогноз (не статистику собираем, а Вангу косплеим) совершения им следующей покупки (как даты, так и состава комплексной покупки) и о возможных similar к ней. И уже на основании этого делать какие-либо выводы и офферы разные предлагать (ну там сырный соус к покупке, ну вы поняли да?)) ).

Реализация этого в экселе занимает от часов до дня, реализация этого в CRM занимает от недель до "на третьи сутки после второго пришествия". Причем реализация на уровне CRM нифига не будет работать без прототипа, реализованного или на бумажке (в виде ТЗ) или в виде костылявых обработок в Экселе. Ведь программист, про кодера даже не будем, не обязан и не будет представлять себе вашу бизнес-логику, ему до нее как Ильичу до лампочки. Да и по поводу ТЗ крайне метко выразился мой препод в институте - то, что вы написали в ТЗ не будет работать так, как это вы написали, а будет так, как прочел (понял) разработчик. Потому мы делает что? Правильно - бьемся челом об сруб светлицы что-то невообразимо-кривое в Экселе, но при перемножении лося и порося получаем искомые 63,3%. Что и служит уже как и вашей (дядиной) прибыли, так и методом контроля разработки. То есть вы берете чистые данные, которые хранятся НЕ в Экселе, а в СУБД, и уже их вьювите и контролите.

Ну и, чтобы разбавить это "много-букофф-ниочем" - боевой пример.

Делаем простейшую экспертную систему в Экселе (без регистации и смс).

Вот таблица:

Поваренная книга Экселиста #0 - Пару слов об Экселе Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных, Субд

Найдите глазами столбец "% win". В зависимости от нее - строка перекрашивается в другой цвет, меняется шрифт, ну и чем значение больше - тем больше вероятность покупки. Цифры относительны, получены крайне простым способом - телефон в руки и обзвон разных групп клиентов. Вычитаем оттуда погрешность "на идиота" и вуаля.

Сама формула:

=VLOOKUP(I109/J109*1000;_tech!$F$2:$G$10;2;TRUE)+
IF(K109="";0;IF(K109>43689;15;VLOOKUP(DATEDIF
(K109;"12/8/19";"D");_tech!$J$2:$K$9;2;TRUE)))

А теперь понимаем как это сделать.

Конкретно этот пример - фитнес. Далее мы ищем данные. Конкретно в моем случае искалось так:

- Таблица с клиентами - CRM (оттуда выгрузка в эксель, листы 62_кк)

- СКУД - с сервера СКУД (да, в CRM она интегрирована криво + карты можно и "забыть" внести в саму CRM, потому что некогда админам) (лист "посещения_приведенные", и "пос_")

- Статистика звонков - с телефонии (потому что оттуда она тупо информативнее, и мне не интересно мнение менеджера о клиенте. Если менеджер говорил более минуты - значит клиенту интересно, просто ему предлагали не то, что ему нужно). (лист 4)

- Дополнительные покупки - CRM (лист "Этот")

- Экспертная оценка - эмпирически-добытые факты. (лист _tech)
(часть данных потер, ибо прайваси-все-дела + обработку специально взял аж с августа, с того времени их было еще версий 20)

Теперь включаем чем думать:

Клиент ходит постоянно -> его все устраивает

Клиент купил карту за сумму N -> сумма его устраивает (то есть предложи столько же или меньше, но не больше)

Клиент покупает что-либо еще -> деньги у клиента не кончились

Карта заканчивается в течение квартала -> купит сейчас, если пункты 1-3 соблюдены.

Клиент женщина 30+, на дворе август, клиент не ходит с июня -> предложить карту и детские занятия со скидкой

Клиенту не звонили с сервисными звонками в течение месяца и пункты 1-3 соблюдены -> звонить в ПЕРВУЮ очередь (не задрочен)

Клиент ходил, сейчас не ходит, возраст до 21 -> звонить и предлагать рассрочку, свалил на лето.

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

Все просто, да? А теперь попытайтесь внедрить это в CRM за неделю) А за две? А за месяц? Да фиг выйдет.. В экселе заняло чуть больше 2х часов.

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

Поваренная книга Экселиста #0 - Пару слов об Экселе Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных, Субд

И да, это тоже можно сделать по другому. А можно и не делать, ведь зачем нужно что-то делать, когда можно просто обсудить все в комментах, неправда ли?))

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

Поваренная книга Экселиста #1 - Преобразовываем ФИО

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

РЕЦЕПТ 1:  Преобразовываем ФИО в Ф / И / О, без использования VBA и прочей нехристи.
Для начала чуть теории - большинство, наверняка, сталкивались с такой задачей - есть ФИО в одном столбце, а нужно вытянуть только имя, ну или два столбца ИМЯ и ФАМИЛИЯ. Да или даже поменять местами, задачи разные - суть одна. Теперь давайте разбираться что имеем - имеем строку с N-количеством слов, разделенных одинаковым символом " " (ну или чуть сложнее с массивом, содержащим N-количество элементов, в роли разделителя " "). Тут важно понять сам смысл - все, что имеет закономерность, подлежит автоматизации. В нашем случае закономерность будет вот такая - СЛОВО" "СЛОВО" "СЛОВО, следовательно мы ИЛИ должны "выбрать" нужное нам СЛОВО из всей строки, или чуть схитрить) Но начнем с выбора.

ВАРИАНТ 1: Средствами экселя. Определяем нахождение разделителей по длине строки.
1. Создаем отдельную страницу (на всякий случай)
2. Вставляем данные - Допустим вид будет вот такой
ID | ФИО
1  | Иванов Иван Иванович (b2)

3. Выбираем ПЕРВОЕ СЛОВО, ячейка (c2)
=TRIM(LEFT(B2;FIND(" ";B2;1)))
Что сделали - нашли первое вхождение символа " " в строку, и резанули все, что после него.

4. Выбираем ВТОРОЕ СЛОВО, ячейка (d2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1))+1;FIND(" ";B2;FIND(" ";B2;1)+1)-FIND(" ";B2;FIND(" ";B2;1))))
Что сделали - указали Экселю на "координаты" первого и второго разделителей, скорректировали координаты (порезали длину строки на лишний символ " ").

5. Выбираем ТРЕТЬЕ СЛОВО, ячейка (e2)
=TRIM(MID(B2;FIND(" ";B2;FIND(" ";B2;1)+1)+1;LEN(B2)))
Что сделали - то же самое, что и со ВТОРЫМ СЛОВОМ, только так как нам не нужно резать по второй координате - за нее взяли длину строки.

На выходе получили вот так:

Поваренная книга Экселиста #1 - Преобразовываем ФИО Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

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

СПОСОБ 2: Подмена разделителя
1. Выделяем столбец с ФИО.
2. Открываем текстовый редактор (дада, Блокнот / TextEdit), вставляем туда. ОБЯЗАТЕЛЬНО переведите его в формат Plain Text/ просто текст (формат сохранения .txt) (!)
3. Находим функцию "Найти и заменить".
3.1. В поле найти введите символ " " (то есть просто поиск по одиночному пробелу).
3.2. В поле заменить вставьте символ TAB. Для этого на первой строке нажмите TAB, выделите его, cmd+c,  cmd+v (или control, у кого какая религия)

Поваренная книга Экселиста #1 - Преобразовываем ФИО Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

3.3. Примените, скопируйте, вставьте в Эксель... И собственно так, если не знали - встречайте, TAB - символ переноса на следующую ячейку))

Поваренная книга Экселиста #1 - Преобразовываем ФИО Excel, Material design, Таблица, Полезное, На заметку, Ms Office, Длиннопост

Надеюсь данные способы вам потребуются) И, главное, на забывайте - работает не только на ФИО)
Понравилось? Что-то интересно? Прошу в комменты)  Ну и там лайк/ подписка / кошелек / очки / мотоцикл)

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

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

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

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

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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

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

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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


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

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

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

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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)))

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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


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))

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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

Выпадающие списки с удалением Excel, Таблица, Список, Полезное, На заметку, Длиннопост

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

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

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1

Для начала - обнял, подбросил всех своих уже 56 фолловеров! Впечатлен, растроган, буду дальше для вас стараться)

И как раз один из вас @ashvud,  написал в Вологду-гдугдугду вот о такой проблеме:

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Скажу сразу - долго не бился, на тест ушло чуть более минуты, так как пример был очень относительный - решение будет тоже относительное, но, если бы столкнулся сам, то решил бы ИЛИ регуляркой по заглавным, точнее по чередованию строчнаяЗАГЛАВНАЯ (в кодировке это разные символы, вот по ним бы и да) с последующей заменой, или скачиванием базы имен и тупо поиску по строке с заменой при нахождении вот по такой маске:

ИМЯ -> ИМЯ+Разделитель

Но правильное и полностью рабочее решение - в конце рецепта, а пока погнали полуфабрикаты готовить..

Рецепт 2. Побеждаем кривое форматирование, неправильный перенос по строкам.

Открыли табличку и смотрим:

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Ага, у нас есть подсказка - если заполнена строка в столбце A - то это начало ФИО в столбце B. Для примера - ячейка A5 - в ней начинается новая часть ПОЛНОЙ строки.

А еще строка А5 намекает на то, что в строке B4 содержится последние символы из "кривого куска".

Что нам это дает? Да собственно решение всей проблемы)

ШАГ А.  ДОБАВЛЯЕМ столбец между существующими A и B, который будем использовать как технический:

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

В ячейку B2 вбиваем следующее:

=IF(A2<>"";"WUT";"")

Это мы проверяем - если значение в столбце А указано - ставим технический символ, а если нет - не ставим. Тем самым мы обозначаем как и окончание полной ПРАВИЛЬНОЙ строки, так и начало полной правильной строки.

Для простоты, кто не в курсе - наводим на нижний правый угол нужной ячейки (B2 в нашем случае), появляется черный крестик. На него два раза - и автозаполнение пройдет до последней заполненной строки справа или слева. (где последняя - туда и дойдет)

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных
Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

ШАГ Б. Поехали рвать вам пуканы в мой любимый текстовый редактор.Выделяем столбцы B, C, копировать, вставить в текстовый редактор.

Наша задача - удалить лишние переносы, при этом не убить текущие правильные пробелы. ОК ГУГЛ, не страшно)
Найти и заменить:

1. SPACE -> --

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

2. TAB -> (пусто, просто удалить делаем через найти и заменить)

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

3. Тыкаем вот сюда, выделяется кусок "пустоты". Выделяем его, копировать-вставить в найти.
ПУСТОТА - > (пусто, просто удалить делаем через найти и заменить)

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

4. Тыкаем в конец файла, жмем ДВАЖДЫ Enter. Получается вот так:

МАССИВ_ТЕКСТА_СЛИТНО

LUL

Выделяем LUL, жмем вверх, выделяется еще и пустая строка. Отлично, копируем получившееся (вместе с пустой строкой). Очищаем ОБЕ строки Найти / Заменить, вбиваем

WUT -> "

LUL"

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

5. LUL -> (пусто, просто удалить делаем через найти и заменить)

6. -- -> SPACE

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Оп-па менты!

ШАГ В. Чтобы получившееся вернуть в Эксель ПОСТРОЧНО, возвращаемся в файл, и там такие применяем фильтр по строке "НОМЕР", убирая значения BLANKS.

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

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

Поваренная книга Экселиста #2 - Побеждаем кривые выгрузки, часть 1 Excel, Таблица, Полезное, На заметку, Ms Office, Длиннопост, Обработка данных

Сохраняемся в конце уровня и просим маму принести полотенце, так как битва была жаркой))

Оставшееся слитные строки - правим руками, потому что мы уже автоматизировали более 90% работы менее чем за 2 минуты, а автоматизировать оставшиеся 10% займет уже дольше времени, чем руками. Потому не ленимся, помним - Эксель это чтобы БЫСТРО.
Ну а точнее - это можно поправить в самой обработке, используя шаги 1-6, просто обработки я не вижу, но автоматизировать на 100% заняло бы минуты 2-3.

Ну, собственно, вот как-то так)

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

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

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

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


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

Google Таблицы: функция GOOGLEFINANCE Google, Табличка, Финансы, Инвестиции, Курс валют, Доллар, Яндекс, Длиннопост

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


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


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


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

Google Таблицы: функция GOOGLEFINANCE Google, Табличка, Финансы, Инвестиции, Курс валют, Доллар, Яндекс, Длиннопост

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


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

Google Таблицы: функция GOOGLEFINANCE Google, Табличка, Финансы, Инвестиции, Курс валют, Доллар, Яндекс, Длиннопост

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

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

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


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

Google Таблицы: функция GOOGLEFINANCE Google, Табличка, Финансы, Инвестиции, Курс валют, Доллар, Яндекс, Длиннопост

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


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

Google Таблицы: функция GOOGLEFINANCE Google, Табличка, Финансы, Инвестиции, Курс валют, Доллар, Яндекс, Длиннопост

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


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

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

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

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

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

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


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

Google Таблицы: функция GOOGLEFINANCE Google, Табличка, Финансы, Инвестиции, Курс валют, Доллар, Яндекс, Длиннопост

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


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


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


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


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

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

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

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


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


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


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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

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

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

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


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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

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


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

Связанные выпадающие списки в Excel Excel, Список, Таблица, Полезное, На заметку, Длиннопост

Далее нажимаем 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, т.к. нам нужен один столбец с Видом товара


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

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

Вопрос к спецам по Excel

Имеется массив цифровых значений (порядка 10000 позиций) - столбец А. Как в столбец В вывести пропущенные (отсутствующие по порядку нумерации) значения из столбца А (данные на картинке внесены вручную).

Вопрос к спецам по Excel Excel, Формула
Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Горячее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!