EDIT March 9, 2016
So I took the car to a guy who specializes in ATX gears, and he felt that it's not a tranny problem. Got a second opinion from a Chevy tech who concurred. So due to that and the negative fuel trim at higher rpm's I'm leaning either toward a bad / plug or maybe an injector that's sticking open. This video from EricTheCarGuy describes really well what I'm hearing on hard acceleration. However, as there is no safety issue and no pending issue of catastrophic failure, it seems that my neighbor probably won't pursue this for now, although if the problem gets worse it might get picked up again.
I took a look at a neighbor's car ( 2000 Chevy Cavalier 2.2L 4Cyl ATX ) and among other problems, it makes this horrible vibratory kind of noise under hard acceleration from a stop or slow speed. It's fine under slow steady acceleration and once the car is already moving above maybe 30 kph no matter how hard you accelerate. I can also induce the noise by lightly accelerating in a traffic circle.
The first thing coming to my mind is the torque converter:
This is from my ATSG manual for a different trany, but I think it's relevant.
The other problems I found were a slight wobble when going to WOT in park and then rapidly letting off the gas ( all engine mounts have been replaced recently, more than once ), the thermostat is either stuck open or has been removed as the car never gets above about 60*C, oil leaking from the valve cover gasket has filled up the spark plug holes, and Long Term Fuel Trim goes to about -11% when revving the engine at about 2500 rpm. There is also a weird PID shown by OBDWiz SEA 0x15 Short Term Fuel Trim stuck at 99.22% which is in addition to the regular STFT PID 0x06 which looks normal staying just slightly negative. I've seen allot of posts on the net which also have this same 0x15 PID stuck at the exact same value, which is really strange, but that's a different question.
In short, am I in the right direction for this noise?