Дано: 2 таблицы в разных файлах, на скрине я их примеры поместил, для удобства на один лист.
В левой таблице мы видим дату действия в рамках события, того кто это действие сделал и для удобства номер этого действия (в оригинале может быть не уникальным, но не суть)
В правой таблице мы видим даты начала и окончания события, участника этого события.
Необходимо: понять совершал ли участник события действия во временном диапазоне события. Т.е если дата в столбце А находится между датами в столбцах F и G И данные в столбцах B и H равны, то данные из столбца C необходимо подставить в столбец I в строчку с участником.
Пробовал сам гуглить и курить форумы, пробовал комбинировать ВПР с ЕСЛИ, но либо не осилил либо не туда копал, а сроки жмут.
По сути ВПР по 2-м условиям, но усложняет то что одним из условий является не точное совпадение, а то что одна ячейка должна быть больше одной и меньше другой (речь про столбцы А, F и G).
Excel 2010. Прислали файл xls, закреплено 8 строк и 3 столбца (A-C), местами используется объединение ячеек.
Из 8 закрепленных строк 4 было скрыто. Путем выделения от 5-й (видимой) верх показал строки и проставил ненулевую высоту.
Но 1-я строка не отобразилась. Могу перейти стрелками на её ячейки, она существует, но нумерация строк - со 2-й.
Пытался через макросы задать Hidden = False, RowHeight = 10, визуально ничего не меняется. Ставил курсор в A1, делал Главная-Ячейки-Формат-Высота строки... и Скрыть или отобразить-Отобразить строки - никаких изменений. Удалял строку - вторая строка поднимается на место первой и исчезает.
Есть две экселевские таблицы. Это могут быть и два разных листа одной книги и две отдельные книги. Их может быть и больше. И вам надо сопоставить ячейки одной таблицы с ячейками другой, чтоб получить значения из её строки. За пределами экселя это делается с помощью SQL. В самом экселе с каких-то пор появилась некая химера под названием PowerQuery, но ну её нафиг.
В составе офиса у нас уже есть DAO. Это ядро баз данных, используемое MS Access-ом - Jet. Его и будем использовать.
Сохраняем область ячеек одной из из исходных таблиц в xls-файл. Например это от A1 до B100
Dim wt As Workbook, sht As Worksheet 'временная книга
"FROM t1 LEFT JOIN dst ON t1.F1 = t2.F1 " & vbCrLf & _
"WHERE t1.F1 Is Not Null AND t2.F1 Is Null;"
Set qdf = dbs.CreateQueryDef("qCompare", strSQL)
Set rst = dbs.OpenRecordset("qCompare")
If Not rst.EOF Then
While Not rst.EOF
'Debug.Print rst.Fields(0), rst.Fields(1)
rst.MoveNext
Wend
End If
Таким образом формируется запрос, в котором вывелись записи из первой таблицы, для которых не нашлось сопоставление во второй. Можем в цикле вывести эти записи в нашу книгу, например.
В коде встречаются две функции. Вот их код:
Function FileExists(strFile As String) As Boolean
On Error GoTo erro
If FileLen(strFile) <> 0 Then FileExists = True
Exit Function
erro:
If Err.Number = 53 Then FileExists = False
End Function
Function CreateDataBaseDAO(newDB As String, Optional sLocate As String = dbLangCyrillic, Optional iVersion As Integer = dbVersion40) As Boolean
Dim dbNew As Database
On Error GoTo Proc_Err
Set dbNew = DBEngine.CreateDatabase(newDB, sLocate, iVersion)
CreateDataBaseDAO = True
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Description, , Err.Number
CreateDataBaseDAO = False
Resume Proc_Exit
End Function
Самая затратная по времени выполнения часть - это копирование записей из файлов в базу. Это приходится делать, т.к. Jet не считает экселевские файлы полноценным источником данных, не может работать с ними непосредственно через связь, и не может по ним строить индексы и вести нормальный поиск. Может только последовательный перебор от начала до конца файла, что нам совсем не удобно. А вот работа с объектами самого акцесса имеет вполне вменяемое быстродействие. В принципе самое быстрое на данный момент среди файловых баз данных.
Можно конечно и так. Но представьте, что у вас картинок несколько сотен и их нужно периодически обновлять/добавлять/удалять. Можно конечно делать это ручками, как описано у автора, но гораздо проще делать это через макрос:
Sub InsertPicturesInComments() On Error Resume Next
Dim rngPics As Range, rngOut As Range Dim i As Long, p As String, w As Long, h As Long
Set rngPics = Range("B1:B100") 'диапазон путей к картинкам Set rngOut = Range("A1:A100") 'диапазон вывода примечаний
rngOut.ClearComments 'удаляем старые примечания
'проходим в цикле по ячейкам For i = 1 To rngPics.Cells.Count
p = rngPics.Cells(i, 1).Value 'считываем путь к файлу картинки w = LoadPicture(p).Width 'и ее размеры h = LoadPicture(p).Height
With rngOut.Cells(i, 1) .AddComment.Text Text:="" 'создаем примечание без текста .Comment.Visible = True .Comment.Shape.Select True End With With rngOut.Cells(i, 1).Comment.Shape 'заливаем картинкой .Fill.UserPicture p .ScaleWidth 3, msoFalse, msoScaleFromTopLeft .ScaleHeight h / w * 5.4, msoFalse, msoScaleFromTopLeft 'корректируем размеры End With Next i End Sub
Несколько раз напоролся на ситуацию, когда хорошая и удобная вещь протирается, рвется или иными способами приходит в негодность, а вспомнить, где я её покупал и за сколько, уже не получается, начал вести список вещей: какая вещь, где и когда куплена, по какой причине и когда пришла в негодность.
Но по описанию я сам не понимаю, какие именно штаны имел в виду, поэтому начал в эту таблицу добавлять и картинки. Но картинки распирают таблицу, получается неудобно и некрасиво.
Поэтому, когда узнал, что в Excel можно вставлять картинки во всплывающие примечания - очень обрадовался.
Поделюсь и тут этим способом, вдруг кому пригодится
Создаём примечание Shift + F2
Наводим курсор на границу примечания и нажимаем ПКМ
3. Переходим в «Формат примечания», переходим во вкладку «Цвета и линии - Цвет - Способы заливки»
4. В окне «Способы заливки» - во вкладку «Рисунок» и там, ткнув на кнопку «Рисунок», выбираем нужную картинку
Politepost– способен превратить гневное письмо с матами в вежливое послание 😇
Хотите быть в курсе о полезных ии сервисах для работы, учебы и облегчения жизни? Подпишитесь на мой канал в Telegram НейроProfit, там я рассказываю о том, как зарабатывать с помощью нейросетей и использовать ии-сервисы для бизнеса 😉
Привет, Пикабу! Когда вы последний раз открывали Excel? Скорее всего, на этой неделе не обошлось без таблиц и данных.
Эксперты Eduson поделились полезными функциями Excel, про которые вы могли не знать, но они облегчат вам жизнь. Составили пост — пользуйтесь на здоровье!
Диаграмма одной кнопкой
Представьте: пятница, конец месяца. Вы презентуете отчётность перед начальством. И вам говорят: не нужно цифр, давайте графики и выводы.
Как сделать:
выделите нужный диапазон данных;
нажмите F11, диаграмма откроется на новом листе;
отредактируйте вид и настройте её под себя;
удивляйте руководство своим мастерством.
Строки в столбцы
Допустим, вы сделали отчёт за квартал, а вам нужно сравнить его с другими наглядно.
Как сделать:
выделите таблицу, в которой хотите поменять местами строки и столбцы;
скопируйте таблицу при помощи CTRL + C;
нажмите на ячейку для новой таблицы;
откройте вкладку «главная» и нажмите на «вставить»
нажмите «транспонировать».
+ сработает в Google-таблицах. На шаге 4 откройте вкладку «правка» → «специальная вставка» → «поменять местами строки и столбцы».
Хотите научиться крутым приемам в работе Excel и пользоваться им на уровне профессионала? Пройдите курс «Excel и Google-таблицы: от новичка до эксперта» и научитесь настраивать макросы, чтобы экономить время на рутинных задачах.
Посчитать сумму по строкам и столбцам
Например, вы сидите на созвоне с коллегами и хотите автоматически рассчитать сумму в отчётах или финансовых моделях.
Как сделать:
1. выделите таблицу с дополнительными пустыми ячейками по строкам и столбцам;
2. нажмите ALT + = или ⌘ + ⇧ + T (для MAC) и продолжайте слушать конф-колл.
Превратить фотографию P&L в таблицу Excel
Ситуация: заказчик прислал вам в WhatsApp фотографию отчёта. Попросил прислать ему готовую таблицу с расчётами и пропал.
Как сделать:
0. убедитесь, что фотография содержит только те данные, которые требуется импортировать;
1. выберите блок «данные» в панели быстрого доступа;
2. нажмите «получить и преобразовать данные» →«из рисунка»
3. выберите «рисунок из файла»
4. проверьте данные с помощью кнопки «проверить»
5. нажмите «вставить данные». Магия!
Важно: сверьте таблицу с исходным изображением. Excel может исказить числа или текст. Однако переносить таблицу вручную займет больше времени, чем исправить ошибки программы.
Разделить или умножить массив данных на число
Возможно, вы немного косякнули, и указали выручку не в тысячах, а в миллионах.
Как сделать:
1. нажмите на пустую ячейку;
2. введите число, на которое хотите разделить или умножить данные из таблицы;
3. нажмите CTRL + C;
4. выделите массив данных, который хотите преобразовать;
5. откройте вкладку «главная» и нажмите на стрелку рядом с командой «вставить»
6. нажмите на «специальная вставка»
7. выберите «разделить»/«умножить» в блоке «операция». Шалость удалась!
Это только 5 функций, которые упрощают работу в Excel. Но вы можете освоить десятки на курсах от Eduson. Всего за две недели!